Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Marti Raudsepp
Date:
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

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

From
Robert Haas
Date:
On Mon, Mar 21, 2011 at 7:17 AM, Marti Raudsepp <marti@juffo.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

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

From
Tom Lane
Date:
Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Mar 21, 2011 at 7:17 AM, Marti Raudsepp <marti@juffo.org> wrote:
>> 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.

Yeah, looks that way.  I'm not sure what it would take to re-support
this case without losing the other advantages of the change.  Personally
I'm not terribly excited about it: I don't think that suppressing nulls
from an index this way is really very useful.  Using a partial index
probably eats more planner cycles than you'll save, overall.
        regards, tom lane

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

From
Robert Haas
Date:
On Mon, Mar 21, 2011 at 10:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Mon, Mar 21, 2011 at 7:17 AM, Marti Raudsepp <marti@juffo.org> wrote:
>>> 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.
>
> Yeah, looks that way.  I'm not sure what it would take to re-support
> this case without losing the other advantages of the change.  Personally
> I'm not terribly excited about it: I don't think that suppressing nulls
> from an index this way is really very useful.  Using a partial index
> probably eats more planner cycles than you'll save, overall.

If only 1% of the table has non-NULL values in that column, maybe not.

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