А почему вы сразу пытаетесь начать с обмана планировщика, вместо того, чтобы сделать ANALYZE проблемной таблице и убедиться, что по ней вовремя ходит autovacuum?
я посчитал ненужным об этом писать в статье. Конечно по таблицам ходит автовакуум. пробовали даже в моменты факапов сделать VACUUM FULL, ANALYZE. Но если постгрис выбрал стратегию которая предполагает другие индексы, то он выбрал.

там есть ручки влияющие на стратегию, но увы они глобальные.
1. У вас здесь налицо искажение cardinality для index scan'ов по (с) и (a, b) на целый порядок — 150331 против 3982643! и 1258378 против 377222. Поэтому имхо лучше начать атаковать эту проблему. Например, если вы делали vacuum full — analyze, увеличитьте выборку по a,b,c при сборе статистики ALTER TABLE SET STATISTICS. Более подробно про оценку кардинальности можно почитать в доке www.postgresql.org/docs/9.5/static/planner-stats-details.html.

2. При построении составного индекса первый параметр должен быть наиболее селективным в запросах среди остальных. У вас планер таковым считает c (150331 строк против 1258378 для (a, b) от общего числа в table1), поэтому и отказывается использовать индекс по (a,b,c). Это необходимо править через более тщательный сбор статистики.
Например, если вы делали vacuum full — analyze, увеличитьте выборку по a,b,c

Близкий к реальному у меня пример такой:


CREATE INDEX ... ON table ("a", "b") WHERE "c" IN (1,2,3);

SELECT
  *
FROM
   "table"
WHERE
       "a" = 1
   AND "b" = 2
   AND "c" IN (1,2,3)

по c имеется свой индекс (полный), по a,b — свой (полный).


я сейчас перевел одну реплику в мастер и поэкспериментировал с полем c: SET STATISTICS 1000; и SET STATISTICS 100; и SET STATISTICS 10000; (больше последнего варианта нельзя делать) — нет никакой разницы — запрос строится неверно.


с полями a,b ща еще эксперименты в процессе — закончу напишу. Но расширить статистику до 10000 — ИМХО мало что даст: в таблице 82 млн записей.

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

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

И вот тут пожалуйста) И желательно с примером.
Поясните пожалуйста.

RETURNING, WITH, JSON-операций и индексирования по ним


И вот тут пожалуйста) И желательно с примером.

нет GIST, GIN, на которых решаются такие задачи как реализация всяких автокомплитеров и геопоисков. Даже индексированный поиск по регулярным выражениям возможен.


пример простой: вот скажем пишем автокомплитер-подсказку фраз. причем начинать фразу пользователь может с любого места (классический вариант такого подсказчика — подсказка адресов улиц: пользователь может начинать писать: маршала жукова или просто жукова, ему надо подсказывать варианты содержащие от начала слова до конца фразы — то что совпадает)
на GIN-индексе такая задача решается написанием одной функции SQL, сплитящей фразы на парты и простановкой этой функции в запрос и в индекс.
на MySQL без внешних приблуд эта задача не решается от слова вообще (пять-десять BTREE индексов и ручное пересечение по ним мы не рассматриваем)

О, да! Там вас ждут совсем другие баги.
Например везде надо эскейпить underscore (зачем, Карл?), кроме запроса GRANT. Там не надо. Очень удобно. Сразу начинаешь жить полноценной жизнью, не то что в постгресе, где какой то сраный планировщик умнее тебя.
НЛО прилетело и оставило эту надпись здесь.
InooDB, не, не слышал

DDL все-равно нетранзакционный.

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

НЛО прилетело и оставило эту надпись здесь.
Если бы мы были в 2008 году скажем, тогда может и не многие, но сейчас я в этом сильно сомневаюсь. Тем более какая разница, сколько человек и чем пользуются, если это работает быстро и надежно, то не вижу в этом причины говорить о том, что MySQL говно, без транзакций и тп.
НЛО прилетело и оставило эту надпись здесь.
Так это проблемы в этих людях, а не в дельфине.
готов поспорить что просто кто то не сделал VACUUM ANALYZE или неправильно настроил стоимости операций
Вакуум делается, а стоимости операций — ручки глобальные.

вот у Вас 50 таблиц размерами от 1Мб до 200Гб.
вопрос: как правильно настроить стоимости операций чтобы всем таблицам было хорошо?

