Re: AW: Re: Performance degradation in PostgreSQL 7.1beta3 vs 6.5.3 - Mailing list pgsql-hackers

From Tom Lane
Subject Re: AW: Re: Performance degradation in PostgreSQL 7.1beta3 vs 6.5.3
Date
Msg-id 16780.979743558@sss.pgh.pa.us
Whole thread Raw
In response to AW: Re: Performance degradation in PostgreSQL 7.1beta3 vs 6.5.3  (Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>)
List pgsql-hackers
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


pgsql-hackers by date:

Previous
From: Camm Maguire
Date:
Subject: Mysterious 7.0.3 error
Next
From: Thomas Lockhart
Date:
Subject: Re: AW: AW: AW: Re: tinterval - operator problems on AIX