SQL индексирует для “не равные” поиски
Индекс SQL позволяет находить быстро строку, которая соответствует моему запросу. Теперь, я должен искать в большой таблице строки, которые не соответствуют. Конечно, нормальный индекс не помогает, и я должен сделать медленное последовательное сканирование:
essais=> \d phone_idx
Index "public.phone_idx"
Column | Type
--------+------
phone | text
btree, for table "public.phonespersons"
essais=> EXPLAIN SELECT person FROM PhonesPersons WHERE phone = '+33 1234567';
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using phone_idx on phonespersons (cost=0.00..8.41 rows=1 width=4)
Index Cond: (phone = '+33 1234567'::text)
(2 rows)
essais=> EXPLAIN SELECT person FROM PhonesPersons WHERE phone != '+33 1234567';
QUERY PLAN
----------------------------------------------------------------------
Seq Scan on phonespersons (cost=0.00..18621.00 rows=999999 width=4)
Filter: (phone <> '+33 1234567'::text)
(2 rows)
Я понимаю (см. очень хорошие объяснения Mark Byers), что PostgreSQL может решить не использовать индекс, когда он видит, что последовательное сканирование было бы быстрее (например, если почти все кортежи соответствуют). Но, здесь, "не равные" поиски действительно медленнее.
Какой-либо способ сделать их "не равен" поискам быстрее?
Вот другой пример к адресной метке превосходные комментарии Byers. Индекс используется для '=' запрос (который возвращает подавляющее большинство кортежей), но не для'! =' запрос:
essais=> \d tld_idx
Index "public.tld_idx"
Column | Type
-----------------+------
pg_expression_1 | text
btree, for table "public.emailspersons"
essais=> EXPLAIN ANALYZE SELECT person FROM EmailsPersons WHERE tld(email) = 'fr';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using tld_idx on emailspersons (cost=0.25..4010.79 rows=97033 width=4) (actual time=0.137..261.123 rows=97110 loops=1)
Index Cond: (tld(email) = 'fr'::text)
Total runtime: 444.800 ms
(3 rows)
essais=> EXPLAIN ANALYZE SELECT person FROM EmailsPersons WHERE tld(email) != 'fr';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Seq Scan on emailspersons (cost=0.00..27129.00 rows=2967 width=4) (actual time=1.004..1031.224 rows=2890 loops=1)
Filter: (tld(email) <> 'fr'::text)
Total runtime: 1037.278 ms
(3 rows)
DBMS является PostgreSQL 8.3 (но я могу обновить до 8,4).
2 ответа
Возможно, это поможет написать:
SELECT person FROM PhonesPersons WHERE phone < '+33 1234567'
UNION ALL
SELECT person FROM PhonesPersons WHERE phone > '+33 1234567'
или просто
SELECT person FROM PhonesPersons WHERE phone > '+33 1234567'
OR phone < '+33 1234567'
PostgreSQL должен быть в состоянии определить, что избирательность операции диапазона очень высока, и рассмотреть возможность использования для нее индекса.
Я не думаю, что он может использовать индекс напрямую для удовлетворения предиката «не равно», хотя было бы неплохо, если бы он мог попробовать переписать не-равно, как указано выше (если это поможет) во время планирования. Если работает, предложите разработчикам;)
Обоснование: поиск в индексе всех значений, не равных определенному, требует сканирования всего индекса. Напротив, поиск всех элементов меньше определенного ключа означает поиск самого большого несовпадающего элемента в дереве и сканирование в обратном направлении. Точно так же поиск всех элементов больше определенного ключа в противоположном направлении. Эти операции легко выполнить с помощью структур b-дерева. Кроме того, статистика, которую собирает PostgreSQL, должна указывать на то, что «+33 1234567» - известное частое значение: удалив частоту этих значений и нулей из 1, у нас будет оставшаяся доля строк для выбора: границы гистограммы будут укажите, смещены ли они в одну сторону или нет.Но если исключение нулей и этого частого значения приводит к тому, что доля строк остается достаточно низкой (Istr около 20%), сканирование индекса должно быть подходящим. Проверьте статистику для столбца в pg_stats, чтобы узнать, какая доля фактически рассчитана.
Обновление : Я пробовал это на локальной таблице с неопределенно похожим распределением, и обе формы вышеперечисленного вызвали нечто иное, чем обычное последовательное сканирование. Последнее (с использованием «ИЛИ») было сканированием растрового изображения, которое может фактически превратиться в последовательное сканирование, если предвзятость к вашему общему значению особенно велика ... хотя планировщик может это видеть, я не думаю, что это произойдет автоматически перепишите во внутреннее «Приложение (индексное сканирование, индексное сканирование)». Отключение "enable_bitmapscan" просто заставило его вернуться к последовательному сканированию.
PS : индексирование текстового столбца и использование операторов неравенства может быть проблемой, если ваша база данных находится не в C. Возможно, вам потребуется добавить дополнительный индекс, который использует text_pattern_ops или varchar_pattern_ops; это похоже на проблему индексации для предикатов столбца LIKE 'prefix%' .
Альтернатива : вы можете создать частичный индекс:
CREATE INDEX PhonesPersonsOthers ON PhonesPersons(phone) WHERE phone <> '+33 1234567'
это заставит <> , использующий оператор select, просто сканировать этот частичный индекс: поскольку он исключает большинство записей в стол, он должен быть маленьким.
База данных может использовать индекс для этого запроса, но предпочитает этого не делать, поскольку это будет медленнее. Update: Это не совсем верно: вам нужно немного переписать запрос. См. ответ Araqnid.
Ваше предложение where выбирает почти все строки в вашей таблице (rows = 999999). База данных видит, что сканирование таблицы в этом случае будет быстрее, и поэтому игнорирует индекс. Это быстрее, потому что столбец person отсутствует в вашем индексе, поэтому для каждой строки придется делать два поиска: один раз в индексе для проверки условия WHERE, а затем снова в основной таблице для получения столбца person.
Если бы у вас был другой тип данных, где большинство значений были foo и только несколько bar, и вы сказали WHERE col <> 'foo', то, вероятно, использовался бы индекс.
Есть ли способ сделать эти поиски "is not equal to" быстрее?
Любой запрос, который выбирает почти 1 миллион строк, будет медленным. Попробуйте добавить ограничение.