Re: partial unique index and the planner

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.

On the other hand it seems that the table finally grew big enough for
Postgres to prefer index scans to heap scans even with the skewed
estimates.

> I'm unsure how hard it'd be to improve the situation. If we've already
> identified relevant partial indexes before any of the stats code has to
> run then it'd be pretty easy, but that might be a bit fragile.
>
> Anyway, the usual advice for such cases is to see if raising the
> statistics target helps.

Unfortunately it's already 1000. And it seems to me it woud not help here
anyway. The estimate is very correct for the number of rows with the most
common identifier, only it cannot take the active status into consideration.

--
Michał Politowski
Talking has been known to lead to communication if practiced carelessly.