работа с json-индексами

Доброго времени суток!

Есть таблица t, в которой много записей, несколько миллионов.
В ней есть поле d типа json (jsonb пока, увы, низя использовать).
В очень малом количестве записей в поле d лежит неNULL'овое значение,
еще в меньшем количестве записей в поле d есть ключ f.

create table if not exists t(id bigserial, d json);
INSERT INTO t(d) SELECT NULL FROM generate_series(1,1000000) i;
update t set d = '{"f":"val"}' WHERE id = 999999;

Собственно, задача быстро найти те записи, в которых есть этот ключ f.

Пробовал созданием индекса
CREATE INDEX json_simple_idx ON t((d->>'f'));

запрос
explain select * from t where (d->>'f') = 'val'
показывает Bitmap Index Scan

но мне куда более подходит запрос
select * from t where (d->>'f') is not null;
а у него seq scan и работает куда как медленнее.

Прошу подсказать - можно ли как-то в рамках моей задачи использовать json-индекс или другим путем идти нужно (если так, то каким?)

deadka,

CREATE INDEX json_simple_idx ON t((d->>'f')) where d is not null;

Как-то не полегчало (.

create table if not exists t1(id bigserial, d json);
truncate t1;
INSERT INTO t1(d) SELECT NULL FROM generate_series(1,1000000) i;
update t1 set d = '{"f":"val"}' WHERE id = 999999;
CREATE INDEX json_simple_idx11 ON t1((d->>'f')) where d is not null;

explain select * from t1 where (d->>'f') is not null;
выдал

"Seq Scan on t1 (cost=0.00..16925.00 rows=995000 width=40)"
" Filter: ((d ->> 'f'::text) IS NOT NULL)"

Однако!

deadka,

не сложно догадаться написать where индекса такое же, как запроса.

deadka,

интересные дела, для json полей статистика оказывается вообще не собирается.
без нее постгрес не знает сколько записей отберется по условию и предполагает, что выкидывается 0.5% что конечно не так. нужны дополнительные ухищрения чтобы заставить использовать индекс (например можно сортировку по нему указать).

для jsonb такой проблемы нет, но все равно нужно явно в условии дополнительно указывать and d is not null, чтобы работало.

p2.
не сложно догадаться написать where индекса такое же, как запроса.

Создал

CREATE INDEX json_simple_idx2 ON t((d->>'f')) where (d->>'f') is not null;

explain select * from t where (d->>'f') is not null;
дал тот же seq scan

Alexius

deadka, интересные дела, для json полей статистика оказывается вообще не собирается. без нее постгрес не знает сколько записей отберется по условию и предполагает, что выкидывается 0.5% что конечно не так. нужны дополнительные ухищрения чтобы заставить использовать индекс (например можно сортировку по нему указать). для jsonb такой проблемы нет, но все равно нужно явно в условии дополнительно указывать and d is not null, чтобы работало.

Alexius,

спасибо за ответ!

В случае сортировки - помогает.

explain select * from t where (d->>'f') is not null ORDER BY d->>'f';

"Index Scan using json_simple_idx4 on ..."

причем что с индексом

CREATE INDEX json_simple_idx4 ON t4((d->>'f')) WHERE (d->>'f') is not null;

что с индексом без where

CREATE INDEX json_simple_idx4 ON t4((d->>'f'));

explain получается одинаковый. Это как-то объяснимо?

deadka,

Мысли вслух: если требуется отбор, сортировка или группировка по значению из композитного типа данных, то такое значение следует вынести в отдельную колонку…

deadka,

explain тут и должен быть одинаковый. речь шла про то, что условие в where частичного индекса почти всегда должно присутствовать в явном виде в where запроса, чтобы он использовался. а ранее условия были разные (where d is not null и where (d->>'f') is not null).