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
copy link · copy postgr.es
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