Как сделать правильный комплексный индекс?

Question

[parallel47adm]

Есть довольно большая таблица (200млн+) в которой есть три интересующие колонки:
update_date timestamptz
num_status int
n_flag int

n_flag может быть 0..5
num_status 0..20
update_date - это когда запись была обновлена.

Надо найти самую старую по обновленности запись, где n_flag=0 и num_status>=10.

Запрос
select * from table1 where n_flag=0 and num_status>=10 order by update_date asc limit 1;
Очень дооолго.

select * from table1 where num_status>=10 order by update_date asc limit 1; - быстро
select * from table1 where n_flag=0 order by update_date asc limit 1; - быстро

На всех трех полях есть индекс.
Как сделать правильно?

Comments

[freeExec]

Так индекс с двумя условиями есть?

[parallel47adm]

freeExec, ... ON table1(num_status,n_flag); - пробовал. не помогло.

[galaxy]

select * from table1 where n_flag=0 and num_status>=10 order by update_date asc limit 1;

план покажите

В любом случае, сделайте ANALYZE на теблицу

Answer 1

[Melkij]

Надо найти самую старую по обновленности запись, где n_flag=0 и num_status>=10.

btree(update_date) where n_flag=0 and num_status>=10

Comments

[parallel47adm]

Да! Спасибо!
Помогло.

Answer 2

[Akina]

n_flag может быть 0..5
num_status 0..20
...
запись, где n_flag=0 и num_status>=10.

Указанным условиям при равномерном распределении значений соответствует ~9% записей, т.е. 18 млн. Причём отбор по n_flag более селективен.
Чисто теоретически оптимальным без использования фич Постгресса будет индекс (n_flag, update_date, num_status).

DEMO fiddle. Правда, 200 млн. записей я генерить как-то не решился... но для 1 млн. записей запрос показывает 40-50 мс - по-моему, вменяемо.

PS. Индекс называется на "комплексный", а композитный. Кроме того, этот индекс - покрывающий, т.е. для обработки запроса серверу не требуется обращаться к таблице.

Comments

[Сергей Томулевич]

1. В PostgreSQL нет понятия покрывающих индексов. Данная функциональность только внедряется и не работает на 100%.
2. В PostgreSQL есть частичные индексы и Melkij тут совершенно прав, загнав статичное условие в частичный индекс. Не считая того, что условие ">=" в принципе потребует Recheck Condition. А update_date на селективность влияет ровно никак, так как по нему ведется сортировка, а значит это поле должно быть последним по определению, так работает планировщик PostgreSQL

[Akina]

Сергей Томулевич,

В PostgreSQL нет понятия покрывающих индексов.

1. Понятие покрывающего индекса никак не связано с СУБД.
   
2. Если PostgreSQL не умеет использовать покрывающий индекс, то объясни мне, почему удаление поля из индекса (fiddle) изменяет план и вместо Index Only Scan using idx1 on test пишет Index Scan using idx1 on test. Что сервер подразумевает в первом случае, когда добавляет слово Only? Я воспринимаю это как мне достаточно индекса, все значения я возьму из него, в саму таблицу лазить не потребуется. Это неверно? тогда приведите своё пояснение происходящего.
   

В PostgreSQL есть частичные индексы

Мне почти очевидно, что значения 0 и 10 в условии Надо найти самую старую по обновленности запись, где n_flag=0 и num_status>=10. - это не константы на веки вечные, а параметры, которые регулярно меняются от вызова к вызову. И второе условие к тому же регулярно меняет оператор - то =, то <=, то вообще BETWEEN... а если это действительно так, то идею с частичным индексом можно смело спускать в унитаз.

[galaxy]

Akina,

Если PostgreSQL не умеет использовать покрывающий индекс, то объясни мне, почему удаление поля из индекса (fiddle) изменяет план и вместо Index Only Scan using idx1 on test пишет Index Scan using idx1 on test. Что сервер подразумевает в первом случае, когда добавляет слово Only?

формально их нет, это принципиальное ограничение архитектуры постгреса, где в индексах не хранится информация о транзакционной видимости.
Но есть костыль, достаточно неплохой

[Akina]

galaxy, гм... нет, получается, что использовать-то он их может, вот только того профита, который обеспечивают покрывающие индексы в других СУБД, не будет, ибо всё одно придётся сбегать в тело таблицы. Битовые карты просто снимают остроту проблемы, делая возможным это обращение только для сравнительно свежих записей, т.е. уменьшая необходимое количество обращений к таблице. А для статической таблицы таких обращений в тело вообще не будет - впрочем, по карте пробежаться придётся, никуда не денешься.
То есть получается, что "PostgreSQL не умеет использовать покрывающий индекс полноценно". Но тем не менее извлекать кое-какой профит из покрывающего индекса он умеет.