Re: 7.4beta2 vs 7.3.3

Gaetano Mendola <mendola@bigfoot.com> writes:
> ===== Bad news =====
> I posted time ago about a slow query:

> SELECT ul.*
> FROM user_logs ul,
>       user_data ud,
>       class_default cd
> WHERE ul.id_user = ud.id_user AND
>        ud.id_class = cd.id_class AND
>        cd.id_provider = 39;

I think the reason the planner chooses a not-optimal plan here is that
it's misestimating the number of rows involved by a factor of 10
(it thinks there will be 400 matching user_data rows, not 43).

You would probably find better results if you increase the statistics
target for user_data.id_class and re-ANALYZE.  See past discussions in
pgsql-performance.

> Here the comparison between Postgres7.3.3 and
> Postgres7.4beta2 for that original query:

Hm, it sure looks to be exactly the same plan.  The performance
difference seems to be just that the seqscans are faster.  I surmise
that in the 7.3 database you had a lot of dead rows, or at least a lot
of free space.  Possibly you need to vacuum more often to keep down the
amount of junk in the tables.
        regards, tom lane