Re: Drawbacks of create index where is not null ?

On Wed, Oct 10, 2012 at 11:42 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> 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)

Actually after an analyze it just uses the plain index no bitmap scan.
So I get the same explain output with or without the "and i is not
null" clause added in.