Re: Drawbacks of create index where is not null ?

On Wed, Oct 10, 2012 at 10:42 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> I think the query planner has gotten a little smarter of late:
>
> smarlowe=# create index on a (i) where i is not null;
> CREATE INDEX
> smarlowe=# explain select * from a where i =10;
> QUERY PLAN
> ------------------------------------------------------------------------
> Bitmap Heap Scan on a (cost=4.28..78.00 rows=100 width=4)
> Recheck Cond: (i = 10)
> -> Bitmap Index Scan on a_i_idx (cost=0.00..4.26 rows=100 width=0)
> Index Cond: (i = 10)
> (4 rows)

It is even smarter a little bit more:

[local]:5432 grayhemp(at)grayhemp=# create index h_idx1 on h (n) where v
is not null;
CREATE INDEX

[local]:5432 grayhemp(at)grayhemp=# explain analyze select * from h where
v = '0.5';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on h (cost=1616.10..8494.68 rows=1 width=30)
(actual time=111.735..111.735 rows=0 loops=1)
Recheck Cond: (v IS NOT NULL)
Filter: (v = '0.5'::text)
-> Bitmap Index Scan on h_idx1 (cost=0.00..1616.10 rows=102367
width=0) (actual time=19.027..19.027 rows=100271 loops=1)
(5 rows)

--
Sergey Konoplev

a database and software architect
http://www.linkedin.com/in/grayhemp

Jabber: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +14158679984