On Wed, Oct 10, 2012 at 11:42 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote: > 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) 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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
