Comments 28
А вот ментата надо было харконненовского брать, он колоритнее.
Просто нужен абсолютно другой парсер плана и другой набор подсказок == много-много экспертизы.
Но, увы, далеко не все вникают так глубоко в делали.
У меня огромная просьба, когда вы решаете проблему медленного SELECT созданием очередного индекса — пишите там же, как это изменение повлиет на операции INSERT\UPDATE\DELETE.
Спасибо
У меня огромная просьба, когда вы решаете проблему медленного SELECT созданием очередного индекса — пишите там же, как это изменение повлиет на операции INSERT\UPDATE\DELETE.Если в вашей базе сильно доминирует чтение, скажем 1000:1 (а это почти всегда так, за исключением очень редких случаев типа мониторинга), то вы +1 индекс заметите только с точки зрения объема БД, на пишущие операции оно повлияет микроскопически.
CREATE INDEX ON tbl(fk_cli, pk DESC);зачем тут desc в индексе? он нужен достаточно редко (в живых, а не учебных базах сходу и не вспоминается такой случай)
чтобы не возникало в плане Index Scan Backward
оно разве чем-то плохо?
ЕМНИП такой индекс нужен разве что для запросов вроде where fk_cli beween 1000 and 2000 order by fk_cli, pk desc (отбор по индексу + сортировка по нескольким полям индекса)
с чего бы это для условия A between 1000 and 2000 индекс (A, B) будет работать плохо?!?
Вот если бы
order by fk_cli, pk desc limit 1000 без условия на fk_cli… Но это уже совсем другой запрос.заглянуть на уровень ниже и взять «последний» pk
откуда взялся последний pk?
я вас не понимаю.
вот смотрите, пусть у нас есть миллион fk_cli, каждому соответсвует 10 pk.
нам надо выбрать примерно 10к записей, где fk_cli в отсортированы в прямом порядке, pk — в обратном (или наоборот, не важно).
в этом случае индекс (fk_cli, pk desc) будет работать идеально, ничего более оптимального нет (индекс даст и нужный диапазон, и нужную сортировку)
случай, конечно, достаточно надуманный, но других применений подобному индексу не придумывается
хорошо, каким способом можно выполнить этот (или экививалентный) запрос быстрее?
Но это сильно зависит от условий — например, сколько реальных значений ожидается найти в BETWEEN-диапазоне (насколько он разрежен).
иногда можно. но не в этом случае. ничего быстрее фильтра «в лоб» по индексу тут не будет, можете проверить
да с чего вы взяли, что он будет работать плохо?
поиск по дереву, потом последовательный перебор страниц — именно то, для чего btree лучше всего подходит
-- задачи с указанным распределением
CREATE TABLE task AS
WITH aid AS (
SELECT
id
, array_agg((random() * 999)::integer + 1) aids
FROM
generate_series(1, 1000) id
, generate_series(1, 20)
GROUP BY
1
)
SELECT
*
FROM
(
SELECT
id
, '2020-01-01'::date - (random() * 1e3)::integer task_date
, (random() * 999)::integer + 1 owner_id
FROM
generate_series(1, 100000) id
) T
, LATERAL(
SELECT
aids[(random() * (array_length(aids, 1) - 1))::integer + 1] author_id
FROM
aid
WHERE
id = T.owner_id
LIMIT 1
) a;
CREATE INDEX ON task(owner_id, task_date, id);
explain (analyze, buffers, costs off)
SELECT
*
FROM
task
WHERE
owner_id = ANY('{1,2,4,8,16,32,64,128,256,512}'::integer[])
ORDER BY
owner_id, task_date, id;"Index Scan using task_owner_id_task_date_id_idx on task (actual time=0.016..0.808 rows=946 loops=1)"
" Index Cond: (owner_id = ANY ('{1,2,4,8,16,32,64,128,256,512}'::integer[]))"
" Buffers: shared hit=974"
946 полученных записей в результате чтения 974 страниц данных. Это я и имею в виду, когда говорю, что индекс работает «нехорошо» — если для получения одной записи требуется читать 8KB+ данных.
Рецепты для хворающих SQL-запросов