Re: Performance degradation in PostgreSQL 7.1beta3 vs 6.5.3

bruc(at)stone(dot)congenomics(dot)com (Robert E. Bruccoleri) writes:
> I have followed the discussion in pgsql-hackers over the previous
> months and others have noted some performance problems, and the response
> has typically been to VACUUM the tables. Unfortunately, this is not a
> practical option for my applications. They are very large -- I have one
> table that is 17GB in length, and the applications are used frequently.

You can't afford to run a VACUUM ANALYZE even once in the lifetime of
the table?

> More importantly, PostgreSQL 6.5.3 works very, very well without
> VACUUM'ing.

6.5 effectively assumes that "foo = constant" will select exactly one
row, if it has no statistics to prove otherwise. I don't regard that
as a well-chosen default, even if it does happen to work OK for your
application. Selecting an indexscan when a seqscan is needed is just
as evil as doing the reverse; what's much worse is that 6.5 will
pick incredibly bad join plans (ie, nested loops) because it thinks
that very little data is coming out of the scans.

If you want to revert to the 6.5 behavior without doing a VACUUM, you
could probably get pretty close with
update pg_attribute set attdispersion = -1.0;

Stats-gathering and planning certainly does need a great deal of
additional work, but I'm afraid that none of that will happen before
7.1.

regards, tom lane