Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fBQGsUm12882 for ; Wed, 26 Dec 2001 11:54:30 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id fBQGsYj20133; Wed, 26 Dec 2001 11:54:34 -0500 (EST) To: Oleg Lebedev cc: Postgres SQL Mailing List Subject: Re: won't drop the view In-reply-to: <3C293ACB.4BEBA02F@waterford.org> References: <8922.1008638480@sss.pgh.pa.us> <3C293ACB.4BEBA02F@waterford.org> Comments: In-reply-to Oleg Lebedev message dated "Tue, 25 Dec 2001 19:49:48 -0700" Date: Wed, 26 Dec 2001 11:54:34 -0500 Message-ID: <20130.1009385674@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200112/247 X-Sequence-Number: 6069 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