Re: Drawbacks of create index where is not null ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, Oct 10, 2012 at 11:26 PM, Craig Ringer <ringerc@xxxxxxxxxxxxx> 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)


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux