ReSQL.ru
     
powered by simpleCommunicator - 2.0.52     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Select из большой таблицы с jsonb
3 сообщений из 3, страница 1 из 1
Select из большой таблицы с jsonb
    #39984953
Shniperson
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shniperson
Гость
Добрый день.
Есть таблица с данными, количество записей на данный момент порядка 200млн. Версия PostgreSQL 11.8 (Ubuntu 11.8-1.pgdg18.04+1).
Один столбец типа jsonb, по которому надо производить отбор. Также пользователь может выбирать несколько значений каждого параметра.

Таблица:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
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)



Индексы:
Код: plsql
1.
2.
CREATE INDEX idx_data_measure ON data USING btree (measure_id);
CREATE INDEX idx_data_parameters_gin ON data USING gin (parameters);



Запрос:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
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 секунд. Вот план запроса:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
 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, но это не помогло и я этот индекс прибил.

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

Заранее спасибо.
...
Рейтинг: 0 / 0
29.07.2020, 13:54
| Ответить | Цитировать | Написать  
Select из большой таблицы с jsonb
    #39985446
Flashpoke
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Flashpoke
Гость
Видимо, у индекса idx_data_measure плохая селективность, вот он и был выброшен из плана.

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



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


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


и проверить производительность такого запроса.
...
Рейтинг: 0 / 0
30.07.2020, 22:18
| Ответить | Цитировать | Написать  
Select из большой таблицы с jsonb
    #39985476
grgdvo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
grgdvo
Гость
Shniperson,

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

Shniperson
Код: plaintext
1.
2.
               ->  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
...
Рейтинг: 0 / 0
31.07.2020, 09:54
| Ответить | Цитировать | Написать  
3 сообщений из 3, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Select из большой таблицы с jsonb
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&fpage=24&tid=1994550]:
 0ms
get settings:
 7ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 53ms
get topic data:
 13ms
get forum data:
 2ms
get page messages:
 33ms
get tp. blocked users:
 2ms
others: 18ms
total:  146ms
созд. / загр.: 146ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]