LINUX.ORG.RU
Форум — Development  

create index for json[]

 


0

2

Есть таблица в ней строки с массивами УНИКАЛЬНЫХ значений. Значения уникальны в пределах всей таблицы. Хочется найти строку в массиве которой есть искомое значение. Судя по приведенному ниже логу это получается вот только explain не радует говоря что фильтровал по условию и отбросил 2 строки.

Подскажите плиз как это все можно ускорить? Создать какой-то другой индекс применительно к json? В принципе я могу хешировать эти json и хранить не массив jsonb а массив text или bytea с хешем если это может помочь. Есть какие-то идеи?

postgres9.6=> create table xxx (x_list jsonb[]);
CREATE TABLE
postgres9.6=> insert into xxx (x_list) values (ARRAY['{ "a" : "78", "b" : "157" }'::jsonb,'{ "a" : "19", "b" : "25" }'::jsonb]);
INSERT 0 1
postgres9.6=> insert into xxx (x_list) values (ARRAY['{ "a" : "67", "b" : "51" }'::jsonb,'{ "a" : "124", "b" : "53" }'::jsonb]);
INSERT 0 1
postgres9.6=> insert into xxx (x_list) values (ARRAY['{ "a" : "14", "b" : "32" }'::jsonb,'{ "a" : "36", "b" : "5" }'::jsonb]);
INSERT 0 1
postgres9.6=> create unique index xxx_i1 ON xxx (x_list);
CREATE INDEX
postgres9.6=> vacuum full ANALYZE xxx;
VACUUM
postgres9.6=> set enable_seqscan = off;
SET
postgres9.6=> select * from xxx where x_list  @> ARRAY['{ "a" : "124", "b" : "53" }'::jsonb];
                                x_list                                
----------------------------------------------------------------------
 {"{\"a\": \"67\", \"b\": \"51\"}","{\"a\": \"124\", \"b\": \"53\"}"}
(1 строка)

postgres9.6=> explain analyze select * from xxx where x_list  @> ARRAY['{ "a" : "124", "b" : "53" }'::jsonb];
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Index Only Scan using xxx_i1 on xxx  (cost=0.13..12.18 rows=1 width=85) (actual time=0.025..0.027 rows=1 loops=1)
   Filter: (x_list @> '{"{\"a\": \"124\", \"b\": \"53\"}"}'::jsonb[])
   Rows Removed by Filter: 2
   Heap Fetches: 3
 Planning time: 0.085 ms
 Execution time: 0.045 ms
(6 строк)
★★

Последнее исправление: quester (всего исправлений: 1)

Unique на твоём индексе кстати не так работает как ты хочешь — он гарантирует уникальность массивов, а не их элементов.

maxcom ★★★★★
()
Ответ на: комментарий от maxcom 
postgres9.6=> create table xxx (x jsonb);
CREATE TABLE
postgres9.6=> insert into xxx (x) values ('[ { "a" : "78", "b" : "157" },{ "a" : "19", "b" : "25" }]'::jsonb);
INSERT 0 1
postgres9.6=> insert into xxx (x) values ('[ { "a" : "67", "b" : "51" },{ "a" : "124", "b" : "53" }]'::jsonb);
INSERT 0 1
postgres9.6=> insert into xxx (x) values ('[ { "a" : "14", "b" : "32" },{ "a" : "36", "b" : "5" }]'::jsonb);
INSERT 0 1
postgres9.6=> CREATE INDEX xxx_i2 ON xxx USING GIN (x jsonb_path_ops) WITH (fastupdate = off);
CREATE INDEX
postgres9.6=> vacuum full ANALYZE xxx;
VACUUM
postgres9.6=> set enable_seqscan = off;
SET
postgres9.6=> select * from xxx where x @> '[{ "a" : "124", "b" : "53" }]'::jsonb;
                         x                         
---------------------------------------------------
 [{"a": "67", "b": "51"}, {"a": "124", "b": "53"}]
(1 строка)

postgres9.6=> explain analyze select * from xxx where x @> '[{ "a" : "124", "b" : "53" }]'::jsonb;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on xxx  (cost=12.00..16.01 rows=1 width=67) (actual time=0.022..0.023 rows=1 loops=1)
   Recheck Cond: (x @> '[{"a": "124", "b": "53"}]'::jsonb)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on xxx_i2  (cost=0.00..12.00 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=1)
         Index Cond: (x @> '[{"a": "124", "b": "53"}]'::jsonb)
 Planning time: 0.065 ms
 Execution time: 0.047 ms
(7 строк)
quester ★★
() автор топика
Ответ на: комментарий от maxcom

да план вроде как стал лучше с GIN индексом вот только GIN не поддерживает уникальность и это печально

quester ★★
() автор топика
Ответ на: комментарий от quester

GIN не гарантирует уникальность, но зато он позволяет в триггере быстро проверить наличие элемента, чтобы не «в цикле сканировать». Здесь важно, чтобы уровень изоляции транзакций был выставлен в serializable, иначе дубликаты всё равно можно вставить, если делать это одновременно.

kvap ★
()
Ответ на: комментарий от kvap

он позволяет в триггере быстро проверить наличие элемента

каким образом?

quester ★★
() автор топика
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.
Development