Индекс и сортировка по двум полям

Оберни пожалуйста портянку с кодом в

[code]
портянка тут
[/code]

Что-бы читабельно было

https://www.linux.org.ru/help/lorcode.md

https://www.linux.org.ru/help/markdown.md

Есть запрос, который выполняется очень долго.

И немудрено.

SELECT ID FROM smev_log ORDER BY LOGDATE DESC, ID ASC LIMIT 150 OFFSET 689113

Для того, чтобы это выполнить даже по индексу, любой нормальной СУБД придётся считать и выкинуть 689113 записей, а потом вернуть 150.

Он все равно смотрит всю таблицу. Там 20 млн. записей

И правильно делает – потому что это быстрее (либо Вы «соврали» PostgreSQL при tuning об относительной стоимости операций / степени кеширования – не стоило этого делать)!

Подскажите, пожалуйста, как заставить запрос работать по индексам!

Если хотите посмотреть, как это будет, то после добавления составных индексов (с правильной сортировкой, к примеру «LOGDATE DESC, ID ASC») попробуйте в сессии «set enable_seqscan = off;».

https://stackoverflow.com/questions/34110504/optimize-query-with-offset-on-large-table/34291099#34291099

любой нормальной СУБД придётся считать и выкинуть 689113 записей, а потом вернуть 150

Обкурился что ли? Индекс уже хранит сортированный порядок.

А поле LOGDATE какой тип имеет? Время+дата или просто дата? Полная отметка времени как бы для индекса не самый лучший вариант, тем более, что там повторений тогда практически не будет и индекс не имеет смысла.

LOGDATE DESC

Вангую что проблема в этом. Нужен индекс именно с таким порядком, а не просто logdate,id.

индекс не имеет смысла

Конечно индекс имеет смысл, т.к. индекс задаёт порядок, а значит не нужно читать блоки данных.

Обкурился что ли?

Нет, ты. Иди основы подучи, хамло.

Индекс уже хранит сортированный порядок.

Индекс – это не массив, а b-tree, быстро выбрать из которого N-ую по порядку запись невозможно.

Индекс – это не массив, а b-tree

Чёт ржу, а b-tree это что такое? Это упорядоченное дерево. Кроме того в некоторых БД помимо этого индекс хранит порядок и просто списком (каждый ключ имеет ссылку на следующий, как раз на случай offset/limit).

быстро выбрать из которого N-ую по порядку запись невозможно

Даже если индекс не имеет отдельного списка элементов по порядку, а является просто деревом, то всё равно пропустить N элементов и взять M будет гораздо быстрее чем сортировать всю таблицу целиком. Тем более, что индекс должен весь лежать в памяти, а таблица будет сортироваться на диске.

основы подучи

Ох ты ж лалка https://www.postgresql.org/docs/12/indexes-ordering.html

значит не нужно читать блоки данных.

index-only-scan в постгресе имеет ограничения, и в общем случае в блоки данных приходится таки лезть.

limit offset на больших таблицах - лютый антипаттерн.

тут ражзевано в картинках все: https://use-the-index-luke.com/blog/2013-07/pagination-done-the-postgresql-way

Чёт ржу, а b-tree это что такое?

А ты лучше думай, а не изображай из себя лошадь.

Это упорядоченное дерево.

Вот есть, допустим, упорядоченный (т.е. в нём просто хранимые значения упорядочены) односвязный список, из которого нужно получить по порядку 100500 значение. Покажи-ка метод, как это сделать, не пройдя по списку до этого элемента. Так вот у упорядоченного дерева – ровно та же проблема.

то всё равно пропустить N элементов и взять M будет гораздо быстрее чем сортировать всю таблицу целиком

Не угадал. Зависит от размера таблицы и N (и PostgreSQL, в отличие от тебя, это «знает» и использует).

Тем более, что индекс должен весь лежать в памяти, а таблица будет сортироваться на диске.

С чего это он должен лежать в памяти? И с чего это таблица должна сортироваться на диске?

Ох ты ж лалка

Подучи-подучи, не относящимися к делу ссылками кидаться не нужно. Кстати, даже в треде уже были ссылки на объяснения, почему индексы неэффективны при больших OFFSET, но ты, наверное, их не смотрел, я угадал?

Покажи-ка метод, как это сделать, не пройдя по списку до этого элемента

Покажи-ка, какой должен быть OFFSET, чтобы идти 20 секунд? Не бывает такого размера индексов (у адекватных людей).

Не угадал. Зависит от размера таблицы и N (и PostgreSQL, в отличие от тебя, это «знает» и использует).

Игрушечные таблицы в принципе не интересны, именно потому, что там без разницы что и как — всё равно (автоматически) быстро.

С чего это он должен лежать в памяти?

С того, что иначе он нафиг не нужен. Если индекс такой огромный, то данных просто охренеть сколько. Это уже совсем другая история.

