Индексирование ключа JSONB в сочетании с другими столбцами

Для поиска по определенным ключам в столбце jsonb я хотел создать индекс для столбца.

Использование: Postgres 10.2

Игнорируя некоторые несвязанные столбцы, у меня есть таблица animals с этими столбцами (исключая некоторые несвязанные столбцы):

animalid PK number
location (text)
type (text)
name (text)
data (jsonb) for eg: {"age": 2, "tagid": 11 }

Мне нужно искать на основе: location, type и tagId. Нравиться:

where location = ? and type = 'cat' and (data ->> 'tagid') = ?

Другие важные моменты:

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

Как сделать так, чтобы поиск был быстрым? Варианты, которые я рассматривал:

  1. Сделать отдельную таблицу для хранения котов: animal_id, location, tagId (хотя FK в партиционированную родительскую таблицу нельзя)
  2. Создайте индекс для location, type и ключа jsonb.
  3. Создайте новый (индексированный) столбец tagId, который будет нулевым для всех животных, кроме кошки.

У меня есть индекс для других столбцов в таблице, но я немного запутался в том, как создать индекс, чтобы сделать поиск кошек на основе tagid быстрым. Какие-либо предложения?

ОБНОВЛЕНИЕ (игнорирование разделов):

(Тестирование на секционированной таблице)

Поэтому я решил использовать вариант, предложенный Эрвином, и попытался создать индекс

CREATE INDEX ON animals_211 (location, ((data->>'tagid')::uuid)) WHERE  type = 'cat';

И попробовал EXPLAIN для запроса (используя секционированную таблицу для простоты):

explain select * from animals_211 a
where a.location  = 32341
and a.type  = 'cat'
and (data->>'tagid')::uuid = '5e54c1d9-3ea0-4bca-81d6-1000d90cc42c'

И судя по результатам, он не использует созданный индекс и выполняет последовательное сканирование:

