PostgreSQL — отличнейшая БД, планировщик которой достаточно интеллектуален.
Однако в ряде случаев мощь интеллекта планировщика вырастает настолько, что он превращается в сверх-интеллект, ну и как всякий сверх-интеллект — объявляет войну своему создателю, а прежде всего начинает с войны с проектом в котором живет.
Образумливать взбунтовавшийся интеллект иногда очень сложно. Поделюсь недавней "находкой" в этой области.
Предположим что у Вас есть вебсайт, на котором есть несколько страничек, которые выводят выборки из БД (в общем-то типовой случай).
Рассмотрим простейший пример. Допустим Вы выбираете список из одной таблицы по пересечению условий AND:
/* Запрос № 1*/
SELECT
*
FROM
"table1"
WHERE
"a" = 1
AND "b" = 2
AND "c" = 3При разработке приложения, разработчику очевидно что для данного запроса
неплохо иметь индекс:
CREATE INDEX CONCURRENTLY "table_index1" ON "table1"("a","b","c");
Или даже частичный, если какое-то из полей — константа:
CREATE INDEX CONCURRENTLY "table_index1" ON "table1"("a","b")
WHERE "c" = 3;
На другой странице у Вас может быть выборка по "c" и "e":
/* Запрос №2 */
SELECT
*
FROM
"table1"
WHERE
"c" = 456
ORDER BY
"e"
LIMIT
10соответственно для другой страницы у Вас будет индекс:
CREATE INDEX CONCURRENTLY "table_index2" ON "table1"("c","e");
Ну и если проект у Вас большой, то вполне вероятно наличие третьего индекса:
CREATE INDEX CONCURRENTLY "table_index3" ON "table1"("a","b","e");
Теперь Ваш сайт работает нормально, таблицы (в данном примере — одна штука) понемногу наполняются.
Сайт развивается. Разработчики иногда добавляют индексы для важных выборок.
В какой-то момент случается факап. Вы заходите pg_dump и видите сотни запросов №1. Вы начинаете исследовать EXPLAIN этого запроса и обнаруживаете нечто вроде следующего:
Bitmap Heap Scan on table1 (cost=43482.22..54652.36 rows=2806 width=2494) (actual time=2544.520..2602.755 rows=337 loops=1)
Recheck Cond: ((a = 1) AND (b = 2) AND (c = 3))
Heap Blocks: exact=86917
-> BitmapAnd (cost=43482.22..43482.22 rows=2806 width=0) (actual time=2516.333..2516.333 rows=0 loops=1)
-> Bitmap Index Scan on table_index2 (cost=0.00..7643.88 rows=150331 width=0) (actual time=1791.934..1791.934 rows=3982643 loops=1)
Index Cond: (c = 3)
-> Bitmap Index Scan on table_index3 (cost=0.00..35836.69 rows=1258378 width=0) (actual time=91.829..91.829 rows=377222 loops=1)
Index Cond: ((a = 1) AND (b = 2))
Planning time: 2.706 ms
Execution time: 2612.418 ms
(10 строк)
То есть начиная с какого-то размера таблицы постгрис решил что вместо того чтобы выбрать 337 записей напрямую из предназначенного для этого запроса (возможно частичного!) индекса, он лучше сделает выборку на 4+ млн записей, потом перемножит ее с выборкой на 0.3+ млн записей и вернет Вам результат из 337 записей.
Как бороться с этим?
Гугл выдает много ссылок с аналогичными вопросами, но мало ответов.
Есть ссылки на разные решения, в том числе и тут на хабре. Однако большинство решений требуют при апгрейдах Pg заниматься вопросом и их апгрейда, требуют нестандартного запуска Pg итп.
В общем нам, смигрировавшим за последние пару лет между 9.0 -> 9.1 -> 9.3 -> 9.5, планирующим апгрейд на 10.x, такой способ не очень подходит.
Заставить PostgreSQL использовать Ваш индекс можно и штатным способом. Для этого нам потребуется фейковая функция:
CREATE FUNCTION "selindex" ("name" TEXT)
RETURNS BOOLEAN
AS $$
BEGIN
RETURN TRUE;
END;
$$
LANGUAGE plpgsql
IMMUTABLE
;
Функция ничего не делает, но будет использоваться для того чтобы сбить сверхинтеллект PostgreSQL с толку в нужное нам русло.
Далее перестраиваем индексы примерно таким образом:
CREATE INDEX CONCURRENTLY "table_index1" ON "table1"("a","b","c");
WHERE "selindex"('table_index1')
Если секция WHERE уже имеется — добавляем в нее соответствующее условие AND:
CREATE INDEX CONCURRENTLY "table_index1" ON "table1"("a","b")
WHERE "c" = 3 AND "selindex"('table_index1');Ну а в запросы дописываем AND "selindex"('table_index1'):
SELECT
*
FROM
"table1"
WHERE
"a" = 1
AND "b" = 2
AND "c" = 3
AND "selindex"('table_index1')PS: Честно говоря я подустал разгребать факапы связанные со "сверхинтеллектом". Отладка, интеграционные тесты, нагрузочные тесты Вам не гарантируют что в перспективе PostgreSQL не откажется от использования того или иного индекса в пользу двух-пяти других. Но пока изящного решения как бороться с этим я не нашел.
Комментарии (69)
MaZaAa
01.12.2017 20:15-1Переходите на MySQL или MariaDB
linuxover Автор
02.12.2017 10:47+1проблема MySQL в том что в ней нет нормального SQL. Нет нормальных индексов, соответственно простейшие задачи решаются при помощи плясок с бубном.
MaZaAa
02.12.2017 12:28проблема MySQL в том что в ней нет нормального SQL.
Поясните пожалуйста.
Нет нормальных индексов, соответственно простейшие задачи решаются при помощи плясок с бубном.
И вот тут пожалуйста) И желательно с примером.linuxover Автор
02.12.2017 12:49Поясните пожалуйста.
RETURNING, WITH, JSON-операций и индексирования по ним
И вот тут пожалуйста) И желательно с примером.
нет GIST, GIN, на которых решаются такие задачи как реализация всяких автокомплитеров и геопоисков. Даже индексированный поиск по регулярным выражениям возможен.
пример простой: вот скажем пишем автокомплитер-подсказку фраз. причем начинать фразу пользователь может с любого места (классический вариант такого подсказчика — подсказка адресов улиц: пользователь может начинать писать: маршала жукова или просто жукова, ему надо подсказывать варианты содержащие от начала слова до конца фразы — то что совпадает)
на GIN-индексе такая задача решается написанием одной функции SQL, сплитящей фразы на парты и простановкой этой функции в запрос и в индекс.
на MySQL без внешних приблуд эта задача не решается от слова вообще (пять-десять BTREE индексов и ручное пересечение по ним мы не рассматриваем)
tgz
02.12.2017 15:03О, да! Там вас ждут совсем другие баги.
Например везде надо эскейпить underscore (зачем, Карл?), кроме запроса GRANT. Там не надо. Очень удобно. Сразу начинаешь жить полноценной жизнью, не то что в постгресе, где какой то сраный планировщик умнее тебя.
Kirill80
02.12.2017 17:43+1В большинстве установок, подавляющем, MySQL это не СУБД вообще. Нет транзакций, нет MVCC, не соблюдается ACID.
MaZaAa
02.12.2017 17:49InooDB, не, не слышал
slonopotamus
02.12.2017 18:27DDL все-равно нетранзакционный.
symbix
02.12.2017 18:33В 8.0 уже таки да. И даже настройки по умолчанию вменяемые. В общем, уже стало похоже на настоящее.
Kirill80
02.12.2017 18:32Inno пользуются немногие. Среди этих немногих найдутся единицы, которые делают это осмысленно.
MaZaAa
02.12.2017 23:44Если бы мы были в 2008 году скажем, тогда может и не многие, но сейчас я в этом сильно сомневаюсь. Тем более какая разница, сколько человек и чем пользуются, если это работает быстро и надежно, то не вижу в этом причины говорить о том, что MySQL говно, без транзакций и тп.
Kirill80
03.12.2017 00:59MySQL было довольно нелепой в результате попыткой сделать общую платформу для реализаций различных парадигм. Но общего оказалось слишком мало. Да и 99% пользователей Дельфина никогда за пределы MyISAM не выходили и не выходят. И насколько быстро и надёжно, и надёжно ли вообще, всё это барахло работает их никогда не волнует.
novoxudonoser
01.12.2017 22:21готов поспорить что просто кто то не сделал VACUUM ANALYZE или неправильно настроил стоимости операций
linuxover Автор
02.12.2017 10:45Вакуум делается, а стоимости операций — ручки глобальные.
вот у Вас 50 таблиц размерами от 1Мб до 200Гб.
вопрос: как правильно настроить стоимости операций чтобы всем таблицам было хорошо?
Если дадите ссылку на документ, описывающий правильную стратегию — буду благодарен :)novoxudonoser
02.12.2017 18:50+2как я и думал, 2 вариант
хинт: какая разница сколько у вас таблиц, индексов и что сколько весит, у вас всё лежит на одной и той же дисковой подсистеме (если нет, то наверно вы не знаете что и зачем делаете), поэтому глобальные настойки вам подходят для всех таблиц
>вопрос: как правильно настроить стоимости операций чтобы всем таблицам было хорошо?
взять и измерить, записать в конфиг
без актуальных данных по стоимости операций вся эвристика и оптимизации постгреса летят в трубу, что вы и наблюдаете, пытаться явно задать индекс для запроса в данной ситуации — ламерское решение (кстати и это вы делаете не совсем хорошо, есть специальное расширение, которое позволяет указывать какой план и индексы использовать)linuxover Автор
02.12.2017 20:13хинт: какая разница сколько у вас таблиц, индексов и что сколько весит, у вас всё лежит на одной и той же дисковой подсистеме (если нет, то наверно вы не знаете что и зачем делаете), поэтому глобальные настойки вам подходят для всех таблиц)
я только не понимаю как хинт поможет постгрису по другому выбирать индексы.
вот он посмотрел на статистику индекса 1 и индексов 2 и 3 и выбрал выбирать из индексов 2 и 3, что является ошибкой в 100% случаев. Как помогут ручки про страницы в данном случае?novoxudonoser
02.12.2017 22:32когда постгрес строит план запроса он смотрит не только лишь на статистику индексов, а пытается найти такой план в котором грубо говоря (потому что учитывается много чего в т.ч. текущая доступность cpu и дисков, гистограммы, и т.д. и это немного по другому работает) количество относительных операций было бы минимальным, если у вас неправильно настроено соотношения стоимостей последовательного к случайному чтению постгрес вполне может (что очень часто наблюдается и скорее всего и у вас происходит) прибегнуть к полному сканированию таблицы или не оптимальному использованию индексов т.к. свято будет уверен что это самый оптимальный путь
linuxover Автор
04.12.2017 12:40+1> когда постгрес строит план запроса он смотрит не только лишь на статистику индексов, а пытается найти такой план в котором грубо говоря (потому что учитывается много чего в т.ч. текущая доступность cpu и дисков, гистограммы, и т.д. и это немного по другому работает)
я это знаю. Я говорю, что выбор плана, предполагающего мерж двух выборок — при наличии точного индекса — является 100%-й ошибкой планирования.
sevikl
04.12.2017 10:29кстати и это вы делаете не совсем хорошо, есть специальное расширение, которое позволяет указывать какой план и индексы использовать
вместо того, чтобы желчью плеваться, можно бы написать, что за расширение и какие там есть ручки.
Kirill80
02.12.2017 10:42Проблема, видимо, в том, что ваши «таблицы» уже крайне фрагментированы, индексы давно потеряли актуальность, а настройка сбора статистики неадекватна ситуации. И имело бы смысл решать именно эти проблемы, а не заставлять Слона пользоваться кривыми индексами.
linuxover Автор
02.12.2017 10:54первый раз, столкнувшись с такой проблемой я попробовал VACUUM FULL + REINDEX всех индексов и ANALYZE.
проблеме это не помогло.
если у вас есть индексы
a,b,cиa,b,e+e, то существует ненулевая вероятность что выборке изa,b,cпланировщик предпочтёт перемножение выборокa,b,e, иe.
делает он это на основании статистики с индексов. Причем эта статистика базируется на коэффициентах и (по видимому) не учитывает абсолютные размеры таблиц.
на первых порах мы это "лечили" путем отказа от одного из индексов:
a,b,cилиa,b,e. То есть чтобы у планировщика не было из чего выбирать.
но поскольку проект большой, варианты у него рано или поздно появляются… увы.linuxover Автор
02.12.2017 11:04Причем эта статистика базируется на коэффициентах и (по видимому) не учитывает абсолютные размеры таблиц.
если смотреть на данный EXPLAIN то выборка из одного индекса дает коэффициент 4млн/80 млн == 0.05, а вторая выборка дает коэффициент 0.3 / 80 = 0.004. Возможно Pg видя что коэффициенты маленькие (это моё предположение, а не знание) предпочел выборку из этих двух индексов, по аналогии с тем, как он на основании статистики предпочитает иногда сделать seq-scan вместо выборки из индекса
Komzpa
02.12.2017 14:28По умолчанию Postgres считает, что в данных нет внутренних корреляций и селективность одной колонки можно просто перемножить на другую, чтобы получить общую селективность. Нативный путь чинить это — создать статистику на пару колонок, www.postgresql.org/docs/10/static/sql-createstatistics.html
linuxover Автор
02.12.2017 14:43-1CREATE STATISTICS появилось только в Pg10, мы пока еще огребаем от нестабильности 9.5, ставить 10 в прод, я считаю, еще где-то год нельзя будет.
PS: из последних проблем 9.5 — коррапт индексов: потребовалось обновить 9.5.5 на 9.5.7 и перестроить индексы. На инстансах 9.3 таких проблем не встречается.
ну а на 10 — пока разбивается нос о проблемы 9.5 ехать продом совсем-совсем уж рано.linuxover Автор
02.12.2017 14:50Не знаю что минусовать. Я ж не выдумываю:
С индексами разбивали нос об эту проблему:
очень долго диагностили что не так с 9.5, почему индексы ломаются. пытались собрать тесткейз, а потом очень кстати (уже было собрались делать масштабный откат на 9.3) вышло обновление от коммюнити с фиксом.
Kirill80
02.12.2017 18:09А вы пытались лепить индексы в конкурентном режиме?
linuxover Автор
02.12.2017 20:14на больших БД в продакшенах индексы возможно строить только в конкурентном режиме. Если не рассматривать вариант простоя БД конечно.
Kirill80
03.12.2017 01:19Скажем так, в конкурентом режиме, но с одним или многими активными сеансами? Просто у меня застарелая привычка не вводить индексы, когда кто-то ещё пользуется БД. Вот и не знаю — отказываться от неё или пока нет.
Kirill80
02.12.2017 17:52Ну, вы понимает, что это просто светская беседа. Я не знаю вашего опыта, вы — моего. Если брать из арсенала общих «измышлений», то проблемы с индексами всегда проблемы со статистикой, проблемы с фрагментацией «таблиц», которые эти индексы обслуживают, ну и проблемы с пониманием того, что много индексов это совсем не хорошо, а полное сканирование далеко не всегда плохо.
В случае Слона, на мой взгляд, нужно вдумчиво курить его реализацию MVCC и читать по теме Index Bloat. Адекватная настройка автовакуума хоть и творит чудеса, но в конце концов всё равно без CLUSTER-а не обойтись.linuxover Автор
02.12.2017 18:02много индексов это совсем не хорошо, а полное сканирование далеко не всегда плохо
начиная с какого-то размера таблиц полное сканирование становится всегда плохо
ну а много индексов плохо только при update’ах
а вот запрос по индексу + filtered секция в explain в моём опыте всегда приводит в итоге к факапу
Kirill80
02.12.2017 18:19Много индексов всегда плохо, на мой взгляд. Полное сканирование, даже очень больших таблиц, зачастую вполне адекватное решение. Потому что на поддержание огромного кол-ва индексов в актуальном состоянии, к тому же на поддержание адекватной статистики, может просто не хватить (никаких мыслимых) ресурсов.
linuxover Автор
02.12.2017 20:16ну много это понятие относительное.
мы практикуем частичные индексы: вот страница, вот выборка для нее. если есть константные данные — они уезжают из индекса в его условие. в итоге получается скажем в индексе на таблице о 80 млн записей всего 1-2 тыс записей.
и таких индексов примерно 10-15. Много это?
Kirill80
02.12.2017 18:41А вы не пробовали реализовать ваши ситуацию в какой-нибудь… «нормальной» (я не всерьёз) СУБД? В том же Оракле. Я к тому, что если в нём планировщик что-то подобное нарисует, то стоит задуматься об адекватности модели данных вообще.
linuxover Автор
02.12.2017 20:28у Оракла есть вот и вот, может и постгрису стоит подумать в этом ключе?
для постгриса увы подобное решение — "внешнее". Закладывать на него прод == брать на себя его поддержку и [или] надеяться
Kirill80
02.12.2017 22:42Вот боялся, что вы до понятия хинтов (в Оракле) дойдёте. Они там исключительно дать традиции (legacy). С хинтами результат всегда хуже. Хинты это однозначно плохо. И отлично, что их нет в Слоне.
Woodroof
05.12.2017 16:48Не всегда.
Пример: есть пустая таблица с индексом, которая заполняется запросом с подзапросом, ссылающимся (по индексу) на эту же таблицу.
Статистику можно обновить только между запросами, а вот задать хинтом nested loop по индексу помогает. Точнее, помогало бы, если бы Oracle аж с версии 10 не начал эти хинты посылать лесом.
В итоге рабочее решение — заполнить таблицу данными, собрать статистику, выбрать нужный план из нескольких и прибить его. После этого можно уже очищать и работать по обычной схеме. Так вот, это куда менее удобно, чем просто хинты.
Ну и опять же отсутствие хинтов приводит к тому, что необходимость прибивать планы возникает в неожиданных местах в неожиданное время.
Рабочие хинты да, как правило уменьшили бы скорость выполнения, но при правильном использовании гарантировали бы отсутствие нежданчиков.linuxover Автор
05.12.2017 19:46+1> Рабочие хинты да, как правило уменьшили бы скорость выполнения
увеличили, как правило.
человек, пишущий хинт, как правило понимает что делает.
человек, который не понимает, не пишет хинт.
вот в примере что в статье: выборка из точного индекса ВСЕГДА будет быстрее мержа двух-трех выборок. И то что Pg предпочитает иногда две выборки — одной — стопроцентная бага планировки.
Если б можно было написать хинт, то написали и поехали дальше.
а тут живешь как на пороховой бочке: построил индекс для X, а Y из за этого стало работать медленнее или вообще факапить. Почему? потому что индекс для Y, который несколько лет успешно пользовался, оно взяло и заменило на мерж выборок из индексов X (появился) и Z (раньше был)
qw1
05.12.2017 19:54выборка из точного индекса ВСЕГДА будет быстрее мержа двух-трех выборок. И то что Pg предпочитает иногда две выборки — одной — стопроцентная бага планировки
Такая же беда у firebird. Наверное, разработчики тестируют на случаях, когда по группе индексов нужно выбрать 20% данных, а не сотню-другую записей из 10 млн.
symbix
05.12.2017 20:59-1Если в вашем случае действительно ВСЕГДА, то, может, выставить enable_mergejoin = off?
u_story
02.12.2017 21:07Боюсь, быть заминусованным, но возможно, у сервера памяти не хватает на работу с индексами и 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
novoxudonoser
02.12.2017 22:43В ветке выше я уже тонко намекал автору об неправильной настройке, здесь вы меня немного опередили, я хотел поговорит об общих настройках чуть попозже. Думаю что большинство настроек будут более чем приемлимы, иначе у автора постгрес всегда бы секвенскан делал бы.
linuxover Автор
04.12.2017 12:47+1> проще пройтись по таблице с диска
блин уже который пост читаю это. Проблема не в том что делается секскан. Наоборот 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,eu_story
04.12.2017 13:46Планировщик ошибся.
Что у вас стоит в default_statistics_target?linuxover Автор
04.12.2017 13:5050, но простановка в 10000 — ничего не меняет в ошибке. я отвечал выше по этой настройке.
PS: кстати поле e у меня имеет всего 10 разных вариантов, соответственно от этой «ручки» вообще не зависит.
linuxover Автор
04.12.2017 13:46Можете настройки сервера выложить?
$ 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
qw1
02.12.2017 22:01В других СУБД всё куда проще
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 нет явных хинтов?Kirill80
02.12.2017 22:47Хинты это даже хуже практики использования курсоров. Хинты в нормальной реляционной MVCC-СУБД не нужны.
linuxover Автор
04.12.2017 12:51если Pg сделал неверный план запроса, то нет способа заставить его сделать верный план (не меняя запрос, не меняя список индексов). Стоимости страниц в данном случае не влияют: я провел эксперимент и поднял стоимость с 4-х до 400 (то есть увеличил стоимость страницы на два порядка) как в плане запроса фигурировал неправильный набор индексов, при имеющемся **точно подходящем, ЧАСТИЧНОМ** индексе, так это и осталось.
seq_page_cost = 1
random_page_cost = 400
novoxudonoser
02.12.2017 23:07в постгресе нет их т.к. они не нужны, при правильной настройке и обслуживании 99.99% запросов будут выполнятся самым оптимальным образом, в случае сверх редкого кейса не оптимального использования индексов, можно воспользоваться расширением, при помощи которого можно явно задать план запроса
qw1
03.12.2017 00:40Теоретически, да. А практически часто встречается, что СУБД не может подобрать оптимальный порядок JOIN-ов при объединении нескольких таблиц, если также сильные ограничения есть в WHERE (знать надо предметную область и типичные ситуации с данными).
И получается разница 200ms против 50ms, оптимизированных вручную. Или считаете, что этим можно пренебречь: не fullscan, и ладно?Kirill80
03.12.2017 01:06Практически не встречается никогда. Хинты во всех тиражных «платных» СУБД дают, если и дают, только частный результат (в Оракле хинты оставили в качестве традиции, они в нём теперь бесполезны совершенно; в Сиквеле они греют душу неосилянтам, хотя тоже теперь бесполезны; в ДБ их похерили очень давно; в Сибе хинтов нет). Т.е. на момент попыток выполнить конкретный запрос в конкретных временных рамках. Если хинтом удалось в один прекрасный момент получить какой-то сказочный результат, то модель нужно переделывать, она явно неадекватная.
qw1
03.12.2017 08:42
при наличии индексов и по OrderKind, и по OrderDate, при выполнении prepare какой должен быть план?select * from Orders where OrderKind=?k and OrderDate between ?s and ?e
Или надо делать новый индекс по двум столбцам (и так каждый раз при появлении нового запроса?).mayorovp
03.12.2017 10:01Мне как разработчику очень не хотелось бы заниматься расстановкой хинтов каждый раз при появлении нового запроса.
qw1
03.12.2017 10:23Мне бы тоже не хотелось. Однако, реальность бывает такая, что некоторые популярные запросы, поступающие параллельно с 500 рабочих мест, приходится оптимизировать вручную, и от ORM отказываться, лишь бы быстрее работало.
linuxover Автор
04.12.2017 12:58после появления 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 в подобных случаях.
Kirill80
02.12.2017 22:54Вообще вы давно наболевшую проблему подняли в очередной раз. По моему глубокому убеждению разработчик не должен всем этим заморачиваться — настройкой СУБД — он должен просто брать и реализовывать свою модель. А все эти напрасные сложности с настройкой самой СУБД просто не должны проявляться в типичных случаях, или если и должны, то явно и однозначно (посмотрел в журнал событий — и всё стало ясно). А ведь до сих пор, откровенно, настройка типичной «большой» СУБД задача редко тривиальная.
linuxover Автор
04.12.2017 12:59По моему глубокому убеждению разработчик не должен всем этим заморачиваться — настройкой СУБД — он должен просто брать и реализовывать свою модель
в этом случае у разработчика никогда не получится сколь-либо полезного проекта, увы
Komzpa
04.12.2017 10:43Вообще, дорога вам лежит в postgresql-hackers, в тред www.postgresql-archive.org/Bitmap-scan-is-undercosted-td5995072.html — там как раз начали обсуждать, почему bitmap and считается более скоростным планом, чем скан по одному индексу.
CherAlexV
04.12.2017 13:00Не совсем в тему, но.
Мы в проекте переносим всякие выборки из слона в эластику. То есть, слон используется как хранилище, которое много что умеет. Типа работы с json и т.д. И в нем выборка, в основном, идёт по ключам. Все остальное — только в эластике.
Конечно, благо, пока никаких агрегаций нет. Но, думаю, эластика справится.)
Komzpa
А почему вы сразу пытаетесь начать с обмана планировщика, вместо того, чтобы сделать ANALYZE проблемной таблице и убедиться, что по ней вовремя ходит autovacuum?
linuxover Автор
я посчитал ненужным об этом писать в статье. Конечно по таблицам ходит автовакуум. пробовали даже в моменты факапов сделать VACUUM FULL, ANALYZE. Но если постгрис выбрал стратегию которая предполагает другие индексы, то он выбрал.
там есть ручки влияющие на стратегию, но увы они глобальные.
maksm
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). Это необходимо править через более тщательный сбор статистики.
linuxover Автор
Близкий к реальному у меня пример такой:
по c имеется свой индекс (полный), по a,b — свой (полный).
я сейчас перевел одну реплику в мастер и поэкспериментировал с полем c:
SET STATISTICS 1000;иSET STATISTICS 100;иSET STATISTICS 10000;(больше последнего варианта нельзя делать) — нет никакой разницы — запрос строится неверно.с полями a,b ща еще эксперименты в процессе — закончу напишу. Но расширить статистику до 10000 — ИМХО мало что даст: в таблице 82 млн записей.