Re: Drawbacks of create index where is not null ?

On Wed, Oct 10, 2012 at 11:26 PM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> wrote:
> On 10/11/2012 01:06 AM, Franck Routier wrote:
>>
>> Hi,
>>
>> I have pretty large tables, with columns that might never receive any
>> data, or always receive data, based on the customer needs.
>> The index on these columns are really big, even if the column is never
>> used, so I tend to add a "where col is not null" clause on those indexes.
>>
>> What are the drawbacks of defining my index with a "where col is not null"
>> clause ?
>
>
> * You can't CLUSTER on a partial index; and
>
> * The partial index will only be used for queries that use the condition
> "WHERE col IS NOT NULL" themselves. The planner isn't super-smart about how
> it matches index WHERE conditions to query WHERE conditions, so you'll want
> to use exactly the same condition text where possible.

I think the query planner has gotten a little smarter of late:

smarlowe=# select version();
version
----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.6 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real
(Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit
(1 row)

smarlowe=# drop table a;
DROP TABLE
smarlowe=# create table a (i int);
CREATE TABLE
smarlowe=# insert into a select null from generate_series(1,10000);
INSERT 0 10000
smarlowe=# insert into a values (10);
INSERT 0 1
smarlowe=# insert into a select null from generate_series(1,10000);
INSERT 0 10000
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)