Re: Drawbacks of create index where is not null ?

Le 11/10/2012 07:26, Craig Ringer a écrit :
> * 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.
>

From my experiments, the planner seems to be smart enougth to tell that
"where col = 'myvalue' " will match with partial index "where col is not
null".
So it will use the index and not do a full tablescan. (this is on 8.4).
This is also what Scott says in his reply.
I'm not thinking of using more complex where predicat for my indexes,
just "is not null". So I think I should not be hit by this...

Thanks,
Franck