pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Oleg Lebedev <olebedev@waterford.org>
Cc: Postgres SQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: won't drop the view
Date: Wed, 26 Dec 2001 18:48:32 -0700
Message-ID: <3C2A7DF0.C4382382@waterford.org> (raw)
References: <web-530735@davinci.ethosmedia.com>
	<8922.1008638480@sss.pgh.pa.us>
	<3C293ACB.4BEBA02F@waterford.org>
	<20130.1009385674@sss.pgh.pa.us>

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 <olebedev@waterford.org> 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

Attachments:

out.txt (text/plain, 8.1K) download 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)

view thread (12+ messages)  latest in thread

Message-ID: <3C2A7DF0.C4382382@waterford.org>
Permalink:  ../3C2A7DF0.C4382382@waterford.org/
Also on:    postgresql.org/message-id/3C2A7DF0.C4382382@waterford.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: olebedev@waterford.org
  Subject: Re: won't drop the view
  In-Reply-To: <3C2A7DF0.C4382382@waterford.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox