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 по дате отправления: