IS NOT DISTINCT FROM + Indexing

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

Responses

Browse pgsql-hackers by date

  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'