PostgreSQL, как ускорить проверку наличия уникального значения в столбце?

Question

[Илья]

Есть простая таблица:

CREATE TABLE public.notices (
  id TEXT NOT NULL,
  json TEXT,
  CONSTRAINT notices_idx PRIMARY KEY(id)
) 
WITH (oids = false);

В json может быть длиннющий текст.
Перед добавлением записей проверяю их наличие в таблице, этот этап прошёл три стадии развития:

1. Проверка по одному: select * from notices where id = '123' limit 1);
   
2. Проверка по одному:

   select exists(select 1 from notices where id = '123');

   
   
3. Проверка всем скопом:

   (SELECT id FROM notices WHERE id='123' LIMIT 1) UNION ALL (SELECT id FROM notices WHERE id='234' LIMIT 1) UNION ALL ...

   
   

Последний способ дал существенное ускорение, но количество записей растёт, количество id в запросе большое, так что скорость проверки пачки в 50 тыс. id для базы размером ~1 млн. записей составляет от 20 до 60 секунд на текущей машине.

Хотелось бы ускорить проверку до минимума. Как можно оптимизировать?
Поскольку поле json может быть очень длинным, виню в том числе и его. Если я выделю id в отдельную таблицу, есть ли ускорение?

Answer 1

[Viktor Koltcov]

Для поля id есть индекс, это первичный ключ?

3 проверка - по моему ужас. Уж лучше так:
SELECT id FROM notices WHERE id='123' OR id='234'...

Comments

[Viktor Koltcov]

Pavel Denisov: IN конечно лучше, но у IN вроде есть ограничение на количество элементов, или я ошибаюсь?

[Илья]

Viktor Koltcov Pavel Denisov
Cпасибо, сейчас попробую. У меня были сомнения по поводу того, сколько WHERE примет значений для проверки. Вариант с UNION ALL - первое, что нагуглил по множественной проверке. В теме особо не разбираюсь.

[Илья]

Viktor Koltcov: Судя по этому обсуждению, лимитов нет: stackoverflow.com/questions/1009706/postgresql-max...
В документации тоже ничего подобного не нашёл.

[Илья]

Pavel Denisov: Потрясающий вариант. Было 30 секунд, стало меньше одной. К сожалению, не могу отметить ваш комментарий как ответ, отмечаю всю ветку. Если хотите, для статистики, можете еще раз ответить ниже, я отмечу его тоже.

Answer 2

[Ivan Velichko]

У вас доступ фактически по ключу, храните в key-value хранилище просто, например в Redis.

Comments

[Илья]

Это только одна из таблиц, их больше на самом деле.

[Ivan Velichko]

Gorily: Тогда попробуйте сделать хранимую процедуру, в которой сначала делать выборку всех уже существующих id в таблице в массив, а затем вставку только тех строк, id которых в массиве не оказалось. Не совсем SQL-решение конечно, но сведется к одному SELECT и одному INSERT + цикл по массиву. До каких-то пределов по кол-ву id в одной выборке должно работать нормально.

[Илья]

Иван Величко: Суть именно в том, чтобы не делать лишние INSERT и вообще действия на стороне клиента. Дублей много, много обработок ошибок, а это точно скажется на производительности.

[Ivan Velichko]

Gorily: на стороне клиента будет всего одно действие: select * from my_stored_procedure(). И хранимка сама по себе будет содержать всего один select и один insert.

Answer 3

[Аристарх Загородников]

Вероятно в большинстве случаев объекты уже есть, так что может быть лучше получать отсутствующие?

select "id" from "notices" where "id" not in ('1', '2', ...)