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  (Peter Geoghegan <pg@heroku.com>)
Re: IS NOT DISTINCT FROM + Indexing  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: Marko Tiikkaja
Дата:
Сообщение: plpgsql.extra_warnings='num_into_expressions'
Следующее
От: Peter Geoghegan
Дата:
Сообщение: Re: IS NOT DISTINCT FROM + Indexing