AW: Re: Performance degradation in PostgreSQL 7.1beta3 vs 6.5.3

Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:

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 thought we had agreed upon a default that would still use
the index in the above case when no statistics are present.
Wasn't it something like a 5% estimate ? I did check
that behavior, since I was very concerned about that issue.
Now, what is so different in his case?

The current estimate is 0.01 (1 percent). That seems sufficient to
cause an indexscan on small to moderate-size tables, but apparently
it is not small enough to do so for big tables. I have been thinking
about decreasing the default estimate some more, maybe to 0.005.
(The reason the table size matters even if you haven't done a VACUUM
ANALYZE is that both plain VACUUM and CREATE INDEX will update the
table-size stats. So the planner may know the correct table size but
still have to rely on a default selectivity estimate. The cost
functions are nonlinear, so what's "small enough" can depend on table
size.)

Bruce, if you'd like to experiment, try setting the attdispersion
value in pg_attribute to various values, eg

update pg_attribute set attdispersion = 0.005
where attname = 'foo' and
attrelid = (select oid from pg_class where relname = 'bar');

Please report back on how small a number seems to be needed to cause
indexscans on your tables.

regards, tom lane