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

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



Собственно, задача быстро найти те записи, в которых есть этот ключ 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-индекс или другим путем идти нужно (если так, то каким?)

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




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)"

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

Создал



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

Alexiusdeadka,

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

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

Alexius,

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

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


"Index Scan using json_simple_idx4 on ..."

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


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


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

vyegorovdeadka,

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

Помыслил ). Встречные мысли вслух: если в таблице очень много записей, и лишь у малого количества записей встречается такой аттрибут - то делать для него столбец и заполнять NULL'ами в 99% случаев - то не жирно ли?.. Композитные типы данных разве не для такого (в том числе) случая создали?

Alexiusdeadka,

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

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

vyegorovdeadkaВстречные мысли вслух: если в таблице очень много записей, и лишь у малого количества записей встречается такой аттрибут - то делать для него столбец и заполнять NULL'ами в 99% случаев - то не жирно ли?.. Композитные типы данных разве не для такого (в том числе) случая создали?
Как я понял по вопросу, для вас такие записи важны. Нормализуйте схему — создайте связанную таблицу для нужных значений и привязывайтесь к ней по необходимости.
Согласен, вариант. Но для каждого такого аттрибута по таблице заводить.. Плюс не вполне понятно, что подразумевается под "важностью" данных :). Какие данные Вы подразумеваете под неважными, чтобы можно из было в композитном типе хранить?

Alexiusdeadka,

оба варианта в данном случае хороши (с частичным функциональным индексом и с отдельным полем с индексом).

по размеру отдельное поле с индексом будет раза в 2 больше места занимать, но сами null'ы ничего не стоят (они все хранятся в битовой маске, если в таблице менее 8 nullable полей - то это 1 лишний байт на каждую строку, который и так есть). из плюсов - на это поле уже будет статистика, если например нужно будет с какой-то таблицей по этому полю join сделать (или по другому полю, но важно правильно знать число строк) - это поможет.
из минусов помимо размера еще необходимость поддерживать поле в актуальном состоянии (например, триггером).

обычно индекса достаточно, но для json полей какие-то запросы придется поправить, где-то может быть не очень очевидным путем.

Alexius, честно говоря думал, что редко появляющееся значение - просто идеальный кандидат для того, чтобы в композитное поле его положить. Если нет, то для каких же данных предназначен json/jsonb? Для тех, по которым нет нужды индексы использовать?