Hi,
I'm curious if there is a reason why "IS NOT DISTINCT FROM" is not an indexable
operation in a B-tree index, as it is effectively testing for equality albeit
with some "magic" for NULLs? Here is an example of what I mean, running tests
on 9.3.4:
-- create a table of integers
CREATE TABLE numbers AS
SELECT x FROM generate_series(1,1000000) x;
-- create a b-tree index
CREATE INDEX numbers_x_idx ON numbers (x);
-- find x = 500
SELECT * FROM numbers WHERE x = 500;
x
-----
500
(1 row)
-- query plan
EXPLAIN SELECT * FROM numbers WHERE x = 500;
QUERY PLAN
----------------------------------------------------------------------------------
Index Only Scan using numbers_x_idx on numbers (cost=0.42..8.44
rows=1 width=4)
Index Cond: (x = 500)
(2 rows)
-- now find x IS NOT DISTINCT FROM 500
SELECT * FROM numbers WHERE x IS NOT DISTINCT FROM 500;
x
-----
500
(1 row)
-- but the query plan is...
EXPLAIN SELECT * FROM numbers WHERE x IS NOT DISTINCT FROM 500;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on numbers (cost=0.00..16925.00 rows=1 width=4)
Filter: (NOT (x IS DISTINCT FROM 500))
With NULLs being indexable, I was wondering if there was some reason why IS NOT
DISTINCT FROM could not use the index?
Thanks,
Jonathan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
