Нетипичные оптимизации в PostgreSQL, или Креативное ускорение запросов

Когда речь заходит об оптимизации базы данных, разработчики обычно перечисляют привычный набор приёмов: слегка переписать запрос, накинуть индекс на колонку, денормализовать, сделать 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-3

Индекс применяется, и запрос быстрый, но...

Представление — рабочее решение, но у него та же проблема дисциплины: аналитики всё равно могут ходить напрямую в таблицу (и будут это делать). Можно отзывать права у таблицы или колдовать с 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-индекс — хороший способ обеспечить уникальность больших значений, на которые не должны ссылаться внешние ключи.