Re: won't drop the view

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(at)waterford(dot)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