GIN индекс по JSONB и Recheck

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 - то это будет непорядок.

Responses

Browse pgsql-ru-general by date

  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: Безопасность