Являются ли postgres индексы JSON эффективными по сравнению с классическими нормализованными таблицами?

Ответ 1

Мне понадобятся некоторые запросы в форме: "Перечислите все объекты, в которых один из имен alt" foobar "". Ожидаемый размер таблицы составляет порядка нескольких миллионов записей. Для этого могут использоваться запросы JSON Postgres, которые также могут быть проиндексированы (например, Index for Finding Element в массиве JSON). Однако СЛЕДУЕТ ли это делать так, или это извращенное обходное решение, которое не рекомендуется?

Это можно сделать так, но это не значит, что вы должны. В некотором смысле, наилучшая практика уже хорошо документирована (см., Например, использование hstore vs с использованием XML vs с использованием EAV и с использованием отдельной таблицы) с новым типом данных, который по существу и в практических целях (помимо проверки и синтаксиса) ничем не отличается из предыдущих неструктурированных или полуструктурированных опций.

Другими словами, это та же старая свинья с новым макияжем.

JSON предлагает возможность использовать индексы инвертированного дерева поиска, так же, как hstore, типы массивов и tsvectors. Они отлично работают, но имейте в виду, что они в основном предназначены для извлечения точек в окрестности (типы геометрии), упорядоченных по расстоянию, а не для извлечения списка значений в лексикографическом порядке.

Чтобы проиллюстрировать, возьмите два плана, на которые отвечает Роман:

• Тот, который выполняет сканирование индекса, прокладывает прямо через страницы диска, извлекая строки в порядке, указанном индексом.
• Тот, который выполняет сканирование растрового индекса, начинается с идентификации каждой страницы диска, которая может содержать строку, и считывает их по мере их появления на диске, как если бы это было (и фактически точно так же) выполнение сканирования последовательности, которое пропускает бесполезные районы.

Возвращаясь к вашему вопросу: загроможденные и негабаритные индексы инвертированных деревьев действительно улучшат производительность вашего приложения, если вы используете таблицы Postgres в качестве гигантских магазинов JSON. Но они также не являются серебряной пулей, и они не доберутся до правильного реляционного дизайна при работе с узкими местами.

Нижняя строка, в конце концов, ничем не отличается от того, что вы получили при принятии решения об использовании hstore или EAV:

• Если ему нужен индекс (т.е. он часто появляется в предложении where или, что еще более важно, в предложении join), вы, вероятно, хотите, чтобы данные были в отдельном поле.
• Если это прежде всего косметический, JSON/hstore/EAV/XML/что-то делает-вы-спать-ночью отлично работает.

Ответ 2

Я бы сказал, что стоит попробовать. Я создал несколько тестов (100000 записей, ~ 10 элементов в массиве JSON) и проверил, как он работает:

create table test1 (id serial primary key, data json);
create table test1_altnames (id int, name text);

create or replace function array_from_json(_j json)
returns text[] as
$func$
    select array_agg(x.elem::text)
    from json_array_elements(_j) as x(elem)
$func$
language sql immutable;

with cte as (
    select
        (random() * 100000)::int as grp, (random() * 1000000)::int as name
    from generate_series(1, 1000000)
), cte2 as (
    select
        array_agg(Name) as "AltNames"
    from cte
    group by grp
)
insert into test1 (data)
select row_to_json(t)
from cte2 as t

insert into test1_altnames (id, name)
select id, json_array_elements(data->'AltNames')::text
from test1

create index ix_test1 on test1 using gin(array_from_json(data->'AltNames'));
create index ix_test1_altnames on test1_altnames (name);

Запрос JSON ( 30 мс на моей машине):

select * from test1 where '{489147}' <@ array_from_json(data->'AltNames');

"Bitmap Heap Scan on test1  (cost=224.13..1551.41 rows=500 width=36)"
"  Recheck Cond: ('{489147}'::text[] <@ array_from_json((data -> 'AltNames'::text)))"
"  ->  Bitmap Index Scan on ix_test1  (cost=0.00..224.00 rows=500 width=0)"
"        Index Cond: ('{489147}'::text[] <@ array_from_json((data -> 'AltNames'::text)))"

Таблица запросов с именами ( 15 мс на моей машине):

select * from test1 as t where t.id in (select tt.id from test1_altnames as tt where tt.name = '489147');

"Nested Loop  (cost=12.76..20.80 rows=2 width=36)"
"  ->  HashAggregate  (cost=12.46..12.47 rows=1 width=4)"
"        ->  Index Scan using ix_test1_altnames on test1_altnames tt  (cost=0.42..12.46 rows=2 width=4)"
"              Index Cond: (name = '489147'::text)"
"  ->  Index Scan using test1_pkey on test1 t  (cost=0.29..8.31 rows=1 width=36)"
"        Index Cond: (id = tt.id)"

Также я должен отметить, что есть некоторая стоимость вставки/удаления строк в таблицу с именами (test1_altnames), поэтому это немного сложнее, чем просто выбирать строки. Лично мне нравится решение с JSON.