Re: Postgres performance slowly gets worse over a month

"Marc Mitchell" <marcm(at)eisolution(dot)com> writes:
> 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?

It probably would be a win, just on the basis of reducing the size and
amount of update activity for the 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.

In fact they are, and this very likely would be a cleaner solution
anyway. Seems like NULL might be a semantically clearer representation
of the row's status than zero. You could still do something with a
partial index excluding the nulls in 7.2, I think.

regards, tom lane