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
