PostgreSQL выбирает разные индексы для разных LIMIT

Постгрес оценивает множество параметров для построения плана запроса и использует некоторые эвристики. То что для конкретного запроса работают магические 78/79 это просто совпадение.

Рекомендую к прочтению:

https://postgrespro.ru/docs/postgrespro/9.5/runtime-config-query (немного устаревшая информация, актуальная - в англоязычной документации)

и как показательный пример: https://habr.com/ru/articles/444018/

практически идентичный вопрос на стековерфлоу: https://stackoverflow.com/questions/8566931/index-not-used-when-limit-is-used-in-postgres

В чём проблема посмотреть план запроса?

Могу, конечно, через hint_plan указать правильный IndexScan

Нет не можешь. Через хинт ты можешь увеличить стоимость определённых операций, т.е. ты можешь указать не правильный IndexScan. И да, это очень тонкий инструмент, если ты не понимаешь как делать и читать планы, то это верный и безошибочный способ сделать хуже.

В общем, ищешь любой материал по анализу запросов и погружаешься в него. На хабре помню была очень не плохая статья по данной теме.

пс.

https://habr.com/ru/companies/tensor/articles/790282/ с картинками :-) Для началу самое то.

практически идентичный вопрос на стековерфлоу:

ничего идентичного не вижу. Почему без ORDER BY для ПГ выгоднее 100 первых попавшихся записей прочитать простым SeqScan - вполне понятно.

У меня тут в WHERE (грубо говоря) id = 10. Так вот при id = 10 всё плохо с выбранным индексом, а при id = 20 - именно этот выбор (по полю в order by) и оказывается верным. Точнее - более быстрым.

Если я вручную указываю IndexScan id - получаю вполне приемлемое время и для 10, и 20.

А если не указываю, и полагаюсь на планировщик, то при 20 он делает лучше меня, а при 10 практически в стоп попадает.

зачем ты два раза пишешь одно и то же но другими словами? если ты ничего идентичного не видишь, возможно просто не в коня корм. причём здесь order by вообще? простой select безо всяких order может быть выгоднее с seqscan против index scan тупо потому что планировщик оценил что последовательная запись к диску быстрее чем прыжки по диску для доступа к индексу. оценки эти основаны на рантайм опциях и константах, ссылки на которые я дал выше. попробуй хоть немного подумать головой, потому что сейчас твои вопросы (особенно второй) выглядят максимально тупо, как-будто ты вообще не соображаешь как работает база, но при этом пытаешься за умного сойти

... на мой взгляд - сам индекс на поле из ORDER BY плохой. Там дата. На полумиллиарде записей, если получилось допустим сотня миллионов дат - то плохо понимаю, как он вообще может пригодиться людям. Он же почти ничем не отличается от SeqScan. Так? С чего вдруг ПГ решает что бегать по такому жирному индексу и отсчитывать LIMIT может быть выгоднее отбора по id?

И что характерно - при некоторых условиях (если записи не очень далекие) - таки угадывает даже.

---

Вы бы не хамили незнакомым людям. Просто на всякий случай.

Он же почти ничем не отличается от SeqScan. Так?

Не совсем так. Индекс всё-таки заранее упорядочен, в отличие от данных которые нужно будет сортировать для order by.

Вот я решил, что буду перебирать все даты подряд, пока не наберу 78 штук записей, у которых нужный id (неудачно назвал - это не ключ записи на самом деле, просто какое-то «id», ключ другой).

Хорошо, пошел по упорядоченному индексу дат. Сходил в запись, в саму таблицу - убедился, что id не подходит. Сходил ещё 1000 раз - опять не оно. Сходил 1000 000 раз - О! вот одна нашлась. Потом опять миллион раз попусту перебираю подряд.

И т.д.

Нет? Не так?

Хорошо, пошел по упорядоченному индексу дат. Сходил в запись, в саму таблицу - убедился, что id не подходит

ЯННП, при чём тут какой-то id? Если тебе нужно выбирать/сортировать по id, то и индекс должен быть по id.

EXPLAIN SELECT
	[some_fields_list]
FROM
	tab1
WHERE
	tab1.some_id = 10
ORDER BY
	tab1.some_date
LIMIT x
;

При LIMIT 79 - Parallel Index Scan using idx_tab_some_id - и сотые доли секунды
При LIMIT 78 - Parallel Index Scan using idx_tab_some_date - и полчаса

Ладно, проехали. Не понял и не понял.

А по дате запрос не проще делать? Типо WHERE date >= $1 AND date < $2 А потом уже в коде резать до нужного количества. Вообще если где-то медленно почти всегда проще и быстрее самому отфильтровать и отсортировать в коде приложения, запросы же к postgresql поменять на наиболее простые и более эффективные, которые точно уже известно, что эффективны

Мне тоже кажется, что там полно мест которые и проще, и надежнее вынести на сторону клиента.

