Received: from mail1.waterford.org (mail1.waterford.org [205.124.117.6]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fBQJIYm18887 for ; Wed, 26 Dec 2001 14:18:34 -0500 (EST) (envelope-from olebedev@waterford.org) Received: from waterford.org ([205.124.138.93]) by mail1.waterford.org (Netscape Messaging Server 3.56) with ESMTP id 502 for ; Wed, 26 Dec 2001 12:08:20 -0700 Message-ID: <3C2A2791.830BE5F1@waterford.org> Date: Wed, 26 Dec 2001 12:40:01 -0700 From: Oleg Lebedev X-Mailer: Mozilla 4.72 [en] (Windows NT 5.0; U) X-Accept-Language: en MIME-Version: 1.0 CC: Postgres SQL Mailing List Subject: Re: won't drop the view References: <8922.1008638480@sss.pgh.pa.us> <3C293ACB.4BEBA02F@waterford.org> <20130.1009385674@sss.pgh.pa.us> Content-Type: text/plain; charset=koi8-r Content-Transfer-Encoding: 7bit X-Archive-Number: 200112/248 X-Sequence-Number: 6070 You are right Tom, (SELECT ... FROM activity WHERE activity.productcode ~ '^m3') acts yeilds 235 rows, with a total of 240 rows in activity table. I wonder why EXPLAIN estimates only 1 row? Are there any online docs on how the planner works? BTW, I would like to buy a book that would help me tune up postgres performance. Which book would you suggest? Thanks, Oleg Tom Lane wrote: > Oleg Lebedev writes: > > Deleting from pg_statistic restored view performance to the way it was > > before I ran vacuum analyze. > > Below I attach two files that contain explain statistics for the view before > > (progress_report.txt) and after (progress_report_analyzed.txt) I ran vacuum > > analyze. The first one takes about 15 secs. to complete, the second one > > takes 12-13 minutes to complete. > > Looks like the issue is the number of rows estimated to be obtained from > the "activity" table: > > good plan: > > > -> Merge Join (cost=14644.00..14648.62 rows=4 width=128) > > -> Sort (cost=11.02..11.02 rows=2 width=48) > > -> Seq Scan on activity (cost=0.00..11.00 rows=2 width=48) > > -> Sort (cost=14632.99..14632.99 rows=367 width=112) > > -> Subquery Scan media_acts (cost=14553.17..14617.36 rows=367 width=112) > > bad plan: > > > -> Nested Loop (cost=14605.17..14686.99 rows=2 width=128) > > -> Seq Scan on activity (cost=0.00..11.00 rows=1 width=48) > > -> Subquery Scan media_acts (cost=14605.17..14671.27 rows=378 width=112) > > The plans for media_acts look about the same, so I have to guess that > activity actually yields 50 or so rows, not just one. That doesn't > hurt the mergejoin too much, but it is a killer for the nestloop. > > You showed the query as > (SELECT ... > FROM activity > WHERE activity.productcode ~ '^m3') acts > How many rows actually match activity.productcode ~ '^m3' ? How many > rows altogether in activity? > > regards, tom lane