Если дадите ссылку на документ, описывающий правильную стратегию — буду благодарен :)
как я и думал, 2 вариант

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

>вопрос: как правильно настроить стоимости операций чтобы всем таблицам было хорошо?
взять и измерить, записать в конфиг

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

я только не понимаю как хинт поможет постгрису по другому выбирать индексы.
вот он посмотрел на статистику индекса 1 и индексов 2 и 3 и выбрал выбирать из индексов 2 и 3, что является ошибкой в 100% случаев. Как помогут ручки про страницы в данном случае?

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

я это знаю. Я говорю, что выбор плана, предполагающего мерж двух выборок — при наличии точного индекса — является 100%-й ошибкой планирования.
точный может инклюдать еще 100500 ненужных столбцов в себя и иметь размер огромный.

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


еще раз: есть индекс по a,b,c, требуется выборка по a,b,c.
выборка по индексам a,b,e и c с мержем по ним ВСЕГДА будет стоить дороже выборки по a,b,c, поскольку:


  1. предполагает стадию фильтрации
  2. предполагает математическую операцию мержа
  3. столбик e в индексе a,b,e как раз является плюс первым к тем 100500 о которых Вы говорите.

итого: выборка по двум индексам при наличии точного подходящего — 100%-я ошибка планирования.

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

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

первый раз, столкнувшись с такой проблемой я попробовал VACUUM FULL + REINDEX всех индексов и ANALYZE.
проблеме это не помогло.


если у вас есть индексы a,b,c и a,b,e + e, то существует ненулевая вероятность что выборке из a,b,c планировщик предпочтёт перемножение выборок a,b,e, и e.
делает он это на основании статистики с индексов. Причем эта статистика базируется на коэффициентах и (по видимому) не учитывает абсолютные размеры таблиц.


на первых порах мы это "лечили" путем отказа от одного из индексов: a,b,c или a,b,e. То есть чтобы у планировщика не было из чего выбирать.
но поскольку проект большой, варианты у него рано или поздно появляются… увы.

Причем эта статистика базируется на коэффициентах и (по видимому) не учитывает абсолютные размеры таблиц.

если смотреть на данный EXPLAIN то выборка из одного индекса дает коэффициент 4млн/80 млн == 0.05, а вторая выборка дает коэффициент 0.3 / 80 = 0.004. Возможно Pg видя что коэффициенты маленькие (это моё предположение, а не знание) предпочел выборку из этих двух индексов, по аналогии с тем, как он на основании статистики предпочитает иногда сделать seq-scan вместо выборки из индекса

По умолчанию Postgres считает, что в данных нет внутренних корреляций и селективность одной колонки можно просто перемножить на другую, чтобы получить общую селективность. Нативный путь чинить это — создать статистику на пару колонок, www.postgresql.org/docs/10/static/sql-createstatistics.html
CREATE STATISTICS появилось только в Pg10, мы пока еще огребаем от нестабильности 9.5, ставить 10 в прод, я считаю, еще где-то год нельзя будет.

PS: из последних проблем 9.5 — коррапт индексов: потребовалось обновить 9.5.5 на 9.5.7 и перестроить индексы. На инстансах 9.3 таких проблем не встречается.
ну а на 10 — пока разбивается нос о проблемы 9.5 ехать продом совсем-совсем уж рано.

Не знаю что минусовать. Я ж не выдумываю:


С индексами разбивали нос об эту проблему:



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

НЛО прилетело и оставило эту надпись здесь.

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

НЛО прилетело и оставило эту надпись здесь.
НЛО прилетело и оставило эту надпись здесь.
НЛО прилетело и оставило эту надпись здесь.
много индексов это совсем не хорошо, а полное сканирование далеко не всегда плохо

начиная с какого-то размера таблиц полное сканирование становится всегда плохо


ну а много индексов плохо только при update’ах


а вот запрос по индексу + filtered секция в explain в моём опыте всегда приводит в итоге к факапу

НЛО прилетело и оставило эту надпись здесь.

ну много это понятие относительное.


мы практикуем частичные индексы: вот страница, вот выборка для нее. если есть константные данные — они уезжают из индекса в его условие. в итоге получается скажем в индексе на таблице о 80 млн записей всего 1-2 тыс записей.
и таких индексов примерно 10-15. Много это?

