Re: won't drop the view - Mailing list pgsql-sql
| From | Oleg Lebedev |
|---|---|
| Subject | Re: won't drop the view |
| Date | |
| Msg-id | 3C2A7DF0.C4382382@waterford.org Whole thread Raw |
| In response to | Re: won't drop the view ("Josh Berkus" <josh@agliodbs.com>) |
| Responses |
Re: won't drop the view
|
| List | pgsql-sql |
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
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
extendedpractice. | | 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
totalfor 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)