Re: 7.4beta2 vs 7.3.3

Gaetano Mendola <mendola(at)bigfoot(dot)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