From: | "Jonathan S(dot) Katz" <jonathan(dot)katz(at)excoventures(dot)com> |
---|---|
To: | Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | IS NOT DISTINCT FROM + Indexing |
Date: | 2014-07-21 23:16:33 |
Message-ID: | 6FC83909-5DB1-420F-9191-DBE533A3CEDE@excoventures.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Geoghegan | 2014-07-21 23:51:32 | Re: IS NOT DISTINCT FROM + Indexing |
Previous Message | Marko Tiikkaja | 2014-07-21 20:56:20 | plpgsql.extra_warnings='num_into_expressions' |