Реклама
Комментарии 28

А вот ментата надо было харконненовского брать, он колоритнее.

Он как-то слишком уж довольно ухмыляется и потирает лапки — как бы чего недоброго не натворил…

А эта штука может быть расширена на другие СУБД? Хотя бы в принципе.
Алгоритмически — да, для любой СУБД, позволяющей получить план.
Просто нужен абсолютно другой парсер плана и другой набор подсказок == много-много экспертизы.
Ну да, я догадываюсь, что это нетривиально чисто технически. Т.е. парсим план во что-то типа дерева, потом ищем там паттерны, и даем на основе этого советы.
Ну постгрес умеет план в JSON возвращать, а оракл если я помню в XML…
Да, думаю что может. Одно время я занимался оптимизацией и улучшением производительности запросов в MS SQL (2008 — 2012 версий). В том числе через донастройку индексов. Описанное в статье выглядит очень знакомым и, в общем смысле, применимо к MS SQL. Вплоть до ситуации с WHERE… OR и ее решением через UNION — частый случай у нас был, хотя и в более изощренной форме.
Понимать, что такое план запроса — это очень важно, большая «О» и понимание, что там проиходит внутри базы — вообще бесценно.

Но, увы, далеко не все вникают так глубоко в делали.

У меня огромная просьба, когда вы решаете проблему медленного SELECT созданием очередного индекса — пишите там же, как это изменение повлиет на операции INSERT\UPDATE\DELETE.
Спасибо
У меня огромная просьба, когда вы решаете проблему медленного SELECT созданием очередного индекса — пишите там же, как это изменение повлиет на операции INSERT\UPDATE\DELETE.
Если в вашей базе сильно доминирует чтение, скажем 1000:1 (а это почти всегда так, за исключением очень редких случаев типа мониторинга), то вы +1 индекс заметите только с точки зрения объема БД, на пишущие операции оно повлияет микроскопически.
Если в вашей базе сильно доминирует чтение, скажем 1000:1 (а это почти всегда так, за исключением очень редких случаев

не согласен, у меня случаи "нужно писать постоянный поток" сплошь и рядом

CREATE INDEX ON tbl(fk_cli, pk DESC);

зачем тут desc в индексе? он нужен достаточно редко (в живых, а не учебных базах сходу и не вспоминается такой случай)

Это исключительно привычка — иметь индекс соответствующий прикладным запросам, чтобы не возникало в плане Index Scan Backward.
чтобы не возникало в плане Index Scan Backward

оно разве чем-то плохо?
ЕМНИП такой индекс нужен разве что для запросов вроде where fk_cli beween 1000 and 2000 order by fk_cli, pk desc (отбор по индексу + сортировка по нескольким полям индекса)

с чего бы это для условия A between 1000 and 2000 индекс (A, B) будет работать плохо?!?

Для этого условия он будет работать нормально. А вот необходимость 1000 раз заглянуть на уровень ниже и взять «последний» pk резко добавляет нагрузки.
Вот если бы order by fk_cli, pk desc limit 1000 без условия на fk_cli… Но это уже совсем другой запрос.
заглянуть на уровень ниже и взять «последний» pk

откуда взялся последний pk?

Из order by же. Как иначе мы сможем получить сортировку по паре ключей?

я вас не понимаю.
вот смотрите, пусть у нас есть миллион fk_cli, каждому соответсвует 10 pk.
нам надо выбрать примерно 10к записей, где fk_cli в отсортированы в прямом порядке, pk — в обратном (или наоборот, не важно).


в этом случае индекс (fk_cli, pk desc) будет работать идеально, ничего более оптимального нет (индекс даст и нужный диапазон, и нужную сортировку)


случай, конечно, достаточно надуманный, но других применений подобному индексу не придумывается

Посмотрите мою статью по ссылке выше. При BETWEEN-условии с сортировкой получается ровно вот этот эффект:

хорошо, каким способом можно выполнить этот (или экививалентный) запрос быстрее?

Иногда можно эффективно применить связку unnest + подзапрос/ARRAY.
Но это сильно зависит от условий — например, сколько реальных значений ожидается найти в BETWEEN-диапазоне (насколько он разрежен).

иногда можно. но не в этом случае. ничего быстрее фильтра «в лоб» по индексу тут не будет, можете проверить

Так я и не утверждал, что «всегда есть способ сделать лучше» — не всегда. А вот «тут индекс будет работать плохо» — правда, он и в тысяче других случаев может плохо работать. :)

да с чего вы взяли, что он будет работать плохо?
поиск по дереву, потом последовательный перебор страниц — именно то, для чего btree лучше всего подходит

Возьмем генератор датасета отсюда и модель индекса отсюда (разве что он по 3 полям, а не по 2):

-- задачи с указанным распределением
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+ данных.
Только полноправные пользователи могут оставлять комментарии. , пожалуйста.