Когда речь заходит об оптимизации базы данных, разработчики обычно перечисляют привычный набор приёмов: слегка переписать запрос, накинуть индекс на колонку, денормализовать, сделать analyze, vacuum, cluster, и так по кругу. Классические техники, конечно, работают, но иногда креативный подход даёт гораздо больше.
Избавляемся от полного сканирования таблицы с помощью check-ограничений
Представим, что у нас есть таблица пользователей:
db=# CREATE TABLE users ( id INT PRIMARY KEY, username TEXT NOT NULL, plan TEXT NOT NULL, CONSTRAINT plan_check CHECK (plan IN ('free', 'pro')) ); CREATE TABLE
Для каждого пользователя мы храним имя и тарифный план. Планов всего два — free и pro, поэтому добавим check-ограничение.
Сгенерируем данные и проанализируем таблицу:
db=# INSERT INTO users SELECT n, uuidv4(), (ARRAY['free', 'pro'])[ceil(random()*2)] FROM generate_series(1, 100_000) AS t(n); INSERT 0 100000 db=# ANALYZE users; ANALYZE
Теперь в системе 100 тысяч пользователей.
Честные ошибки
Мы выдали доступ к этой таблице аналитику, чтобы он обращался к ней из своего любимого инструмента для сбора отчётности. Вот первый запрос, который он отправил:
db=# SELECT * FROM users WHERE plan = 'Pro'; id │ username │ plan ────┼──────────┼────── (0 rows)
Запрос ничего не вернул, аналитик в недоумении. Почему в системе нет пользователей на плане "Pro"?
Потому что план называется pro, а не Pro (с заглавной P). Обычная человеческая ошибка, такое может случиться с кем угодно. Но сколько она стоит?
Посмотрим план выполнения для запроса с несуществующим значением:
db=# EXPLAIN ANALYZE SELECT * FROM users WHERE plan = 'Pro'; QUERY PLAN ────────────────────────────────────────────────────────────────── Seq Scan on users (cost=0.00..2185.00 rows=1 width=45) (actual time=7.406..7.407 rows=0.00 loops=1) Filter: (plan = 'Pro'::text) Rows Removed by Filter: 100000 Buffers: shared hit=935 Planning: Buffers: shared hit=29 read=2 Planning Time: 4.564 ms Execution Time: 7.436 ms
PostgreSQL просканировал всю таблицу, хотя в поле есть check-ограничение: ни одна строка не может иметь значение Pro, и БД это гарантирует. Если условие заведомо ложно, зачем сканировать таблицу?
Используем constraint_exclusion
PostgreSQL может сообразить, что таблицу не надо сканировать, если в запросе есть условие, которое всегда ложно. Но по умолчанию он пропускает сканирование не всегда. Чтобы при построении плана учитывались ограничения, нужно выставить параметр constraint_exclusion:
db=# SET constraint_exclusion to 'on'; SET db=# EXPLAIN ANALYZE SELECT * FROM users WHERE plan = 'Pro'; QUERY PLAN ─────────────────────────────────────────────────────────────────────────────────────── Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.001 rows=0.00 loops=1) One-Time Filter: false Planning: Buffers: shared hit=5 read=4 Planning Time: 5.760 ms Execution Time: 0.008 ms (6 rows)
Отлично. После включения constraint_exclusion PostgreSQL по check-ограничению понял, что строк не будет, и полностью убрал сканирование.
Когда уместен параметр constraint_exclusion
Что же такое constraint_exclusion и почему он не включен по умолчанию?
Сейчас constraint_exclusion по умолчанию для включён только для таблиц, секционированных с использованием наследования. Если включить его для всех таблиц, накладные расходы на планирование станут заметными, особенно для простых запросов, и чаще всего это не даст выигрыша.
По умолчанию параметру constraint_exclusion присвоено значение partition. С такой настройкой при обращении к секционированным таблицам целиком отбрасываются ненужные секции. Это называется partition pruning.
В документации сказано, что для простых запросов стоимость проверки всех ограничений может перевесить выигрыш. В итоге на планирование можно потратить больше времени, чем на само выполнение. Логично, что системные запросы реже запрашивают некорректные значения или противоречат ограничениям. Но для разовых ручных запросов из BI-инструментов это не так.
В BI- и отчётных системах пользователи часто пишут сложные запросы вручную. В такой среде ошибки, как у нашего аналитика, вполне нормальны. Поэтому в окружениях аналитики и хранилищ данных, где много разовых запросов, установка constraint_exclusion = on может реально экономить время и ресурсы за счёт исключения лишних полных сканирований таблиц.
Оптимизация под низкую кардинальность с индексом по функции
Предст��вьте таблицу продаж такого вида:
db=# CREATE TABLE sale ( id INT PRIMARY KEY, sold_at TIMESTAMPTZ NOT NULL, charged INT NOT NULL ); CREATE TABLE
Допустим, мы храним время продажи и сумму покупки. Создадим 10 миллионов продаж и проанализируем таблицу:
db=# INSERT INTO sale (id, sold_at, charged) SELECT n AS id, '2025-01-01 UTC'::timestamptz + (interval '5 seconds') n AS sold_at, ceil(random() 100) AS charged FROM generate_series(1, 10_000_000) AS t(n); INSERT 0 10000000 db=# ANALYZE sale; ANALYZE
Накидываем индекс B-Tree
Если ваши аналитики строят ежедневные отчёты по продажам, их запросы выглядят примерно так:
db=# EXPLAIN (ANALYZE ON, BUFFERS OFF, COSTS OFF) SELECT date_trunc('day', sold_at AT TIME ZONE 'UTC'), SUM(charged) FROM sale WHERE '2025-01-01 UTC' <= sold_at AND sold_at < '2025-02-01 UTC' GROUP BY 1; QUERY PLAN ────────────────────────────────────────────────────────────────────────────────── HashAggregate (actual time=626.074..626.310 rows=32.00 loops=1) Group Key: date_trunc('day'::text, sold_at) Batches: 1 Memory Usage: 2081kB -> Seq Scan on sale (actual time=6.428..578.135 rows=535679.00 loops=1) Filter: (('2025-01-01 02:00:00+02'::timestamp with time zone <= sold_at) AND (sold_at < '2025-02-01 02:00:00+02'::timestamp with time zone)) Rows Removed by Filter: 9464321 Planning Time: 0.115 ms Execution Time: 627.119 ms
PostgreSQL просканировал всю таблицу, и запрос выполнился примерно за 627 мс. Аналитики немного избаловались, и для них это долго. Вы делаете то, что обычно делают в таких случаях, — накидываете B-Tree-индекс:
db=# CREATE INDEX sale_sold_at_ix ON sale(sold_at); CREATE INDEX
Запускаем тот же запрос с индексом:
db=# EXPLAIN (ANALYZE ON, BUFFERS OFF, COSTS OFF) SELECT date_trunc('day', sold_at AT TIME ZONE 'UTC'), SUM(charged) FROM sale WHERE '2025-01-01 UTC' <= sold_at AND sold_at < '2025-02-01 UTC' GROUP BY 1; QUERY PLAN ─────────────────────────────────────────────────────────────────────────────────────────────────── HashAggregate (actual time=186.970..187.212 rows=32.00 loops=1) Group Key: date_trunc('day'::text, sold_at) Batches: 1 Memory Usage: 2081kB -> Index Scan using sale_sold_at_ix on sale (actual time=0.038..137.067 rows=535679.00 loops=1) Index Cond: ((sold_at >= '2025-01-01 02:00:00+02'::timestamp with time zone) AND (sold_at < '2025-02-01 02:00:00+02'::timestamp with time zone)) Index Searches: 1 Planning Time: 0.261 ms Execution Time: 187.363 ms
Время выполнения упало с ~627 мс до 187 мс, аналитики довольны. Но какой ценой?
db=# \di+ sale_sold_at_ix List of indexes ─[ RECORD 1 ]─┬──────────────── Schema │ public Name │ sale_sold_at_ix Type │ index Owner│ haki Table│ sale Persistence │ permanent Access method │ btree Size │ 214 MB Description │ ¤
Индекс весит 214 МБ. Это почти половина размера всей таблицы. Аналитики рады, а мы — не очень.
Создать индекс B-Tree по умолчанию привычно, но DBA и разработчики часто игнорируют цену хранения и стоимость обслуживания такого индекса. А ведь простыми мерами можно сэкономить место и деньги.
Смотрим на задачу по-новому
Сделаем шаг назад и посмотрим, что именно мы оптимизировали. Аналитикам нужен отчёт за день, а мы дали индекс, который обеспечивает точность до миллисекунд. Индексируя и дату, и время, мы дали гораздо больше, чем нас просили.
Что если индексировать только дату, без времени?
db=# CREATE INDEX sale_sold_at_date_ix ON sale((date_trunc('day', sold_at AT TIME ZONE 'UTC'))::date); CREATE INDEX
Так создаётся индекс по выражению. В качестве выражения здесь — дата продажи. Сначала задаём часовой пояс, потом обрезаем до даты, чтобы получилось так, как аналитики пишут в отчётах.
Сначала посмотрим размеры индексов:
db=# \di+ sale_sold_at_* List of indexes Name │ Table│ Access method │ Size ──────────────────────┼──────┼───────────────┼──────── sale_sold_at_date_ix │ sale │ btree │ 66 MB sale_sold_at_ix │ sale │ btree │ 214 MB
Индекс по выражению занимает всего 66 МБ, то есть в три с лишним раза меньше полного индекса. Да, date меньше timestamptz (4 байта против 8), но основная экономия не в этом. У такого индекса меньше различных значений, и PostgreSQL может сжать его с помощью дедупликации.
Чтобы проверить, как отработает более компактный индекс, сначала удалим полный:
db=# DROP INDEX sale_sold_at_ix; DROP INDEX
Немного поправим текст запроса, чтобы он смог использовать индекс по выражению (к этому ещё вернёмся):
db=# EXPLAIN (ANALYZE ON, BUFFERS OFF, COSTS OFF) SELECT date_trunc('day', sold_at AT TIME ZONE 'UTC'), SUM(charged) FROM sale WHERE date_trunc('day', sold_at AT TIME ZONE 'UTC')::date BETWEEN '2025-01-01' AND '2025-01-31' GROUP BY 1; QUERY PLAN ───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── GroupAggregate (actual time=6.499..145.889 rows=31.00 loops=1) Group Key: date_trunc('day'::text, (sold_at AT TIME ZONE 'UTC'::text)) -> Index Scan using sale_sold_at_date_ix on sale (actual time=0.015..119.832 rows=535679.00 loops=1) Index Cond: ((date_trunc('day'::text, (sold_at AT TIME ZONE 'UTC'::text))::date >= '2025-01-01 00:00:00'::timestamp without time zone) AND (date_trunc('day'::text, (sold_at AT TIME ZONE 'UTC'::text))::date < '2025-02-01 00:00:00'::timestamp without time zone)) Index Searches: 1 Planning Time: 0.151 ms Execution Time: 145.913 ms
Индекс использовался, и запрос выполнился за 145 мс. Это примерно на 20 мс быстрее, чем с полным индексом, и в 4,5 раза быстрее, чем полное сканирование таблицы.
Проблема дисциплины
С индексом по функции есть тонкость: подход довольно хрупкий. Достаточно чуть-чуть поменять выражение, и база уже не сможет использовать индекс:
db=# EXPLAIN (ANALYZE OFF, COSTS OFF) SELECT (sold_at AT TIME ZONE 'UTC')::date, SUM(charged) FROM sale WHERE (sold_at AT TIME ZONE 'UTC')::date BETWEEN '2025-01-01' AND '2025-01-31' GROUP BY 1; QUERY PLAN ──────────────────────────────────────────────────────────────────────────────────── HashAggregate Group Key: ((sold_at AT TIME ZONE 'UTC'::text))::date -> Seq Scan on sale Filter: ((((sold_at AT TIME ZONE 'UTC'::text))::date >= '2025-01-01'::date) AND (((sold_at AT TIME ZONE 'UTC'::text))::date <= '2025-01-31'::date))
Запрос, по сути, тот же самый, но выражение изменили: вместо date_trunc использовали ::date. �� итоге база не смогла задействовать индекс по выражению.
Чтобы всегда использовать одно и то же выражение, нужна дисциплина, которой в реальных командах почти никогда не бывает. Наивно ждать, что это будет надёжно работать само по себе. Нужен способ принудительно использовать именно это выражение.
Раньше это решали с помощью представления:
db=# CREATE VIEW v_sale AS SELECT *, date_trunc('day', sold_at AT TIME ZONE 'UTC')::date AS sold_at_date FROM sale; CREATE VIEW
Представление добавляет вычисляемую колонку sold_at_date с тем же выражением, по которому мы определили индекс. Используя представление, мы гарантируем, что база сможет применить индекс:
db=# EXPLAIN (ANALYZE OFF, COSTS OFF) SELECT sold_at_date, SUM(charged) FROM v_sale WHERE sold_at_date BETWEEN '2025-01-01' AND '2025-01-31' GROUP BY 1; QUERY PLAN ────────────────────────────────────────────────────────────────────────────────────────────────────────────────── GroupAggregate Group Key: (date_trunc('day'::text, (sale.sold_at AT TIME ZONE 'UTC'::text)))::date -> Index Scan using sale_sold_at_date_ix on sale Index Cond: (((date_trunc('day'::text, (sold_at AT TIME ZONE 'UTC'::text)))::date >= '2025-01-01'::date) AND ((date_trunc('day'::text, (sold_at AT TIME ZONE 'UTC'::text)))::date <= '2025-01-31'::date))
Индекс применяется, и запрос быстрый, но...
Представление — рабочее решение, но у него та же проблема дисциплины: аналитики всё равно могут ходить напрямую в таблицу (и будут это делать). Можно отзывать права у таблицы или колдовать с search_path, подсовывая представление вместо таблицы, но есть путь проще.
Используем виртуальные генерируемые столбцы
Начиная с версии 14, PostgreSQL поддерживает генерируемые столбцы, которые автоматически заполняются выражением при вставке строки. Почти то, что нужно, но есть нюанс: результат выражения материализуется, то есть занимает дополнительное место. А мы как раз пытались это место экономить.
К счастью, с версии 18 появились виртуальные генерируемые столбцы. Они выглядят как обычные, но на деле их значение вычисляется при каждом обращении. По сути, то, чего мы пытались добиться через представление.
Сначала добавим в таблицу виртуальный генерируемый столбец с тем же выражением, которое индексировали:
db=# ALTER TABLE sale ADD sold_at_date DATE GENERATED ALWAYS AS (date_trunc('day', sold_at AT TIME ZONE 'UTC')); ALTER TABLE
Теперь выполним запрос через виртуальный генерируемый столбец:
db=# EXPLAIN (ANALYZE ON, COSTS OFF, BUFFERS OFF) SELECT sold_at_date, SUM(charged) FROM sale WHERE sold_at_date BETWEEN '2025-01-01' AND '2025-01-31' GROUP BY 1; QUERY PLAN ────────────────────────────────────────────────────────────────────────────────────────────────────────────────── GroupAggregate (actual time=7.047..162.965 rows=31.00 loops=1) Group Key: (date_trunc('day'::text, (sold_at AT TIME ZONE 'UTC'::text)))::date -> Index Scan using sale_sold_at_date_ix on sale (actual time=0.015..134.795 rows=535679.00 loops=1) Index Cond: (((date_trunc('day'::text, (sold_at AT TIME ZONE 'UTC'::text)))::date >= '2025-01-01'::date) AND ((date_trunc('day'::text, (sold_at AT TIME ZONE 'UTC'::text)))::date <= '2025-01-31'::date)) Index Searches: 1 Planning Time: 0.128 ms Execution Time: 162.989 ms
Используя виртуальный генерируемый столбец, мы можем быть уверены, что в запросе используется именно то выражение, которое мы индексировали. PostgreSQL в таком случае может применить индекс, и запрос работает быстро.
У этого подхода есть несколько плюсов:
Меньший индекс. Меньше различных значений, значит база может эффективнее применять дедупликацию и уменьшать индекс.
Более быстрый запрос, Узкий и точечный индекс требует меньше ресурсов, поэтому запрос отрабатывает быстрее.
Не требуется принуждение. Вычисляемую колонку использовать просто, а индекс гарантированно применим.
Полный порядок. Когда команда должна руками везде писать одно и то же выражение, ошибки и расхождения неизбежны, особенно с часовыми поясами. Виртуальная вычисляемая колонка убирает эту неопределённость.
Индексация виртуальных вычисляемых колонок
Следующий логичный шаг — создать индекс прямо на виртуальном столбце. К сожалению, на момент написания статьи PostgreSQL 18 не поддерживал такие индексы:
db=# CREATE INDEX sale_sold_at_date_ix ON sale(sold_at_date); ERROR: indexes on virtual generated columns are not supported
Будем надеяться, что индексы на виртуальных виртуальных генерируемых столбцах появятся в PostgreSQL 19.
Обеспечиваем уникальность через Hash-индекс
Представим систему, которая извлекает информацию из URL. Создадим такую таблицу:
CREATE TABLE urls ( id INT PRIMARY KEY, url TEXT NOT NULL, data JSON );
Добавим записи:
db=# INSERT INTO urls (id, url) SELECT n, 'https://' uuidv4() '.com/ ' uuidv4() '?p=' || uuidv4() FROM generate_series(1, 1_000_000) AS t(n); INSERT 0 1000000
Обработка веб-страниц может быть ресурсоёмкой, долгой и дорогой, поэтому важно гарантировать, что одна и та же страница не обрабатывается повторно.
Накидываем уникальный B-Tree по привычке
Чтобы URL не обрабатывались дважды, добавим ограничение уникальности на колонку url:
db=# CREATE UNIQUE INDEX urls_url_unique_ix ON urls(url);
Теперь один и тот же URL точно не обработается повторно:
db=# INSERT INTO urls(id, url) VALUES (1_000_001, 'https://hakibenita.com'); INSERT 0 1 db=# INSERT INTO urls(id, url) VALUES (1_000_002, 'https://hakibenita.com'); ERROR: duplicate key value violates unique constraint "urls_url_unique_ix" DETAIL: Key (url)=(https://hakibenita.com) already exists.
Уникальность ограничения обеспечивается уникальным индексом B-Tree. Заодно получаем приятный бонус: быстрый поиск конкретного URL.
db=# EXPLAIN (ANALYZE ON, BUFFERS OFF, COSTS OFF) SELECT * FROM urls WHERE url = 'https://hakibenita.com'; QUERY PLAN ────────────────────────────────────────────────────────────────────────────────────────── Index Scan using urls_url_unique_ix on urls (actual time=0.018..0.018 rows=1.00 loops=1) Index Cond: (url = 'https://hakibenita.com'::text) Index Searches: 1 Planning Time: 0.173 ms Execution Time: 0.046 ms
URL у страниц бывают очень длинными. Некоторые веб-приложения вообще кладут в URL почти всё состояние приложения. Для пользователей это удобно, но хранить такие URL слишком накладно.
Посмотрим размер таблицы и индекса B-Tree, который обеспечивает уникальность:
db=# \dt+ urls List of tables ─[ RECORD 1 ]─┬────────── Schema │ public Name │ urls Type │ table Owner │ haki Persistence │ permanent Access method │ heap Size │ 160 MB Description │ ¤ db=# \di+ urls_url_unique_ix List of indexes ─[ RECORD 1 ]─┬─────────────────── Schema │ public Name │ urls_url_unique_ix Type │ index Owner │ haki Table │ urls Persistence │ permanent Access method │ btree Size │ 154 MB Description │ ¤
Размер таблицы — 160 МБ, а индекс — аж 154 МБ.
Уникальный Hash-индекс
B-Tree хранит в листовых блоках сами индексируемые значения. Поэтому на больших значениях индекс B-Tree может разрастаться очень сильно.
В PostgreSQL есть другой тип индекса — Hash index (статья про Hash-индексы). Он хранит не сами значения, а их хеши, которые намного компактнее. На больших значениях с низкой повторяемостью Hash-индекс особенно хорош.
Звучит так, будто Hash-индекс отлично подходит для обеспечения уникальности. Попробуем создать уникальный Hash-индекс:
db=# CREATE UNIQUE INDEX urls_url_unique_hash ON urls USING HASH(url); ERROR: access method "hash" does not support unique indexes
Увы, PostgreSQL не поддерживает уникальные Hash-индексы. Но это не значит, что с его помощью нельзя обеспечить уникальность.
Как обеспечить уникальность с помощью Hash-индекса
В PostgreSQL есть специальный тип ограничений — ограничение-исключение (exclusion constraint). Это не самый известный и не самый популярный инструмент, который чаще вспоминают вместе с GIN/GiST как способ запрещать пересекающиеся диапазоны. Но с помощью exclusion-ограничений как раз и можно обеспечить уникальность с помощью Hash-индекса:
db=# ALTER TABLE urls ADD CONSTRAINT urls_url_unique_hash EXCLUDE USING HASH (url WITH =); ALTER TABLE
Так мы добавляем exclusion-ограничение, которое запрещает две строки с одинаковым URL, то есть гарантирует уникальность. Реализовано это через Hash-индекс. Получается, Hash-индекс обеспечивает уникальность.
Сначала проверим, что уникальность реально соблюдается:
db=# INSERT INTO urls (id, url) VALUES (1_000_002, 'https://hakbenita.com/postgresql-hash-index'); INSERT 0 1 db=# INSERT INTO urls (id, url) VALUES (1_000_003, 'https://hakbenita.com/postgresql-hash-index'); ERROR: conflicting key value violates exclusion constraint "urls_url_unique_hash" DETAIL: Key (url)=(https://hakbenita.com/postgresql-hash-index) conflicts with existing key (url)=(https://hakbenita.com/postgresql-hash-index).
Попытка вставить уже существующий URL привела к ошибке exclusion-ограничения.
Посмотрим, может ли этот Hash-индекс ускорять запросы с условием по конкретному URL.
db=# EXPLAIN (ANALYZE ON, BUFFERS OFF, COSTS OFF) SELECT * FROM urls WHERE url = 'https://hakibenita.com'; QUERY PLAN ──────────────────────────────────────────────────────────────────────────────────────────── Index Scan using urls_url_unique_hash on urls (actual time=0.010..0.011 rows=1.00 loops=1) Index Cond: (url = 'https://hakibenita.com'::text) Index Searches: 1 Planning Time: 0.178 ms Execution Time: 0.022 ms
Да, и в этом случае даже быстрее, чем B-Tree (0.022 мс против 0.046 мс).
И наконец, сравним размеры индексов двух типов:
db=# \di+ urls_url_* List of indexes Name │ Access method │ Size ─────────────────────┼───────────────┼──────── urls_url_unique_hash │ hash │ 32 MB urls_url_unique_ix │ btree │ 154 MB
Впечатляет. Hash-индекс в 5 раз меньше соответствующего B-Tree. Вместо хранения длинных URL в листовых страницах B-Tree Hash-индекс хранит только хеши, поэтому размер значительно уменьшается.
Ограничения «уникальных» exclusion constraints
Если обеспечивать ��никальность с помощью exclusion constraint + Hash-индекс, можно серьёзно сэкономить место и ускорить запросы. Но есть несколько оговорок:
⚠️ На колонку нельзя ссылаться внешними ключами
PostgreSQL требует, чтобы внешний ключ ссылался на ограничение уникальности. Так как нельзя создать ограничение уникальности, поддерживаемое Hash-индексом, внешний ключ на него сослаться не сможет:
db=# CREATE TABLE foo (url TEXT REFERENCES urls(url)); ERROR: there is no unique constraint matching given keys for referenced table "urls"
⚠️ Ограничения на INSERT ... ON CONFLICT
Предложение ON CONFLICT в INSERT очень популярно и удобно для синхронизации данных. Но использовать его с exclusion-ограничением не так просто.
Попытка использовать exclusion constraint через список полей в ON CONFLICT ... DO NOTHING может закончиться ошибкой:
db=# INSERT INTO urls (id, url) VALUES (1_000_004, 'https://hakibenita.com') ON CONFLICT (url) DO NOTHING; ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
Сообщение намекает, что использовать exclusion-ограничение всё-таки можно. Это правда, но через ON CONFLICT ON CONSTRAINT:
db=# INSERT INTO urls (id, url) VALUES (1_000_004, 'https://hakibenita.com') ON CONFLICT ON CONSTRAINT urls_url_unique_hash DO NOTHING; INSERT 0 0
А вот ON CONFLICT ... DO UPDATE здесь вообще не работает, даже с ON CONFLICT ON CONSTRAINT:
db=# INSERT INTO urls (id, url) VALUES (1_000_004, 'https://hakibenita.com') ON CONFLICT ON CONSTRAINT urls_url_unique_hash DO UPDATE SET id = EXCLUDED.id; ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints
Лично я не люблю завязывать SQL на имена ограничений, поэтому для обхода обоих ограничений использовал бы MERGE:
db=# MERGE INTO urls t USING (VALUES (1000004, 'https://hakibenita.com')) AS s(id, url) ON t.url = s.url WHEN MATCHED THEN UPDATE SET id = s.id WHEN NOT MATCHED THEN INSERT (id, url) VALUES (s.id, s.url); MERGE 1
И напоследок проверим план выполнения, чтобы убедиться, что запрос может использовать Hash-индекс:
QUERY PLAN ──────────────────────────────────────────────────────────────────────────────────────────── Merge on urls t (cost=0.00..8.04 rows=0 width=0) -> Nested Loop Left Join (cost=0.00..8.04 rows=1 width=6) -> Result (cost=0.00..0.01 rows=1 width=0) -> Index Scan using urls_url_unique_hash on urls t (cost=0.00..8.02 rows=1 width=6) Index Cond: (url = 'https://hakibenita.com'::text)
Может и использует!
Несмотря на эти небольшие ограничения и неудобства, Hash-индекс — хороший способ обеспечить уникальность больших значений, на которые не должны ссылаться внешние ключи.