если есть константные данные- партицируйте.
p.s. ну это конечно совет для нормальных субд, в PG с этим ньансы…

где возможно — партицируем. Но партицирование возможно далеко не везде.
большие сложности при партицировании и перекрестных (FOREIGN) ссылках.

НЛО прилетело и оставило эту надпись здесь.
НЛО прилетело и оставило эту надпись здесь.

у Оракла есть вот и вот, может и постгрису стоит подумать в этом ключе?


для постгриса увы подобное решение — "внешнее". Закладывать на него прод == брать на себя его поддержку и [или] надеяться

НЛО прилетело и оставило эту надпись здесь.
Не всегда.
Пример: есть пустая таблица с индексом, которая заполняется запросом с подзапросом, ссылающимся (по индексу) на эту же таблицу.
Статистику можно обновить только между запросами, а вот задать хинтом nested loop по индексу помогает. Точнее, помогало бы, если бы Oracle аж с версии 10 не начал эти хинты посылать лесом.
В итоге рабочее решение — заполнить таблицу данными, собрать статистику, выбрать нужный план из нескольких и прибить его. После этого можно уже очищать и работать по обычной схеме. Так вот, это куда менее удобно, чем просто хинты.
Ну и опять же отсутствие хинтов приводит к тому, что необходимость прибивать планы возникает в неожиданных местах в неожиданное время.
Рабочие хинты да, как правило уменьшили бы скорость выполнения, но при правильном использовании гарантировали бы отсутствие нежданчиков.
> Рабочие хинты да, как правило уменьшили бы скорость выполнения

увеличили, как правило.

человек, пишущий хинт, как правило понимает что делает.
человек, который не понимает, не пишет хинт.

вот в примере что в статье: выборка из точного индекса ВСЕГДА будет быстрее мержа двух-трех выборок. И то что Pg предпочитает иногда две выборки — одной — стопроцентная бага планировки.
Если б можно было написать хинт, то написали и поехали дальше.
а тут живешь как на пороховой бочке: построил индекс для X, а Y из за этого стало работать медленнее или вообще факапить. Почему? потому что индекс для Y, который несколько лет успешно пользовался, оно взяло и заменило на мерж выборок из индексов X (появился) и Z (раньше был)
выборка из точного индекса ВСЕГДА будет быстрее мержа двух-трех выборок. И то что Pg предпочитает иногда две выборки — одной — стопроцентная бага планировки
Такая же беда у firebird. Наверное, разработчики тестируют на случаях, когда по группе индексов нужно выбрать 20% данных, а не сотню-другую записей из 10 млн.

Если в вашем случае действительно ВСЕГДА, то, может, выставить enable_mergejoin = off?

Вам не понравилось "всегда", можете привести пример когда join быстрее безфильтровой выборки из одного индекса?


  1. join всегда с фильтром
  2. да существует теоретическая возможность что фильтр ничего не отбросит, но именно в случае join она очень низка
  3. join — это математическая операция, которой в прямой выборке из индекса нет

Вопрос не в том, понравилось ли мне, а в том, не поможет ли отключение enable_mergejoin :-)

в документации пишут что там где Pg использует mergejoin он начнет предпочитать nested loop и hashjoin. попробовать можно конечно (и я попробую как с текучкой разберусь), но поскольку проект большой как бы не акунулось это в каком другом месте.


вообще ручки настройки планировщика — глобальные и когда в приложении много разных запросов, то движение ручки в ту или иную сторону будет помогать одним запросам и вредить другим. Поэтому hint'ы были бы идеальным инструментом для проектов сложнее hello world

В принципе, есть SET LOCAL, действующий в рамках текущей транзакции.


Позиция разработчиков pg по поводу хинтов общеизвестна и не менялась лет уже как 20, не думаю, что они ее вдруг пересмотрят.

НЛО прилетело и оставило эту надпись здесь.
что такое база данных? — просто массив строк. ну или несколько массивов в общем случае.

что такое индекс? — средство ускорения поиска по массиву.

если индекс — обуза, то руки оторвать разработчику такому.
НЛО прилетело и оставило эту надпись здесь.
Ваше определение?
НЛО прилетело и оставило эту надпись здесь.
С чего вы взяли что вот прям всегда?

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


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

НЛО прилетело и оставило эту надпись здесь.

