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

On Mon, Mar 21, 2011 at 5:40 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Greg Stark <gsstark(at)mit(dot)edu> writes:
>> So it's a clever hack that we used to allow the partial indexes to be
>> used. It relied on the implicit assumption that min(x) and max(x)
>> where the only values of x where NULL were both NULL.
>
>> It would be nice if we were clever enough to support *any* strict
>> aggregate using partial indexes on WHERE NOT NULL since they'll all
>> have that property.
>
> Huh?  The point of the min/max optimization is to not scan the whole
> index but just fetch the endpoint value.

But in the case where the index has no records it doesn't know whether
there were no records in the table or they were just all NULL. As it
happens min() and max() return NULL in both cases so it doesn't
matter. My point was that this is a clever hack and a non-obvious
deduction the planner is making.

> 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?

--
greg