Re: Planner regression in 9.1: min(x) cannot use partial index with NOT NULL

Greg Stark <gsstark(at)mit(dot)edu> writes:
> On Mon, Mar 21, 2011 at 5:40 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> For general aggregates, you
>> have to scan the table anyway. If an index is useful for that, it'll
>> get picked up in the normal planning process.

> if I do "SELECT count(col) from tab" with no WHERE clauses on a table
> with 1% non-null values in col will the planner correctly find the
> partial index? If so why doesn't the min/max planning find it?

It will not. The hard part of doing something with that is that there
could be more than one aggregate. I did think about whether we could
just push the IS NOT NULL into the main query, but that falls down on
cases like this:

select min(x), max(y) from tab;

If we try to modify that to

select min(x), max(y) from tab where x is not null and y is not null;

then we get the wrong answers, since x and y are probably nonnull in
different subsets of the table.

In the case of min/max, the endpoint hack makes the aggregates so cheap
that we can afford to perform a separate indexscan for each aggregate,
and thus having a NOT NULL qual that is different for each aggregate
isn't a problem (as long as we make sure it only affects that
aggregate's subquery and not the whole query). This approach doesn't
scale to aggregates that will scan the whole table, though.

I suppose we might be able to do what you're suggesting for the case of
only one aggregate, but that isn't going to meet the desire of not
having a regression from what 9.0 could do with min/max.

regards, tom lane