Тут пользователя забанили, но на его комментарий я все-таки отвечу.


Опять же, спорное утверждение. Я не всё из вашей терминалогии понял, но вы, по-моему, не верно ставите знак равенства, как и автор статьи, между «быстро» и «доступ по индексу». Если обслуживаемое индексом отношение не кластеризовано по индексу, то далеко не обязательно произвольный доступ к страницам с данными (а именно они являются минимальными единицами администрирования, а не некие «записи») через индекс окажется быстрее с точки зрения совокупной стоимости, чем циклы со слиянием. К тому же, вполне возможно, что стоимость процессора в конкретной конфигурации очень низкая, а стоимость произвольного чтения — высока. В общем, это задачка со слишком многими неизвестными, чтобы что-то категорически заявлять.

Такое может произойти только если число записей в выборке сравнимо с общим числом записей в таблице. Но если число записей в таблице достаточно велико, то само существование подобного запроса к оперативной БД — уже ошибка.


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

Которые оптимизатор оракла весело игнорит

Боюсь, быть заминусованным, но возможно, у сервера памяти не хватает на работу с индексами и postresql считает, что проще пройтись по таблице с диска, чем грузить индекс с диска в файл подкачки или что-то подобное.

Можете настройки сервера выложить?
Какой объём ОЗУ, какое кол-во конектов максимальное, что стоит в настройках следующих:
  • max_connections
  • shared_buffers
  • effective_cache_size
  • work_mem
  • maintenance_work_mem
  • min_wal_size
  • max_wal_size
  • checkpoint_completion_target
  • wal_buffers
  • default_statistics_target
В ветке выше я уже тонко намекал автору об неправильной настройке, здесь вы меня немного опередили, я хотел поговорит об общих настройках чуть попозже. Думаю что большинство настроек будут более чем приемлимы, иначе у автора постгрес всегда бы секвенскан делал бы.
Ваша настройка не поможет проблеме, ибо речь о seqscan не идет.
> проще пройтись по таблице с диска

блин уже который пост читаю это. Проблема не в том что делается секскан. Наоборот seqscan не делается.

проблема в том что делая выборку по a,b,c есть несколько индексов, содержащих a,b,c:

1. a,b,c — точно подходящий для выборки
2. a,b,e — дающий срез по ab
3. c,d,e — дающий срез по c
4. итп

и вот Pg вместо того чтоб взять и выбрать из abc запрос по abc (индекс специально для запроса и строился, в нем даже фильтровать ничего не надо) берет и мержит две выборки по a,b,e и c,d,e
Планировщик ошибся.
Что у вас стоит в default_statistics_target?
50, но простановка в 10000 — ничего не меняет в ошибке. я отвечал выше по этой настройке.

PS: кстати поле e у меня имеет всего 10 разных вариантов, соответственно от этой «ручки» вообще не зависит.
Не заметил сразу.
А память свободная для постгреса есть? Он не свопится?

когда факап происходит (причина его установлена — неверный выбор индексов) — тогда тоже не свопит: свопа в системе нет :)

НЛО прилетело и оставило эту надпись здесь.
Можете настройки сервера выложить?

$ grep -E 'max_connections|shared_buffers|effective_cache_size|work_mem|maintenance_work_mem|min_wal_size|max_wal_size|checkpoint_completion_target|wal_buffers|default_statistics_target' /etc/postgresql/9.5/main/postgresql.conf|grep -v '^#'|sort 

checkpoint_completion_target = 0.9
default_statistics_target = 50
effective_cache_size = 80GB
maintenance_work_mem = 1GB
max_connections = 200
shared_buffers = 27GB
wal_buffers = 8MB
work_mem = 768MB
В других СУБД всё куда проще
SELECT * FROM table1 WITH(INDEX(table_index1)) WHERE c='3'

SELECT * FROM orders o
JOIN clients c WITH(INDEX(PK_Client_Id)) ON c.ClientId=o.ClientId
WHERE o.OrderDate='2017-12-02'

Неужели в PostgreSQL нет явных хинтов?
НЛО прилетело и оставило эту надпись здесь.
если Pg сделал неверный план запроса, то нет способа заставить его сделать верный план (не меняя запрос, не меняя список индексов). Стоимости страниц в данном случае не влияют: я провел эксперимент и поднял стоимость с 4-х до 400 (то есть увеличил стоимость страницы на два порядка) как в плане запроса фигурировал неправильный набор индексов, при имеющемся **точно подходящем, ЧАСТИЧНОМ** индексе, так это и осталось.

