Запрос не подхватывает индекс, который идеально подходит

Привет.

Если очень кратко - есть простой запрос по схеме FROM Таблица1 + JOIN Таблица2
Для Таблицы1 применяется индекс.
Для Таблицы2 - нет, хотя есть прям идеально для него подходящий, хотя бы потому, что он полностью покрывает все столбцы.
Из Таблицы2 отбирается примерно 1/6 всех данных, из плана

СУБД берет и просто сканирует:
Время выполнения около 7 сек.

ПРИ ЭТОМ: параллельное выполнения я отключил (из диагностических соображений), ресурсов у сервера много.

И самое интересное вот в чем.
Если запретить seqscan и удалить все индексы, кроме готового - время становится около 5 сек.
Если еще и запретить nestedloops - юзается hash выигрывается еще секунда... И вот хоть ты тресни.

Вопрос банален: почему так происходит?
Может можно какую-то настройку подкрутить?
Какой-то cost где-то подправить?
По администрированию слабо шарю, честно.

Никакого объяснения, кроме как то, что легче просканить значительную часть уже загруженной в память БД, чем грузить индекс у меня нет, хотя время выполнения показывает, что это все не проблема...

Пробовал на PG 15 и 16.

0

а сколько строк в выборке по первой таблице?

0

Swa111, примерно так:

-> Index Only Scan using ... on "Таблица1" (cost=0.43..0.87 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=393476)

Но прикол также и в том, что даже если просто отбирать из Таблицы2 - он все равно не подхватывает индекс, если все также поотменять, хотя это более логично, чем в случае с JOIN

0

Сообщение от Vasiliusis Но прикол также и в том, что даже если просто отбирать из Таблицы2 - он все равно не подхватывает индекс,

Ну смотрите. (пальцем в небо, общие принципы, не видя всех данных, плана)

В Таблица2 1.327.365 записей, каждая запись по 12 байт, тогда имеем размер данных 16Мб, и если данные лежат на страницах по 8К, то получается, что таблица занимает 2000 страниц на диске.

Построение индекса по всем полям Таблица2 создаст B-дерево, у которого будет 2000 листьев + 2 страницы промежуточного уровня + 1 страница корневая, итого 2003 страницы индекса.

Если предположить, что данные распределены равномерно, то, что бы извлечь 30% данных, то надо пройти по всему дереву, те просканировать 2003 страниц.

Поэтому оптимизатор выбирает более дешевое сканирование всей таблицы 2000 страниц, чем сканирование индекса 2003 страницы.

PS индекс работает обычно, когда в условие отбора попадает 1% от силы 2% данных.

0

PaulWist, ну в случае запроса без джоина я понимаю, тут логика прослеживается.

Но думаю, при обычном сканировании перебирается больше, чем 2000 страниц, т.к. индекс, грубо говоря, покрывает меньше половины данных в строке. Причем (увы, так повелось еще до меня) в таблице есть и длинные строки и TEXT. то есть не факт, что в 8кб уместится весь объем всех страниц, как индекса так и таблицы. Но видимо, у меня так совпало, спасибо за пояснение.

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

0

В постгресе, емнип, в индексах версии строк не хранятся и зачастую всё равно в таблицу идти нужно, проверять? Так что вполне может быть, что индексом дороже

0

katamoto, может объяснить, что значит термин "версии строк"? Это строки? А почему - версии?
Слово "версии" наводит меня упорно на мысли об историчности, типа "можно вернуться к старой - какой-то - версии строки".

Добавлено через 2 часа 39 минут
Решение нашел.
Если у вас SSD (а у меня он) - можно сравнять настройки

seq_page_cost = 1.0
random_page_cost = 1.0

и все будет прям хорошо.

источник тут https://habr.com/ru/companies/... es/576980/
либо в следующей статье (там на странице ищите ссылку)

0

Сообщение от Vasiliusis katamoto, может объяснить, что значит термин "версии строк"? Это строки? А почему - версии? Слово "версии" наводит меня упорно на мысли об историчности, типа "можно вернуться к старой - какой-то - версии строки".

Если в кратце то при редактировании строк, на самом деле создается ее копия. Эту копию видит только создавший, до тех пор пока не сделает commit. После Commit новую версию строки будут видеть все, а старая перейдет в разряд мертвых и будет занимать место пока за ней не придет vacuum.
Вернуться к старой версии после commit нельзя.
Но в рамках транзакции можно вернутся к старой версии (на начало транзакции) если будет rollback (или ошибка в частности). В этом случае несостоявшиеся новые строки передут в разряд мертвых.

Про оценку стоимости выполнения плана можно почитать в этом
курсе

0

Swa111, а понял, спасибо, копия строки в транзакции

0