Я перенес свою БД с Postgresql 8.4 на новый сервер с Postgresql 9.1. Размер БД - 9,9 ГБ. Каталог данных находится на ssd-диске емкостью 60 ГБ. А сервер имеет 16 ГБ ОЗУ и 16 ядер процессора.
Но сегодня средняя загрузка выросла до 70.
Я выяснил, что запросы используют хэш-соединение в плане, и один из моих запросов выполняется за 16 минут, но когда я устанавливаю enable_hashjoin = off, он выполняется через 5 минут, когда я устанавливаю enable_mergejoin = off, он становится использовать вложенный цикл и выполняется за 12 мс.
Почему postgresql не использует оптимальный план запроса?
EXPLAIN ANALYZ результаты, которые я вставил в http://explain.depesz.com/s/764 (с enable_hashjoin = on) http://explain.depesz.com/s/weY (с вложенным циклом)
Потому что он думает, что это будет более быстрый план.
У вас очень сложное соединение c и u таблицы. Это настолько сложно, что Постгрес не может предсказать, сколько строк вернет это соединение - он думает, что вернет более 1 600 000 строк, хотя фактически возвращает только 4.
Попробуйте упростить свой запрос - не используйте coalesce или case в соединениях, может быть отдельно c.user_id=? и c.expert_id=? на 2 запроса и union all их. Если в rows x столбец на сайте prepare.depesz.com, то плохая и хорошая производительность запроса будет очень случайной.