seq_page_cost = 1
random_page_cost = 400
НЛО прилетело и оставило эту надпись здесь.
в постгресе нет их т.к. они не нужны, при правильной настройке и обслуживании 99.99% запросов будут выполнятся самым оптимальным образом, в случае сверх редкого кейса не оптимального использования индексов, можно воспользоваться расширением, при помощи которого можно явно задать план запроса
Теоретически, да. А практически часто встречается, что СУБД не может подобрать оптимальный порядок JOIN-ов при объединении нескольких таблиц, если также сильные ограничения есть в WHERE (знать надо предметную область и типичные ситуации с данными).

И получается разница 200ms против 50ms, оптимизированных вручную. Или считаете, что этим можно пренебречь: не fullscan, и ладно?
НЛО прилетело и оставило эту надпись здесь.
select * from Orders where OrderKind=?k and OrderDate between ?s and ?e
при наличии индексов и по OrderKind, и по OrderDate, при выполнении prepare какой должен быть план?
Или надо делать новый индекс по двум столбцам (и так каждый раз при появлении нового запроса?).
Мне как разработчику очень не хотелось бы заниматься расстановкой хинтов каждый раз при появлении нового запроса.
Мне бы тоже не хотелось. Однако, реальность бывает такая, что некоторые популярные запросы, поступающие параллельно с 500 рабочих мест, приходится оптимизировать вручную, и от ORM отказываться, лишь бы быстрее работало.
а зачем тут препэйр?
НЛО прилетело и оставило эту надпись здесь.

после появления WITH можно контроллировать порядок реальных объединений прописывая в WITH выборки по порядку.


у Pg кстати болезнь — делать JOIN не в том порядке, особенно это касается LEFT JOIN


SELECT
  *
FROM
  "t1"
LEFT JOIN "t2" ON "t1"."t2_id" = "t2"."id"

WHERE
  "t1"."some" = 'bla' -- обратите внимание, фильтруется только t1

Глядя на подобный запрос, очевидно, что разворот JOIN'ов по отношению к написанному — будет ошибкой в 100% случаев (был бы простой, не LEFT JOIN — разговор был бы другой), однако очень часто натыкались на подобный разворот и пару лет назад взяли за практику писать WITH в подобных случаях.

Что-то я не могу представить как вообще можно выполнить LEFT JOIN не в том порядке… Можете привести такой план запроса?
НЛО прилетело и оставило эту надпись здесь.
По моему глубокому убеждению разработчик не должен всем этим заморачиваться — настройкой СУБД — он должен просто брать и реализовывать свою модель

в этом случае у разработчика никогда не получится сколь-либо полезного проекта, увы

НЛО прилетело и оставило эту надпись здесь.
Вообще, дорога вам лежит в postgresql-hackers, в тред www.postgresql-archive.org/Bitmap-scan-is-undercosted-td5995072.html — там как раз начали обсуждать, почему bitmap and считается более скоростным планом, чем скан по одному индексу.

Не совсем в тему, но.
Мы в проекте переносим всякие выборки из слона в эластику. То есть, слон используется как хранилище, которое много что умеет. Типа работы с json и т.д. И в нем выборка, в основном, идёт по ключам. Все остальное — только в эластике.
Конечно, благо, пока никаких агрегаций нет. Но, думаю, эластика справится.)

НЛО прилетело и оставило эту надпись здесь.
В нелюбимом вами MySQL зато есть принудительное использование индекса/индексов
USE INDEX (col1_index,col2_index)

и принудительное НЕ использование индексов
IGNORE INDEX (col3_index)

dev.mysql.com/doc/refman/5.7/en/index-hints.html
Хинты планера в PostgreSQL
Японские разработчики из NTT реализовали хинты планера PostgreSQL. Причем, им удалось это сделать, не меняя ядро, в виде отдельного модуля pg_hint_plan
linuxover В новой версии PostgreSQL 10 ошибка планера, которая была у вас, ушла? Проверяели? (Из статьи не понял)
десятку в продакшене еще не пробовали. я где-то выше писал :)
Только полноправные пользователи могут оставлять комментарии.
Войдите, пожалуйста.