Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 Select из большой таблицы с jsonb  [new]
Shniperson
Member

Откуда:
Сообщений: 1
Добрый день.
Есть таблица с данными, количество записей на данный момент порядка 200млн. Версия PostgreSQL 11.8 (Ubuntu 11.8-1.pgdg18.04+1).
Один столбец типа jsonb, по которому надо производить отбор. Также пользователь может выбирать несколько значений каждого параметра.

Таблица:
id.                 bigint                   not null    nextval('my_schema.measures_values_id_seq'::regclass)
measure_id.   bigint
parameters    jsonb
value            numeric(18,3)

Indexes:
    "data_pk" PRIMARY KEY, btree (id) CLUSTER
    "idx_data_measure" btree (measure_id)
    "idx_data_parameters_gin" gin (parameters)


Индексы:
CREATE INDEX idx_data_measure ON data USING btree (measure_id);
CREATE INDEX idx_data_parameters_gin ON data USING gin (parameters);


Запрос:
SELECT d.id, d.measure_id, CAST(d.parameters as TEXT) as parameters, d.value 
FROM my_schema.data d 
WHERE 
     d.measure_id=51 AND 
     parameters @> '{"6":819,"717":820,"718":1286,"719":822}' AND 
     CAST((parameters ->> '720') AS BIGINT) in (821,823,824,825,826,827,828,829) and 
     CAST((parameters ->> '2') AS BIGINT) in (472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498) 
ORDER BY d.id;


Такой запрос обрабатывается около 12 секунд. Вот план запроса:
 Gather Merge  (cost=5797210.43..5797314.97 rows=896 width=83) (actual time=10948.672..10952.528 rows=216 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=291515 read=151732
   ->  Sort  (cost=5796210.41..5796211.53 rows=448 width=83) (actual time=10944.060..10944.068 rows=72 loops=3)
         Sort Key: id
         Sort Method: quicksort  Memory: 36kB
         Worker 0:  Sort Method: quicksort  Memory: 34kB
         Worker 1:  Sort Method: quicksort  Memory: 34kB
         Buffers: shared hit=291515 read=151732
         ->  Parallel Bitmap Heap Scan on data d  (cost=1861.61..5796190.68 rows=448 width=83) (actual time=10916.302..10943.917 rows=72 loops=3)
               Recheck Cond: (parameters @> '{"6": 819, "717": 820, "718": 1286, "719": 822}'::jsonb)
               Filter: ((measure_id = 51) AND (((parameters ->> '720'::text))::bigint = ANY ('{821,823,824,825,826,827,828,829}'::bigint[])) AND (((parameters ->> '2'::text))::bigint = ANY ('{472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498}'::bigint[])))
               Rows Removed by Filter: 1497
               Heap Blocks: exact=1778
               Buffers: shared hit=291499 read=151732
               ->  Bitmap Index Scan on idx_data_parameters_gin  (cost=0.00..1861.34 rows=200179 width=0) (actual time=10915.826..10915.826 rows=4708 loops=1)
                     Index Cond: (parameters @> '{"6": 819, "717": 820, "718": 1286, "719": 822}'::jsonb)
                     Buffers: shared hit=291499 read=147024
 Planning Time: 1.876 ms
 Execution Time: 10953.520 ms


Во-первых в плане я не вижу использование индекса idx_data_measure.
Во-вторых мне кажется что все-таки запрос выполняется долго, и как-то можно сделать быстрее. Я пробовал создать общий индекс (measure_id, parameters) с помощью btree_gin, но это не помогло и я этот индекс прибил.

Вопросы: насколько оптимально созданы индексы, создан запрос, и можно ли как-то сделать чтобы это работало быстрее?

Заранее спасибо.
29 июл 20, 13:54    [22175135]     Ответить | Цитировать Сообщить модератору
 Re: Select из большой таблицы с jsonb  [new]
Flashpoke
Member

Откуда:
Сообщений: 26
Видимо, у индекса idx_data_measure плохая селективность, вот он и был выброшен из плана.

Сомнительно, что можно что-то сделать для улучшения с parameters.
Разве что '2' и '720' часто используемые и стабильные значения, тогда можно по них создать свои индексы навроде
CREATE INDEX idx_data_parameters_gin_2  ON data USING GIN ((jdoc -> '2'));


Есть только предложение попробовать создать индекс
CREATE INDEX idx_data_parameters_gin ON data USING gin (parameters jsonb_path_ops);

подавать ему все параметры через jsonb
WHERE parameters @> '{"6":819,"717":820,"718":1286,"719":822}' AND (
	parameters @> '{"720":819}' OR parameters @> '{"720":821}' OR ...
)

и проверить производительность такого запроса.
30 июл 20, 22:18    [22175897]     Ответить | Цитировать Сообщить модератору
 Re: Select из большой таблицы с jsonb  [new]
grgdvo
Member

Откуда:
Сообщений: 104
Shniperson,

Shniperson
количество записей на данный момент порядка 200млн.

Напрашивается партицирование по measure_id с сохранением индекса по parameters.
У вас очень распухший индекс получился. Очень много IO.

Shniperson
               ->  Bitmap Index Scan on idx_data_parameters_gin  (cost=0.00..1861.34 rows=200179 width=0) (actual time=10915.826..10915.826 rows=4708 loops=1)
Index Cond: (parameters @> '{"6": 819, "717": 820, "718": 1286, "719": 822}'::jsonb)
Buffers: shared hit=291499 read=147024
31 июл 20, 09:54    [22175958]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить