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

From: Marti Raudsepp <marti(at)juffo(dot)org>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Planner regression in 9.1: min(x) cannot use partial index with NOT NULL
Date: 2011-03-21 11:17:04
Message-ID: AANLkTin9NTu0Xq1imn2NOzN1TpNuC8JVDXjS3HPi4eV0@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Regards,
Marti

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Vaibhav Kaushal 2011-03-21 11:44:43 How to look at the Expression Trees
Previous Message Heikki Linnakangas 2011-03-21 10:24:57 Re: Rectifying wrong Date outputs