IS NOT DISTINCT FROM + Indexing
| От | Jonathan S. Katz |
|---|---|
| Тема | IS NOT DISTINCT FROM + Indexing |
| Дата | |
| Msg-id | 6FC83909-5DB1-420F-9191-DBE533A3CEDE@excoventures.com обсуждение исходный текст |
| Ответы |
Re: IS NOT DISTINCT FROM + Indexing
Re: IS NOT DISTINCT FROM + Indexing |
| Список | 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
effectivelytesting for equality albeit with some "magic" for NULLs? Here is an example of what I mean, running tests
on9.3.4:
-- create a table of integersCREATE TABLE numbers ASSELECT x FROM generate_series(1,1000000) x;
-- create a b-tree indexCREATE INDEX numbers_x_idx ON numbers (x);
-- find x = 500SELECT * FROM numbers WHERE x = 500; x ----- 500(1 row)
-- query planEXPLAIN SELECT * FROM numbers WHERE x = 500; QUERY PLAN
---------------------------------------------------------------------------------- Index Only Scan
usingnumbers_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 500SELECT * 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.00rows=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
В списке pgsql-hackers по дате отправления: