GIN индекс по JSONB и Recheck
От | Dmitry E. Oboukhov |
---|---|
Тема | GIN индекс по JSONB и Recheck |
Дата | |
Msg-id | 197911557937435@sas2-80cfc068821c.qloud-c.yandex.net обсуждение исходный текст |
Ответы |
Re: GIN индекс по JSONB и Recheck
|
Список | pgsql-ru-general |
Построил индекс GIN по jsonb полю. Заполнил тестовую таблицу случайными json'ами и играю с поиском. Вводные: 1. В тестовой базе 20 млн записей 2. Записей удовлетворяющих поисковому условию - 37 Если индекс строится с json_path_ops, то вот так: CREATE INDEX tstj_data_idx ON tstj USING GIN (data json_path_ops); unera=# EXPLAIN ANALYZE select id, data FROM tstj WHERE data @> '{"_a": "brj"}'::JSONB LIMIT 10; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Limit (cost=211.00..241.53 rows=10 width=53) (actual time=0.046..0.073 rows=10 loops=1) -> Bitmap Heap Scan on tstj (cost=211.00..61275.12 rows=20000 width=53) (actual time=0.045..0.070 rows=10 loops=1) Recheck Cond: (data @> '{"_a": "brj"}'::jsonb) Heap Blocks: exact=10 -> Bitmap Index Scan on tstj_data_idx (cost=0.00..206.00 rows=20000 width=0) (actual time=0.028..0.029 rows=37loops=1) Index Cond: (data @> '{"_a": "brj"}'::jsonb) Planning time: 0.090 ms Execution time: 0.103 ms (8 строк) А если просто индекс: CREATE INDEX tstj_data_idx ON tstj USING GIN (data); То вот этак: unera=# EXPLAIN ANALYZE select id, data FROM tstj WHERE data @> '{"_a": "brj"}'::JSONB LIMIT 10; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Limit (cost=234.58..265.12 rows=10 width=53) (actual time=2.263..2.300 rows=10 loops=1) -> Bitmap Heap Scan on tstj (cost=234.58..61160.72 rows=19946 width=53) (actual time=2.262..2.298 rows=10 loops=1) Recheck Cond: (data @> '{"_a": "brj"}'::jsonb) Rows Removed by Index Recheck: 9 Heap Blocks: exact=19 -> Bitmap Index Scan on tstj_data_idx (cost=0.00..229.59 rows=19946 width=0) (actual time=2.241..2.241 rows=60loops=1) Index Cond: (data @> '{"_a": "brj"}'::jsonb) Planning time: 0.092 ms Execution time: 2.331 ms (9 строк) И вот в первом случае меня очень смущает наличие Rows Removed by Index Recheck. Возможно из за этого прогнозное время отличаетсяот реального на два порядка? Кто может объяснить почему? PS: в реальном проекте видим что при выборке 20 значений точного соответствия Rows Removed by Index Recheck достигает несколькихтысяч, а в базе всего - один миллион записей. и в реальном проекте прогнозное время отличается от реального на три-четыре порядка. И как с этим бороться - непонятно. Вопросы: 1. Можно ли избавиться от Recheck по jsonb - GIN индексу 2. Если нет - можно ли избавиться от Recheck Removed хотя бы? 3. Если смотреть на оба EXPLAIN то можно видеть что в обоих случаях выбираются ВСЕ совпадения индекса (в БД на 20 млн записейсодержится ровно 37 записей удовлетворяющих поисковому запросу). Вопрос: можно ли перестроить запрос чтобы выбиралосьне более LIMIT записей? Или поясните - почему так происходит и как с этим жить? PPS: пишется система где таким способом делается ПОИСКОВЫЙ запрос. Он идёт всегда с лимитом. Типа если выдается фигня - пользовательбудет что-то уточнять. Ну а поскольку запрос - поисковый, то могут запросить как нечто хорошо селективное, так и нечто слабо селективное. То естьесли он будет в промежутке выбирать скажем из 10 млн записей 1 млн а потом от него брать LIMIT 10 - то это будет непорядок.
В списке pgsql-ru-general по дате отправления: