Re: Postgres performance slowly gets worse over a month

"Marc Mitchell" <marcm(at)eisolution(dot)com> writes:
> However, the problem
> was that half the rows in "TABLE_A" had no foreign key relationship and so
> had this field set to zero. No one would ever do a select asking for all
> rows where foreign key = 0 as that didn't make "Business sense". But,
> since the database isn't aware of the business meaning of our data, the
> stats gathered by the ANALYSE would think that the overall distribution of
> the column for a unique value was between 2000 and 3000 rows.

> Our short term fix was to turn down the value of random_page_cost.
> However, as Tom Lane very rightly noted in response to a similar posting,
> this is a total hack. Our goal is to switch to 7.2 in the hopes that the
> "WHERE" extension to the "CREATE INDEX" command coupled with greater
> control of the sample space used in statistics will be the true
> answer.

Actually, 7.2 should fix this without any need for messing with partial
indexes. The new statistics code will realize that zero is an outlier,
and will estimate scans for other target values differently.

regards, tom lane