Но нельзя - задача повторять функциональность процедур T-SQL со всеми их странностями теперь в ПГ.

Одна процедура с кучей хитромудрых запросов по разным источникам с откладыванием выборки в одну итоговую временную таблицу с динамическими лимитами каждый раз (в зависимости от результата предыдущего запроса).

Пока вроде как выглядит так, что полное переписывание с временных таблиц на CTE каким-то образом починило ситуацию. Сейчас без хинтов ПГ всё правильно делает. И без временных таблиц.

Вообще не представляю, почему оно выбирает индекс по дате, если в конечном счёте вас интересуют айдишники.

Второй план действительно выливается в голимый SeqScan по дереву… Явно ещё и напрягая диск своими обращениями.

Может ему статистика шепчет на ухо, мол использовать первый индекс может вылиться во что-то страшное.

Может, статистика думает, что вы со своим запросом получите очень много строк и их придётся мучительно сортировать? Или о чем оно может ещё думать…

Может статистика слишком мала, или обновляется слишком редко? Или может она права и у вас в БД где-то присутствуют вот такие страшные крайние случаи.

Я это на кофейной гуще гадаю, опыта работы со столь большими БД у меня не было.

Вижу пару идей для решения:

0. Пошаманить над статистикой

1. Составной индекс по tab1(some_id, some_date). Но как бы не знаю. Может оно и его проигнорирует. 😃 Да и у вас БД большая, не знаю, насколько можете себе позволить вкорячивать ещё индексов, особенно, если оно сильно и не нужно.

2. Если вы уверены, что одинаковых some_id всегда относительно немного, то почему бы просто не вынести выборку по some_id в подзапрос или временную таблицу, а отсортировать/отлимитить уже снаружи? Возможно, так ему использовать idx_tab_some_id покажется более привлекательным.

Судя по последнему вашему сообщению, вы именно так (п.2) и сделали?

вы именно так (п.2) и сделали?

Нет, едва ли.

Насколько я понимаю - когда заменил относительно мелкие запросы с INSERT результата во временную таблицу и подсчетом count(*) отобранных записей с передачей посчитанного в переменной в LIMIT следующего запроса -> на один сплошной CTE с подсчетом count(*) в LIMIT динамически сразу по месту - в ПГ отключились какие-то алгоритмы оптимизаций. Он же теперь не может знать сколько именно там в LIMIT получится. Вот и перестаёт умничать с выбором индекса.

Т.е. на том условном псевдокоде - вот так:

EXPLAIN 
WITH cte_1 as (
SELECT 1 FROM generate_series(1,78)
)
SELECT
	[some_fields_list]
FROM
	tab1
WHERE
	tab1.some_id = 10
ORDER BY
	tab1.some_date
LIMIT (SELECT count(*) FROM cte_1)
;

-----

Думал тут местные крутые сишники быстренько посмотрят в исходники планировщика ПГ и назовут строку, в которой там происходит анализ LIMIT. Ибо что за магические 78/79 всё равно же не понятно.

Составной индекс по tab1(some_id, some_date).

Вот это - мне кажется наиболее правильным решением. Только мне такие вещи не дают делать самостоятельно. Завтра буду, видимо, такой вариант предлагать начальникам.

Какой страшненный костыль. Хорошо, что работает, но выглядит жутко. О_О

Это решение примерно на том же уровне, как то, которое я вам хотел, но постеснялся предложить: обернуть весь запрос в prepared statement, посмотреть, какой план он предложит при неизвестных переменных, и если план хороший — дальше этим запечёным prepared statementом и пользоваться. Судя по всему, результат получится такой же.

И ВСЁ ЖЕ, и ваш костыль, и мой костыль ощущаются жутко ненадёжно. Это он пока выбирает правильный индекс, а что, если потом изменится статистика и он по каким-то своим внутренним причинам передумает?

Хоть Postgres в любом случае оставляет за собой такое право. Тут уже как ни ворочайся, ему только потакать и надеяться на лучшее. :)

Думал тут местные крутые сишники быстренько посмотрят в исходники планировщика ПГ

Ну вы чего. Это нужны уж слишком крутые сишники, ещё и варящиеся во внутрянке конкретной СУБД.

Такие вопросы, ИМХО, лучше задавать на stackoverflow.com или вообще dba.stackexchange.com

P.s. пока искал, откуда может исходить подобное поведение, нашёл интересную статью как раз по теме и как раз с погружением в сишную внутрянку. Можете глянуть.

Звучит правда грамотно, но не удивлюсь, если вас всё же развернут. Индексы тоже чего-то стоят, а у вас БД, смотрю, очень большая.

Если у вас записей с одинаковым some_id всегда немного и у них не очень большой разброс по some_date, мне кажется, от составного индекса профита может много и не будет.

Ну выберете эти 500 в среднем записей, которые ещё и относительно рядом по временной шкале. А толку? Их и без индекса можно спокойно в памяти отсортировать.

