Индексируем OR-выражения или как мы ускорили запрос в 35 000 раз
 |
Андрей Климковский меня снял на пятничном семинаре. Фотографию выставляю, потому что заснят исторический момент тестирования новой фичи, которую Teodor Sigaev только что испек в рамках нашей работы по работе с jsonb на SQL-уровне. А именно, появилась поддержка индексирования выражений с OR. Коллеги по команде помнят, как Федя бегал по офису и все коллективно вспоминали слово RPN, так вот, это все делалось для того, чтобы в фразе An index scan has zero or more scan keys, which are implicitly ANDed ( http://www.postgresql.org/docs/9.4/static/index-scanning.html ) не было вот этого implicitly ANDed и index scan мог поддерживать не только AND-ы, но и OR-ы. Сейчас есть поддержка OR-в в GiST, GIN и BRIN.
Чтобы все поняли, показываю пример: Найти 10 ближайших точек из нескольких кругов (Арктика и Антарктика ) к заданной точке. Если брать по отдельности Арктику или Антарктику, то knn пуляет - на 7 млн точках работает с долями ms, а если соединяем области через OR, то получаем 7 секунд (смотрите на Filter):
explain analyze select name,point from geo where point <@ circle('(90,90)',10) or point <@ circle('(-90,90)',10) order by point <-> '(0,45)' limit 10;
QUERY PLAN
----------------
Limit (cost=0.41..662.97 rows=10 width=30) (actual time=7228.580..7266.604 rows=10 loops=1)
-> Index Scan using geo_idx on geo (cost=0.41..958987.91 rows=14474 width=30) (actual time=7228.570..7266.585 rows=10 loops=1)
Order By: (point <-> '(0,45)'::point)
Filter: ((point <@ '<(90,90),10>'::circle) OR (point <@ '<(-90,90),10>'::circle))
Rows Removed by Filter: 3956498
Planning time: 0.300 ms
Execution time: 7266.673 ms
(7 rows)В нашей pgpro-шной версии мы уже имеем 0.2 ms, или выигрыш в 35 000 раз!
explain analyze select name,point,elevation from geo where point <@ circle('(90,90)',10) or point <@ circle('(-90,90)',10) order by point <-> '(0,45)' limit 10;
QUERY PLAN
----------------
Limit (cost=0.41..322.73 rows=1 width=34) (actual time=0.123..0.147 rows=10 loops=1)
-> Index Scan using geo_idx on geo (cost=0.41..322.73 rows=0 width=34) (actual time=0.121..0.144 rows=10 loops=1)
Index Cond: ((point <@ '<(90,90),10>'::circle) OR (point <@ '<(-90,90),10>'::circle))
Order By: (point <-> '(0,45)'::point)
Planning time: 0.097 ms
Execution time: 0.192 ms
(6 rows)Индексирование OR-в делает ненужным тип tsquery, потому что теперь 'city | town'::tsquery равнозначен 'city'::tsquery OR 'town'::tsquery в отношении индексной поддержки, то есть в FTS мы возвращаемся к обычному SQL, который расширяем ! Аналогично мы рассуждали и про jsquery, когда говорили о том, что его надо выводить на SQL-уровень ради расширяемости, а для этого нужна поддержка индексирования OR-выражений и подзапросов.
Мы постараемся сделать такие доклады, которые требуют некоторых знаний внутренностей постгреса, интересными и познавательными, приходите на pgconf.ru в феврале.