Обсуждение: GIN индекс по JSONB и Recheck
Построил индекс 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 - то это будет
непорядок.
Привет > Возможно из за этого прогнозное время отличается от реального на два порядка? Что такое "прогнозное время"? Планировщик не прогнозирует время выполнения запроса. План выбирается с минимальной оценённой стоимостью выполнения, но этоне время. > 1. Можно ли избавиться от Recheck по jsonb - GIN индексу Нет. Recheck тут не только от gin, но и от bitmap. > 2. Если нет - можно ли избавиться от Recheck Removed хотя бы? При обходе bitmap индекс сам может запросить recheck значения, если реализация этого ops считает, это значение подойти подусловие может, но не обязательно. > 3. Если смотреть на оба EXPLAIN то можно видеть что в обоих случаях выбираются ВСЕ совпадения индекса (в БД на 20 млн записейсодержится ровно 37 записей удовлетворяющих поисковому запросу). Вопрос: можно ли перестроить запрос чтобы выбиралосьне более LIMIT записей? Или поясните - почему так происходит и как с этим жить? Потому что это Bitmap Index Scan. Index Scan по GIN невозможен, amgettuple не представлен. > Ну а поскольку запрос - поисковый, то могут запросить как нечто хорошо селективное, так и нечто слабо селективное. То естьесли он будет в промежутке выбирать скажем из 10 млн записей 1 млн а потом от него брать LIMIT 10 - то это будет непорядок. Тут можно gin_fuzzy_search_limit покрутить - как раз верхний лимит результата для GIN. Сергей
> Нет. Recheck тут не только от gin, но и от bitmap. > >> 2. Если нет - можно ли избавиться от Recheck Removed хотя бы? > > При обходе bitmap индекс сам может запросить recheck значения, если реализация этого ops считает, это значение подойтипод условие может, но не обязательно. > я тогда не понимаю. Поскольку индекс в случае текстов ВСЕГДА выдает recheck Recheck в случае текстов ВСЕГДА делает множество Recheck Removed (даже когда TEXT[] просто индексируем) то выходит что GIN индекс ВСЕГДА невозможно предсказать по времени и ресурсам - сколько же займет выборка? в чем смысл тогда GIN индекса? все прочие индексы ведь нужны только затем чтобы привести выборку к предсказуемому O(1), O(ln(n)) итп
Привет >>> Возможно из за этого прогнозное время отличается от реального на два порядка? >> >> Что такое "прогнозное время"? >> Планировщик не прогнозирует время выполнения запроса. План выбирается с минимальной оценённой стоимостью выполнения, ноэто не время. > Planning time: 0.092 ms > Execution time: 2.331 ms > > что такое Planning time тогда? > > в случае с jsonb_path_ops оно почти совпадает с временем Execution > > это время работы планировщика? Да, это время затраченное на планирование запроса. >>> 1. Можно ли избавиться от Recheck по jsonb - GIN индексу >> >> Нет. Recheck тут не только от gin, но и от bitmap. >> >>> 2. Если нет - можно ли избавиться от Recheck Removed хотя бы? >> >> При обходе bitmap индекс сам может запросить recheck значения, если реализация этого ops считает, это значение подойтипод условие может, но не обязательно. > реализация опс > > значит ли это что если написать свой опс который никогда не запросит речек, то проблему можно решить? recheck тут появляется из двух мест: - от bitmap index scan сам по себе. Может проверять и отбрасывать много строк, если становится непример lossy вместо exact.(недостаток work_mem в частности, ну это так, для полноты ответа) - если индекс при добавлении элемента в bitmap сказал, что не уверен в истинности этого значения. В мануале про второй случай сказано в частности здесь: https://www.postgresql.org/docs/current/index-scanning.html > The access method can report that the index is lossy, or requires rechecks, for a particular query. This implies that theindex scan will return all the entries that pass the scan key, plus possibly additional entries that do not. The coresystem's index-scan machinery will then apply the index conditions again to the heap tuple to verify whether or not itreally should be selected. If the recheck option is not specified, the index scan must return exactly the set of matchingentries. То есть индекс имеет право вернуть не только строго подходящие TID, но и те, которые возможно подходят, но надо перепроверить.Вот неподходящие как раз и будет видно в Recheck Removed. >>> 3. Если смотреть на оба EXPLAIN то можно видеть что в обоих случаях выбираются ВСЕ совпадения индекса (в БД на 20 млнзаписей содержится ровно 37 записей удовлетворяющих поисковому запросу). Вопрос: можно ли перестроить запрос чтобы выбиралосьне более LIMIT записей? Или поясните - почему так происходит и как с этим жить? >> >> Потому что это Bitmap Index Scan. Index Scan по GIN невозможен, amgettuple не представлен. > хм > невозможен скан в текущей реализации или в принципе? Это лучше в -hackers уже спрашивать. По идее amgettuple реализовать можно, но вот смысла в этом немного намой взгляд. По gin невозможно делать сортировку. А запросбез сортировки вида "точность не надо, N каких-то достаточно" - штука редкая и для него уже есть gin_fuzzy_search_limit. >> Нет. Recheck тут не только от gin, но и от bitmap. >> >>> 2. Если нет - можно ли избавиться от Recheck Removed хотя бы? >> >> При обходе bitmap индекс сам может запросить recheck значения, если реализация этого ops считает, это значение подойтипод условие может, но не обязательно. > > я тогда не понимаю. Поскольку индекс в случае текстов ВСЕГДА выдает recheck > Recheck в случае текстов ВСЕГДА делает множество Recheck Removed (даже когда TEXT[] просто индексируем) Removed - не всегда.