Seq Scan on animals_211  e  (cost=0.00..121.70 rows=1 width=327)                                                                                                        |
  Filter: ((location = 32341) AND ((type)::text = 'cat'::text) AND (((data ->> 'tagid'::text))::uuid = '5e54c1d9-3ea0-4bca-81d6-1000d90cc42c'::uuid

ОБНОВЛЕНИЕ 2 (без использования частичного индекса)

Это как-то парциальный индекс, как без него - вроде работает:

CREATE INDEX tag_id_index ON animals_211 (location, type, ((data->>'tagid')::uuid))

Когда я делаю план объяснения:

Index Scan using tag_id_index on animals_211 e  (cost=0.28..8.30 rows=1 width=327)                                                                                         
  Index Cond: ((location = 32341) AND ((type)::text = 'cat'::text) AND (((data ->> 'tagid'::text))::uuid = '5e54c1d9-3ea0-4bca-81d6-1000d90cc42c'::uuid))

postgresql database-design indexing jsonb postgresql-performance
person Chillax    schedule 30.01.2021    source источник
comment
Ваше обновление сбивает с толку. Частичный индекс находится в родительской таблице, но Postgres 10 не поддерживает индексы в родительской таблице. Вы должны увидеть сообщение об ошибке для этого. Я добавил еще кое-что к своему ответу по теме. Предлагаю отделить вопрос о лучшем индексе от вопроса о лучшем партиционировании...   -  person Erwin Brandstetter    schedule 01.02.2021
comment
@ErwinBrandstetter может создать индекс, без сообщений об ошибках. Но его не было в списке индексов таблицы. Я согласен, давайте уберем раздел из уравнения (мне нужно было бы сделать это вручную для всех разделов) - я пытался создать индекс конкретно на одном разделе. Индекс создан, но план объяснения показывает, что он по-прежнему не использует индекс. Я пробовал только местоположение + тип, просто чтобы посмотреть, и это работает. Так почему-то кажется, что это проблема с индексом jsonb?   -  person Chillax    schedule 02.02.2021
comment
Также обновил мой вопрос   -  person Chillax    schedule 02.02.2021
comment
Частичный индекс определенно должен работать. Возможно, вам нужно запустить ANALYZE. Рассмотрим примечания и ссылки, которые я добавил к своему ответу.   -  person Erwin Brandstetter    schedule 03.02.2021

Ответы (1)


arrow_upward
1
arrow_downward

Основы (без учета разбиения)

Основываясь на ваших трех важных моментах, я предлагаю частичный индекс для выражения :

CREATE INDEX ON animals ((data->>'tagid'))
WHERE  type = 'cat';

Используйте CREATE INDEX CONCURRENTLY ..., чтобы избежать проблем с блокировкой при одновременном доступе для записи в одну и ту же таблицу. .

Postgres также собирает определенные статистические данные для частичного индекса, которые помогают планировщику запросов получить соответствующие оценки. Обратите внимание, что вам нужно запустить ANALYZE (или VACUUM ANALYZE) вручную, если вы тестируете индекс сразу после создания, прежде чем autovacuum сможет сработать. См.:

Если tagid действительно является каким-то другим типом данных, чем text, вы также можете преобразовать выражение для дополнительной оптимизации. Видеть:

Ваше обновление предполагает, что tagid хранит значения UUID. Читать:

Поэтому вместо этого рассмотрите этот индекс:

CREATE INDEX ON animals (((data->>'tagid')::uuid))  -- !
WHERE  type = 'cat';

Дополнительный набор скобок вокруг (data->>'tagid')::uuid необходим, чтобы сделать синтаксис однозначным.
И соответствующий запрос:

SELECT *
FROM   animals
WHERE  location = 32341
AND    type = 'cats'
AND    (data->>'tagid')::uuid = '5e54c1d9-3ea0-4bca-81d6-1000d90cc42c';  -- !

Или — в зависимости от избирательности каждого предиката и возможных вариантов запроса — включить location, чтобы сделать его многоколоночным индексом:

CREATE INDEX ON animals (location, ((data->>'tagid')::uuid))
WHERE  type = 'cat';

Или сначала tagid, если у вас есть запросы без фильтрации по местоположению. Видеть:

Поскольку только относительно небольшое количество строк имеет тип «кошка», индекс будет относительно небольшим, исключая большую часть ваших миллионов строк. И для начала нам нужен только индекс tagid для кошек. Беспроигрышный.

Если возможно, выделите ключ json data->>'tagid' в качестве выделенного столбца. (Как вы рассматривали вариант 3.) Может быть нулевым там, где это неприменимо, нулевое хранилище очень дешево. Удешевляет хранение и индексирование, а запросы немного упрощают.

Разделение

Postgres 10 не поддерживает индексы в родительской таблице многораздельной таблицы. Это было добавлено в Postgres 11. С тех пор декларативное разбиение было намного улучшено. Рассмотрите возможность обновления до текущей версии 13 или более поздней.

Существует также вариант старого стиля. с наследованием. Тогда у вас может быть отдельный раздел для кошек с дополнительной колонкой tagid только там. Руководство:

Для декларативного секционирования секции должны иметь точно такой же набор столбцов, что и секционированная таблица, тогда как при наследовании таблиц дочерние таблицы могут иметь дополнительные столбцы, отсутствующие в родительской.

Звучит как идеальная подгонка. Но наследование вышло из моды в Postgres, поэтому я бы дважды подумал, прежде чем делать это.

В любом случае - будь то декларативный или наследование - если у вас есть все кошки в отдельном разделе, нечастичный индекс, очевидно, выполняет эту работу:

CREATE INDEX ON cats (location, ((data->>'tagid')::uuid));

И запрос может быть нацелен на раздел cats вместо родительской таблицы:

SELECT *
FROM   cats
WHERE  location = 32341
AND    (data->>'tagid')::uuid = '5e54c1d9-3ea0-4bca-81d6-1000d90cc42c';

Ориентация на родительскую таблицу тоже должна работать. (Не уверен насчет Postgres 10.)

SELECT *
FROM   animals
WHERE  type = 'cat'
AND    location = 32341
AND    (data->>'tagid')::uuid = '5e54c1d9-3ea0-4bca-81d6-1000d90cc42c';

Но активируйте удаление разделов< /a> для этого. Руководство:

Обратите внимание, что отсечение секции управляется только ограничениями, неявно определенными ключами секции, а не наличием индексов. Поэтому нет необходимости определять индексы для ключевых столбцов.

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

person Erwin Brandstetter    schedule 30.01.2021
comment
Только что обновил свой вопрос, я сделал, как вы предложили. Но, похоже, не использует индекс. Где-нибудь я могу ошибаться? - person Chillax; 01.02.2021
comment
Обновление Postgresql не является вариантом atm (хотя я понимаю его важность\уместность), и мы не хотим создавать для этого отдельный столбец, так как мы хотим, чтобы столбцы были для общих полей для животных и не хотим, чтобы отдельный столбец для кот\новые животные в будущем - person Chillax; 02.02.2021
comment
Просто для протокола: если кошки составляют более нескольких процентов, обычно все же было бы эффективно иметь выделенный столбец tagid, который равен NULL для не кошек. Опционально применяется с ограничением CHECK. Хранилище NULL очень дешево. Перейдите по ссылке выше. - person Erwin Brandstetter; 03.02.2021
comment
Мне удалось успешно создать индексаторы, но мне пришлось делать это с большим количеством повторных попыток, так как это вызывало ошибки взаимоблокировки (это живые таблицы с одновременными операциями записи). Я думаю об использовании СОВМЕСТНО при создании индекса - может ли это помочь в предотвращении взаимоблокировок? (как в документации, я мог видеть, что это не будет блокировать таблицу от записи и будет ждать завершения текущих транзакций, а также разрешать транзакции во время создания индекса) - person Chillax; 08.04.2021
comment
@Chillax: Да, это именно то, для чего предназначен CONCURRENTLY. Я также добавил ссылку выше. - person Erwin Brandstetter; 08.04.2021