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

Zeugswetter Andreas SB <ZeugswetterA(at)wien(dot)spardat(dot)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