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

On Mon, Mar 21, 2011 at 7:17 AM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
> Hi list,
>
> When I have fields with lots of null values, I often create indexes
> like this: CREATE INDEX foo_i ON foo(i) WHERE i IS NOT NULL;
> This saves me some space, as most indexed queries exclude NULLs anyway.
>
> In PostgreSQL 9.0.3, min(i) can successfully use this index:
> -----------
> marti=# create table foo as select null::int as i from
> generate_series(1,100000);
> marti=# create index foo_i_notnull on foo (i) where i is not null;
> marti=# analyze foo;
> marti=# explain analyze select min(i) from foo;
>  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.025..0.026
> rows=1 loops=1)
>   InitPlan 1 (returns $0)
>     ->  Limit  (cost=0.00..0.00 rows=1 width=4) (actual
> time=0.021..0.021 rows=0 loops=1)
>           ->  Index Scan using foo_i_notnull on foo  (cost=0.00..8.27
> rows=100000 width=4) (actual time=0.019..0.019 rows=0 loops=1)
>  Total runtime: 0.063 ms
> -----------
>
> It seems that PostgreSQL 9.1alpha3 cannot, however:
> -----------
> marti=# explain analyze select min(i) from foo;
>  Aggregate  (cost=1594.00..1594.01 rows=1 width=4) (actual
> time=29.612..29.612 rows=1 loops=1)
>   ->  Seq Scan on foo  (cost=0.00..1344.00 rows=100000 width=4)
> (actual time=0.023..14.221 rows=100000 loops=1)
>  Total runtime: 29.661 ms
> -----------
>
> It would be cool to have this feature re-added before a 9.1 release.
>
> I know that the Merge Append patch required some changes in the
> min/max optimization, which is probably the cause.

Yeah, I think this is a direct result of commit
034967bdcbb0c7be61d0500955226e1234ec5f04.

I was kind of nervous about that one when it went in, and the fact
that we're getting our first complaint about it before we've even hit
beta is not setting my mind at ease...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company