И с чего это таблица должна сортироваться на диске

С того что часто столько памяти нет, чтобы всю таблицу всосать. Если таблица не игрушечная и есть сортировка, то следует изначально предполагать, что она будет на диске/c диска. Да даже если вся таблица в памяти, но большая, то всё равно пройти по индексу один раз будет быстрее, чем 100500 раз пройти по таблице (для сортировки).

даже в треде уже были ссылки на объяснения, почему индексы неэффективны при больших OFFSET

Индексы эффективны (по сравнению с сортировкой таблицы) при любых OFFSET. Использование больших OFFSET+ORDER проблема именно потому, что эффективности этого нужен индекс, а индекс который подойдёт не всегда возможно/желательно иметь. Конкретно в случае ТС никакой проблемы нет (кроме той, что индекс у него не подхватывается).

Покажи-ка, какой должен быть OFFSET, чтобы идти 20 секунд?

OFFSET = (кол-во записей в таблице - 1) при их достаточном количестве, например. Несложно же, нет?

Не бывает такого размера индексов (у адекватных людей).

Ах да, неудобных для тебя примеров в природе не существует. Как размер индекса связан с адекватностью?!

Игрушечные таблицы в принципе не интересны, именно потому, что там без разницы что и как — всё равно (автоматически) быстро.

Это просто неправда. И на «игрушечных» таблицах можно заклинить всё очень надолго, если выполняемая операция имеет квадратичную сложность.

С того, что иначе он нафиг не нужен.

Это бред, извини. Т.е. ты даже не знаешь, для чего изначально предназначены b-деревья и почему они могут приемлемо работать, вовсе не помещаясь в память.

Если индекс такой огромный, то данных просто охренеть сколько.

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

С того что часто столько памяти нет, чтобы всю таблицу всосать.

У тебя нет, может быть? Советовал бы тебе прекратить использовать найденное на помойке «железо» для современных баз данных.

Если таблица не игрушечная и есть сортировка, то следует изначально предполагать, что она будет на диске/c диска.

Или не следует, см. выше.

Да даже если вся таблица в памяти, но большая, то всё равно пройти по индексу один раз будет быстрее,

Или не будет. В PostgreSQL index-only scans, эээ… не совсем, а иногда совсем не index-only.

чем 100500 раз пройти по таблице (для сортировки).

Что-что?! Ты знаешь, как external merge sort работает? Или твои познания в этом такие же «прекрасные», как и в индексах?

Увы, но postgres так не работает. Точнее, индекс то так работает, но конкретно postgresql, хранит в индексе не совсем то. В постгресе индекс хранит список-всех-существовавших-версий-с-последнего-вакуума( delete никогда не затрагивает индекс). И из этого следуют некоторые фокусы

В итоге, пока у нас «positioning-by-key», то всё классно: index scan для поиска, и сравнительно-быстрый скан страниц для получения содержимого, с проверкой «видимости».

Но когда у нас order-by-offset, то получается что для пропуска первых 100500 записей, сервер должен сходить в страницы на диске, и посмотреть «а жива ли эта запись для этой транзакции».

Прикинув такой план дела, постгрес тупо сразу берет хипскан, т.к. «все равно вероятно придется обойти пол хипа, в непонятном порядке».

плата за mvcc

Подробнее, можно почитать тут: https://www.postgresql.org/docs/10/indexes-index-only-scans.html

Для затравки

But there is an additional requirement for any table scan in PostgreSQL: it must verify that each retrieved row be “visible” to the query’s MVCC snapshot…However, for seldom-changing data there is a way around this problem…visibility map…и.т.д.

В итоге, нельзя рассчитывать что когда постгресу говоришь «дай мне первые 100 строк в порядке возрастания id» он тебе одним индекс-сканом это найдет. Только если ты пальцем не тронешь данные которые

postgres так не работает

Дело не в том как оно там шуршит данными, а в том что

In addition to simply finding the rows to be returned by a query, an index may be able to deliver them in a specific sorted order. This allows a query’s ORDER BY specification to be honored without a separate sorting step

сервер должен сходить в страницы на диске, и посмотреть «а жива ли эта запись для этой транзакции»

Печально конечно, но всё равно лучше «сходить и отсортировать все записи».

неудобных для тебя примеров в природе не существует

Ну ты же пока не привёл. Я тебя спросил, сколько должно быть записей, чтобы индекс проверяло 20 секунд. Ты что-то там булькаешь в лужу. Ты проведи эталонное тестирование-то, проверь. Потом расскажешь.

Всем спасибо. Решение: Сделали индексы LOGDATE DESC, ID ASC. Запрос выполняется почти моментом.

Сделали индексы LOGDATE DESC, ID ASC. Запрос выполняется почти моментом

ЧТД.