GD Star Rating
loading...

Как оптимизировать SQL-запросы в PostgreSQL?

Это как бы не моя основная область деятельности, но на своём личном проекте приходится и этим заниматься.
Решил тут, пока время свободное, ускорить немного работу сервиса – и столкнулся, в частности, со следующей проблемой: http://pastebin.com/BibLDX7d
Проблема действительно имеет значение – время загрузки соответствующей страницы около 11/64 секунды, из которых около 10/64 уходит на выполнение этого одного-единственного запроса.
Таблица Threads содержит около полумиллиона записей. Есть индекс на BoardId (ASC); на LastPostId (ASC); на IsAnnouncement (DESC) и LastPostId (DESC). Кроме того, на BoardId и LastPostId есть FK, ссылающиеся на другие таблицы. Все поля, кроме LastPostId, IS NOT NULL; на LastPostId такого ограничения нет, но фактически оно тоже выполняется.
Из вывода explain складывается такое ощущение, что эти индексы не используются. Что с этим делать, что почитать по теме – найти не могу.

Админы и сочувствующие посетители hardblog.net посчитали злободневным:игромания это болезнь !

16 Responses to Как оптимизировать SQL-запросы в PostgreSQL?

  1. Xibin:

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

  2. KapSm:

    Сделай один индекс на несколько полей, по которым выполняется выборка.

  3. Cilapp:

    Получить бы доступ к базе. Навскидку:
    1) Сколько занимает запрос без LIMIT 40?
    2) Сколько занимает запрос с действующим индексом на поля IsAnnouncement, LastPostId (именно в этом порядке)?
    3) Сколько занимает запрос из пункта 2, но с выключенными раздельными индексами?

    зы. Хинты в Постгре так и не появились?

  4. Xibin:

    : ну там принципиальная позиция по этому вопросу, насколько я понимаю. Зря, конечно. Хотя по сравнению с mysql они нужны крайне редко.

  5. UPnsuper:

    : Я же говорю – не особо разбираюсь в этих делах 🙂

  6. UPnsuper:

    :
    1) http://pastebin.com/2iDexPPm.
    2) Т.е. без условия на BoardId? Тут что-то очень странное получается: http://pastebin.com/24vykJcA.

  7. UPnsuper:

    : ОМГ, вот это опечатка у меня была!
    Теперь всё ок: http://pastebin.com/nuiQYVN7, страница грузится менее чем за 1/64 секунды.

  8. Cilapp:

    : Про опечатку не понял : Судя по плану – помог составной индекс?

    Еще вопрос: при запросах в страничке вы используете собранные запросы в виде строки (select name from table where id = 1), или bind-переменные? (типа select name from table where id = :p_id). Если вариант первый и одинаковые запросы используются часто – использование bind-переменных улучшит ситуацию в целом

  9. Cilapp:

    : С этими гребаными оптимизаторами лучше быть уверенным точно. Оракл говна не посоветует

  10. UPnsuper:

    :
    > Про опечатку не понял

    А ты посмотри, из чего состоят индексы Threads_FirstPostId_idx, Threads_IsAnnouncement_LastPostId_idx, Threads_LastPostId_idx.

    > Судя по плану — помог составной индекс?

    Помогло исправление опечатки, с индексами всё осталось так же, как было описано в посте 🙂

    > при запросах в страничке вы используете собранные запросы в виде строки (select name from table where id = 1), или bind–переменные?

    Конечно, второе – хотя бы чтобы об sql injections не думать.

    > Если вариант первый и одинаковые запросы используются часто — использование bind–переменных улучшит ситуацию в целом

    А вот про это можно поподробнее?
    Сейчас я никаким специальным образом сами команды не кэширую – каждый раз создаю новый объект команды с соответствующим текстом. Да и не очень понятно, как эти объекты кэшировать, т.к. многопоточность (тупо хранить словарь текст-команда не вариант, команды с одинаковым текстом могут использоваться одновременно в разных потоках), плюс команда ссылается на объект подключения, а подключение тоже открывается новое на каждый запрос (тут уже.net-овская фича пула подключений это дело оптимизирует).
    Или всем этим занимается постгрес?
    Хотя что-то мне подсказывает, что время на разбор запроса составляет мизерную часть времени на выполнение запроса на огромных таблицах…

  11. UPnsuper:

    А вот ещё одна странная хрень (да, я знаю, что это говноструктура): http://pastebin.com/VYEM4cA2

  12. Xibin:

    : а что именно странно?

  13. UPnsuper:

    : Один и тот же запрос при первой попытке выполняется в сто раз дольше, чем при второй. Разве постгрес не должен держать все такие поля в памяти (когда её хватает)?
    А если не должен – то как это настраивается? Хочу, чтобы каждый раз уходило по 283мс (лучше бы, конечно, ещё меньше) – может быть, кто-нибудь подскажет, как это сделать 🙂

  14. Cilapp:

    : Как раз второй запуск и есть результат “держания полей в памяти”. Попробуйте запрос с COUNT(имя поля) вместо COUNT(*)

Добавить комментарий