jeudi 13 août 2015

PostgreSQL: Create an index to quickly distinguish NULL from non-NULL values

Consider a SQL query with the following WHERE predicate:

...
WHERE name IS NOT NULL
...

Where name is a textual field in PostgreSQL.

No other query checks any textual property of this value, just whether it is NULL or not. Therefore, a full btree index seems like an overkill, even though it supports this distinction:

Also, an IS NULL or IS NOT NULL condition on an index column can be used with a B-tree index.

What's the right PostgreSQL index to quickly distinguish NULLs from non-NULLs?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire