Re: partial unique index and the planner

On Feb 16, 2009, at 7:18 PM, Michal Politowski wrote:

> On Sun, 15 Feb 2009 13:41:05 -0500, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> wrote:
>> Michal Politowski <mpol+pg(at)meep(dot)pl> writes:
>>> Is it normal that plans using a scan on a partial unique index
>>> estimate that much more than one row is returned?
>>
>> There isn't currently any special logic to recognize that case;
>> the estimate is just whatever is going to come out of the normal
>> statistics-based estimation.
>
> Too bad. It seems then that the schema is not well suited to what
> Postgres
> would like. Maybe changing it will be the right thing to do.
>
> The situation is that there are potentially several versions of a row,
> only one of which is active at any given moment. The partial unique
> index
> lets a query find the active row quickly, but since it is not known
> to the
> planner that there is only one such row, a join caused problems.
>
> So it looks like, at least for the current problem, separating the
> active
> and inactive rows in their own tables would help.

I don't know your exact situation, but you could define a foreign key
from some other table to the 'active' rows in your table. That key
would then need to be updated by a few triggers (on insert, update &
delete) on your table. Put an index on the row that's referencing the
foreign key and you get the same index content-wise that your partial
index was covering, except that all the rows in the referencing table
(and thus the index) are known to have only one matching row in your
table. This way you shouldn't have your earlier problem with the
estimates.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4999b115747031962913450!