From: | Dmitry E(dot) Oboukhov <unera(at)debian(dot)org> |
---|---|
To: | pgsql-ru-general <pgsql-ru-general(at)postgresql(dot)org> |
Subject: | GIN индекс по JSONB и Recheck |
Date: | 2019-05-15 16:23:55 |
Message-ID: | 197911557937435@sas2-80cfc068821c.qloud-c.yandex.net |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | 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=37 loops=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=60 loops=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 - то это будет непорядок.
From | Date | Subject | |
---|---|---|---|
Next Message | Sergei Kornilov | 2019-05-15 16:51:30 | Re: GIN индекс по JSONB и Recheck |
Previous Message | Aln Kapa | 2019-04-26 09:36:20 | Re: Безопасность |