Re: Postgres performance slowly gets worse over a month

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Marc Mitchell" <marcm@eisolution.com>
Cc: "Robert M. Meyer" <rmeyer@installs.com>; "Naomi Walker"
<nwalker@eldocomp.com>; <pgsql-admin@postgresql.org>
Sent: Wednesday, July 24, 2002 10:21 AM
Subject: Re: [ADMIN] Postgres performance slowly gets worse over a month


> "Marc Mitchell" <marcm@eisolution.com> writes:
> > 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.

That's welcome news.  Two follow-up questions if you could:

1) Is there any inherit value or "dis-value" to using a partial index in
this case?  Given that 50% of the table has a value the we have no need to
be supported via an index, is there any reason not to use a partial index?

2) If we stay in 7.1, would changing the foriegn key field from "NOT NULL"
(where we currently populate with zero) to nullable (where we would
populate instead with null) have any effect on performace?  The hope would
be that nulls might be treated differently within the 7.1 stats gathering
process than non-null values.  This would be a better, albeit still
temporary, solution in our current environment than the random_page_access
hack we chose for now.

Thanks,

Marc Mitchell
Enterprise Information Solutions, Inc.
4910 Main Street
Downers Grove, IL 60515
marcm@eisolution.com