5 ms·
My first guess would be that IS NOT DISTINCT FROM considers nulls to match. Indexes might not contain null values. I don't know of postgres indexes are sparse o
by singlow 3y ago
My first guess would be that IS NOT DISTINCT FROM considers nulls to match. Indexes might not contain null values. I don't know of postgres indexes are sparse or can be non-sparse? It would be hard to do use a sparse index and find null matches. I would expect this could be optimized away if the column is also not null, but then why would you use the operator in that case so maybe no one has handled it.
- singlow 3y agoHmm - actually read some docs and I think that the implementation of IS NOT DISTINCT FROM was originally implemented one way when nulls were not indexed, but then hasn't been fixed since null indexing was implemented.
- whakim 3y agoIndices in Postgres do contain NULL values. My guess is that it's possible to make IS DISTINCT FROM an indexable operator, but it wouldn't be straightforward given that NULLs are a bit of a special case for indexing comparisons in the sense that they totally ignore data types. IS DISTINCT FROM would probably have to redefine a bunch of existing opclasses to account for the possibility of NULL.