Received: from mail1.waterford.org (mail1.waterford.org [205.124.117.6]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fBR1R3m38318 for ; Wed, 26 Dec 2001 20:27:03 -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 294 for ; Wed, 26 Dec 2001 18:16:54 -0700 Message-ID: <3C2A7DF0.C4382382@waterford.org> Date: Wed, 26 Dec 2001 18:48:32 -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: multipart/mixed; boundary="------------3B4DB343C76608C4437ED53D" X-Archive-Number: 200112/250 X-Sequence-Number: 6072 This is a multi-part message in MIME format. --------------3B4DB343C76608C4437ED53D Content-Type: text/plain; charset=koi8-r Content-Transfer-Encoding: 7bit Thanks for your comments Tom. I attached a file with the query you mentioned in your posting and it's output. Those numbers don't really tell me anything, but let me know if you find anything interesting there. 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 --------------3B4DB343C76608C4437ED53D Content-Type: text/plain; charset=koi8-r; name="out.txt" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="out.txt" select attname, attdispersion, s.* from pg_statistic s, pg_attribute a, pg_class c where starelid = c.oid and attrelid=c.oid and staattnum=attnum and relname='activity'; attname | attdispersion | starelid | staattnum | staop | stanullfrac | stacommonfrac | stacommonval | staloval | stahival -------------------------+---------------+----------+-----------+-------+-------------+---------------+--------------------------------------+-------------+------------------------------ actname | 0.110222 | 14559105 | 1 | 1066 | 0 | 0.266667 | ip | 0000 | test2 actdesc | 0.002625 | 14559105 | 2 | 1066 | 0 | 0.0125 | Operations extended practice. | | this is for testing purposes origdesigner | 0.447556 | 14559105 | 3 | 1066 | 0.633333 | 0.0625 | 52 | | 85 origwriter | 0.537514 | 14559105 | 4 | 1066 | 0.704167 | 0.0583333 | | | 55 origartist | 0.537514 | 14559105 | 5 | 1066 | 0.704167 | 0.0166667 | | | 98 origvideo | 0.494181 | 14559105 | 6 | 1066 | 0.670833 | 0.1 | 31 | | 31 origaudio | 0.537514 | 14559105 | 7 | 1066 | 0.704167 | 0.275 | | | 42 origprogrammer | 0.537514 | 14559105 | 8 | 1066 | 0.704167 | 0.0416667 | 4 | | 4 formaldesigner | 0.883556 | 14559105 | 9 | 1066 | 0.933333 | 0.0208333 | 83 | | 84 formalwriter | 0.883556 | 14559105 | 10 | 1066 | 0.933333 | 0.025 | 116 | | 55 formalartist | 0.883556 | 14559105 | 11 | 1066 | 0.933333 | 0.00833333 | 105 | | 98 formalvideo | 0.875035 | 14559105 | 12 | 1066 | 0.933333 | 0.0625 | | | 31 formalaudio | 0.883556 | 14559105 | 13 | 1066 | 0.933333 | 0.0375 | 42 | | 42 formalprogrammer | 0.883556 | 14559105 | 14 | 1066 | 0.933333 | 0.0208333 | 124 | | 136 status | 0.473181 | 14559105 | 15 | 1066 | 0 | 0.654167 | Predesign | Design | Production artnotes | 0.767625 | 14559105 | 16 | 1066 | 0.8625 | 0.00833333 | 10 art days total for IP and Assess. | 10 Art Days | sdafasdfasdfasdf productcode | 0.002625 | 14559105 | 17 | 1066 | 0 | 0.0125 | m3nt22 | 0000 | t3nt15 artproduction | 0.540139 | 14559105 | 18 | 58 | 0 | 0.641667 | t | f | t audioproduction | 0.540139 | 14559105 | 19 | 58 | 0 | 0.641667 | t | f | t actclass | 0.703181 | 14559105 | 20 | 1066 | 0.179167 | 0.820833 | | | comments | 0.356514 | 14559105 | 21 | 1066 | 0.554167 | 0.445833 | | | priority | 0.0208472 | 14559105 | 22 | 97 | 0 | 0.0791667 | 1 | 0 | 148 prodaudio | 0.948181 | 14559105 | 23 | 1066 | 0.0291667 | 0.970833 | | | prodartist | 0.948181 | 14559105 | 24 | 1066 | 0.0291667 | 0.970833 | | | proddesigner | 0.948181 | 14559105 | 25 | 1066 | 0.0291667 | 0.970833 | | | prodprogrammer | 0.948181 | 14559105 | 26 | 1066 | 0.0291667 | 0.970833 | | | prodvideo | 0.948181 | 14559105 | 27 | 1066 | 0.0291667 | 0.970833 | | | prodwriter | 0.948181 | 14559105 | 28 | 1066 | 0.0291667 | 0.970833 | | | prodaudioisgroup | 1 | 14559105 | 29 | 58 | 0 | 1 | f | f | f prodartistisgroup | 1 | 14559105 | 30 | 58 | 0 | 1 | f | f | f proddesignerisgroup | 1 | 14559105 | 31 | 58 | 0 | 1 | f | f | f prodprogrammerisgroup | 1 | 14559105 | 32 | 58 | 0 | 1 | f | f | f prodvideoisgroup | 1 | 14559105 | 33 | 58 | 0 | 1 | f | f | f prodwriterisgroup | 1 | 14559105 | 34 | 58 | 0 | 1 | f | f | f origdesignerisgroup | 1 | 14559105 | 35 | 58 | 0 | 1 | f | f | f origwriterisgroup | 1 | 14559105 | 36 | 58 | 0 | 1 | f | f | f origartistisgroup | 1 | 14559105 | 37 | 58 | 0 | 1 | f | f | f origaudioisgroup | 1 | 14559105 | 38 | 58 | 0 | 1 | f | f | f origprogrammerisgroup | 1 | 14559105 | 39 | 58 | 0 | 1 | f | f | f formaldesignerisgroup | 1 | 14559105 | 40 | 58 | 0 | 1 | f | f | f origvideoisgroup | 1 | 14559105 | 41 | 58 | 0 | 1 | f | f | f formalwriterisgroup | 1 | 14559105 | 42 | 58 | 0 | 1 | f | f | f formalartistisgroup | 1 | 14559105 | 43 | 58 | 0 | 1 | f | f | f formalvideoisgroup | 1 | 14559105 | 44 | 58 | 0 | 1 | f | f | f formalaudioisgroup | 1 | 14559105 | 45 | 58 | 0 | 1 | f | f | f formalprogrammerisgroup | 1 | 14559105 | 46 | 58 | 0 | 1 | f | f | f objectid | -1 | 14559105 | 47 | 412 | 0 | 0.00416667 | 143264 | 1668 | 193932 objectversion | 0.152514 | 14559105 | 48 | 97 | 0 | 0.329167 | 3 | 0 | 18 (48 rows) --------------3B4DB343C76608C4437ED53D--