Составной индекс под фильтр и сортировку
Вопрос
Запрос SELECT id, created_at, amount FROM orders WHERE customer_id = 777 ORDER BY created_at DESC LIMIT 5 сейчас выполняется так: находит все заказы клиента и сортирует их.
Limit (cost=82.04..82.06 rows=5 width=23) (actual time=1.315..1.316 rows=5 loops=1)
-> Sort (cost=82.04..82.09 rows=20 width=23) (actual time=1.313..1.314 rows=5 loops=1)
Sort Key: created_at DESC
Sort Method: top-N heapsort Memory: 25kB
-> Bitmap Heap Scan on orders (cost=4.58..81.71 rows=20 width=23) (actual time=0.120..0.305 rows=20 loops=1)
Recheck Cond: (customer_id = 777)
Heap Blocks: exact=20
-> Bitmap Index Scan on orders_customer_id_idx (cost=0.00..4.58 rows=20 width=0) (actual time=0.101..0.101 rows=20 loops=1)
Index Cond: (customer_id = 777)
Допиши индекс, после которого план станет Limit -> Index Scan Backward using orders_cust_created_idx без узла Sort.
CREATE INDEX orders_cust_created_idx ON orders (__(1)__, __(2)__);
Попробуйте сами
Заполните пропуски по номерам.
Показать ответ и разбор
Коротко
Что в пропусках
customer_id
created_at или created_at desc
Разбор
Правило составного индекса: сначала колонки с условием равенства, затем колонка сортировки. Внутри одного customer_id записи индекса уже упорядочены по created_at, поэтому PostgreSQL читает их с конца (Index Scan Backward) и останавливается после 5 строк, без сортировки и без чтения остальных заказов клиента. На проверке это дало 0,09 мс против 2,1 мс. Порядок (created_at, customer_id) так не работает: равенство по второй колонке не даёт непрерывного отрезка индекса.
Ответьте сами, а не прочитайте
В тренажёре вопрос попадёт в повторения и вернётся, когда начнёт забываться. Плюс билеты с зачётом: 20 вопросов, не больше 2 ошибок.