А вот если таких записей может вдруг оказаться очень много или если корелляции по some_date никакой нет.. Тогда, может быть, idx_tab_some_id уже окажется не таким хорошим…

Тут уже к вам вопросы, только вы знаете всю специфику своей БД. Мы тут можем только гадать, теоретизировать и предлагать варианты. :)

Думал тут местные крутые сишники быстренько посмотрят в исходники планировщика ПГ и назовут строку, в которой там происходит анализ LIMIT. Ибо что за магические 78/79 всё равно же не понятно.

ну вот мне когда очень сильно приспичило разобраться в проблеме, пришлось-таки смотреть исходники сей СУБД, лучше бы я этого не делал… - там уровень алгоритмов застрял где-то в 80-х годах прошлого века.

Если по теме: вместо того, чтобы лепить LIMIT нужно закрывать курсор по достижению нужного количества записей.

вместо того, чтобы лепить LIMIT

в оригинале оно примерно такое:

INSERT INTO #t_temp () SELECT TOP 999 ()
SELECT @count1 = count(*) FROM #t_temp
IF @count1 < 999
SELECT @count2 = 999 - @count1
BEGIN
SET ROWCOUNT @count2
INSERT INTO #t_temp () SELECT ()
END
...
SET ROWCOUNT @count3
...
SET ROWCOUNT @count4

Статистика какая-то именно на этой таблице так ему подсказывает. Но какая?

Статистика частоты встречаемости значения tab1.some_id = 10

лучше бы я этого не делал…

А что там такого ужасного?

Кстати, бегло глянул репозиторий — ребята очень уважают документацию. Полно READMEшек размером с небольшую книжку, и каждый метод покрыт тонной комментариев. Приятно такое видеть.

закрывать курсор по достижению нужного количества записей

Собственно, а зачем так? Неужели просто чтобы скрыть от СУБД размер лимита? Вроде бы и курсоры так-то далеко не бесплатные…

Приятно такое видеть

Я не вижу никакой связи между наличием документации и качеством продукта (документация, к слову, довольно паршивая, просто потому что о подводных граблях в документации предпочитают умалчивать), ну к примеру: можно взять джуна и заставить его писать код и документацию, на выходе получится довольно посредственный продукт, зато с документацией.

Если ближе к теме; добавление в order by выражения nulls first полностью отучит query planner использовать индекс для сортировки, в документации такой трюк если и описан, то крайне витеевато: https://www.postgresql.org/docs/current/indexes-ordering.html, а на самом деле имеет место быть баг в query planner - он определяет применимость индекса лишь по формальному совпадению выражения, без учета других ограничений.

У order by 1 и order by 1 nulls first разные условия сортировки, выражение тут не при чём.

Ну конечно… что насчёт ограничения not null на колонке?

Выбрасывай SELECT count(*) cчитай на клиенте или на сервере. Ну или используй что-то вроде select reltuples AS count FROM pg_class where relname

Вообще по возможности выбрасывай любые COUNT, ORDER BY, GROUP BY, DISTINCT и это все делай на сервере или на клиенте. Запросы же упрощай максимально. Также стоит активно использовать разделение на PARTITIONы и делать запросы только по временным промежуткам.

добавление в order by выражения nulls first полностью отучит query planner использовать индекс для сортировки,

Вот как раз поскольку мы тут усиленно пытаемся имитировать T-SQL - вместе с его поведением NULLS FIRST|LAST, то у нас и индексы построены с указанием NULLS FISRT, и в ORDER BY вручную указываем.

И уже пару раз натыкался, что в коде эта фишка с DESC (для ПГ - с NULLS LAST) использовалась прошлыми разработчиками смыслово - для отбрасывания NULL из первых ста записей.

Т.е. получается с точностью до наоборот к вашему замечанию - НЕ добавление )

В MSSQL данные могут кластеризовываться в таблице согласно одному из индексов (обычно по ПК), поэтому в MSSQL финты с индексами ещё более-менее оправданы, в PostreSQL такого нет, и извращаться с индексами смысла нет никакого.

В MSSQL

Sybase на самом деле.

извращаться с индексами смысла нет никакого.

Если мы хотим, чтобы при ORDER BY DESC NULLS LAST использовались индексы в ПГ - нам нужно извращаться. Тут и выбора-то нет.

----------------------

А вот скажите другой вопрос.
Процедуры в стиле «CREATE TABLE #tab, INSERT, UPDATE, DELETE, UPDATE, DELETE (и т.д. 100500 раз) и SELECT из неё в конце» - в 9ти случаях из 10ти я могу переписать на один запрос в ПГ через CTE - без временных таблиц, их бесконечного обновления удаления, без курсоров.

Сомнения появились. Нужно ли к этому стремиться? Для ПГ что хуже - одна огромная портянка в CTE, или кучка мелких запросов INSERT/UPDATE/DELETE по временной таблице?