Re: Drawbacks of create index where is not null ?

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.

--
Craig Ringer