Postgresql query tuning

[Likenew]

А кто знает как етот постгрес работает, куда бечь почитать. Вот седни первый день с утра установила пгАдмин. До етого никада не видела. Вопрос такой, первый раз время запроса#1 было 4899мс, время запроса #2, якобы улучшенного, 63мс. А вот после етого, когда первый запрос запустить, то время 62мс. Ето ДБ енгине так работает? Или ето глюк?

Надо улучшить запрос то времени. Речь не идет об индексах. Если что-то можно сделать, то только сам запрос переделать. Мы подключаемся к чужой базе вытащить данные. Масса соединений, масса, среди них в некоторых запросах но >10 <outer join>. Прислали два запроса исходный и улучшенный, узучить и внедрить лучшее в практику. Где можно найти как сам ДБ енгине работает в постгрес?

[Сабина]

А кто знает как етот постгрес работает, куда бечь почитать. Вот седни первый день с утра установила пгАдмин. До етого никада не видела. Вопрос такой, первый раз время запроса#1 было 4899мс, время запроса #2, якобы улучшенного, 63мс. А вот после етого, когда первый запрос запустить, то время 62мс. Ето ДБ енгине так работает? Или ето глюк?

Надо улучшить запрос то времени. Речь не идет об индексах. Если что-то можно сделать, то только сам запрос переделать. Мы подключаемся к чужой базе вытащить данные. Масса соединений, масса, среди них в некоторых запросах но >10 <outer join>. Прислали два запроса исходный и улучшенный, узучить и внедрить лучшее в практику. Где можно найти как сам ДБ енгине работает в постгрес?

Это кеширование результата сработало скорее всего. Когда то же квери запускаете движок это распознаёт и отдает вам результат из Кеша .
Что настроить квери нужно его проанализировать - это целая наука SQL tuning, мне очень нравится одноименная книга кажется от Орайли

[Likenew]

Так запрос был якобы переделан. Т.е. у меня возникло впечатление, что енгине распознал " не переделанный" запрос как " переделанный"? Я пока ниче не трогала и не вникала. Именно изменение в длительности обработки и было представлено как успех переделки.

Сабина, а ты с постгрес работала? Чем там они тюнят? Я ваще ноль, а надо быстро, нас в помощь перебросили

[Сабина]

Так запрос был якобы переделан. Т.е. у меня возникло впечатление, что енгине распознал " не переделанный" запрос как " переделанный"? Я пока ниче не трогала и не вникала. Именно изменение в длительности обработки и было представлено как успех переделки.

Сабина, а ты с постгрес работала? Чем там они тюнят? Я ваще ноль, а надо быстро, нас в помощь перебросили

Ну первый непеределанный же тоже был запущен, оно ж не только последний результат кеширует.
Нет конкретно с этой базой не работала, но Гугел говорит что все какой то pgAdmin пользуются
http://wiki.postgresql.org/wiki/Communi ... _GUI_Tools

Тут подробнее
http://dave.webdev.pgadmin.org/docs/1.6 ... -tips.html

[Likenew]

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

Тогда похоже, что первые 2 результата не из кеша, все остальное...
Спасибо за ссылки. Надо быстро читать

[crypto5]

Если запрос читает с диска данные, то они тоже хорошо кешируются в кеше ФС операционки, что тоже хорошо ускоряет работу.

[kostik78]

Когда делаешь SQL tunning также важно делать cold explain/run (после рестарта сервиса). Postgres достаточно умный с кешированием данный и очень часто последующие запросы работают быстрее так как он загрузил в память индексы и данные. Особенно важно когда размер результируешьго сета меньше чем размер доступной памяти на серевер.

[Likenew]

Как по-умному очистить кеш, чтобы видеть изменеия в перформенсе?

[kostik78]

Как не странно это звучало был но только полные рестатр сервера даст cold start.
http://stackoverflow.com/questions/1216 ... es-buffers
Я вообщем обычно делаю рестарт postgres - это не совсем чистый эксперимент, но разницу в перфоманс конкретного query и оптимизации обычно показывает.

[kostik78]

dup

[Likenew]

Костик , я такого сделать не могу
А сколько там времени надо с последнего времени пользования, где можно посмотреть установки когда чистится?

[kostik78]

Нету там таймаутов Только нагрузка может вытяснить данные.
Кстати Postgers можно еще по другому зайти. Explain достаточно подробный ... можно по нему орентироватся на результаты оптимизации. Почитайте про explain. Он естимайты выдает.

[oleg lebedev]

Как по-умному очистить кеш, чтобы видеть изменеия в перформенсе?

Есть способ очистить кэш.
Ждя этого надо заглушить базу и затем заглушить сервер и затем оиключть питание на 30 сек и всё стартануть назад.
Почему это так сложно? Потомц что кэшируется везде и на разных уровнях. Кэшируется в Базе, на уровне ОС, на уровне RAID Array и даже на уровне HDD и наверное ещё где-то где я не упомянул.

Но если вы спрашиваете как практически это делается, то query исполняется с др. параметрами и/или по истечению какого-то времени, когда этот кэш замещается другим, из другой активности. Postgres весьма специфическая и очень хорошая база. Требует изучения и опыта. Пробуйте разные подходы и читайте форумы по нему.
В Postgres хорошо исполняются hash merged queries по сравнению с sort merged и это надо учитывать, когда вы их пишите.
Например, если у вас большая таблица T с полем id то "select distinct id from T" будет почти всегда хуже чем "select id from T group by id".

[Сабина]

Как по-умному очистить кеш, чтобы видеть изменеия в перформенсе?

А зачем вам собственно такое делать ? Если вы делаете query tuning и меняете его синтаксис улучшение перформанса будет видно и при кешированных данных. Точнее там не факт что будет из Кеша вычитывать , но никто ж не мешает прогнать два раза и сравнить best time

[oleg lebedev]

Как по-умному очистить кеш, чтобы видеть изменеия в перформенсе?

А зачем вам собственно такое делать ? Если вы делаете query tuning и меняете его синтаксис улучшение перформанса будет видно и на кешированных данных

Вероятнее всего часть кэша будет использована всё равно. Если ваша первая query сбегала и притащила какие-то данные, то вторая query может уже притащить эти данные из кэша, скажем файловой системы. Ей нет нужды читать файл, который только что был уже прочитан и находится уже в кэше. Сама база может об этом и не догадываться, т.к. эта часть исполняется на ОС уровне.
Query - это высокоуровневый запрос, которы разбивается на ряд более мелких шагов и эти шаги и связанные с ней данные могут повторяться для совершенно по разному выглядящих queries.

[Сабина]

Но ведь конечная цель чтобы квери бегал быстро в ежедневном продакшене ? Значит все же не стоит циклиться на замерах на пустой кеши, а просто тьюнить как есть ? Мы именно так и делали когда я этим плотно занималась. Случаи когда перестартовывали и сравнивала были только когда нужно было эффективность кеширования оценить

[oleg lebedev]

Но ведь конечная цель чтобы квери бегал быстро в ежедневном продакшене ? Значит все же не стоит циклиться на замерах на пустой кеши, а просто тьюнить как есть ? Мы именно так и делали когда я этим плотно занималась. Случаи когда перестартовывали и сравнивала были только когда нужно было эффективность кеширования оценить

Это зависит от того для чего query. Часто бывают query, которые бегают раз или два в сутки и даже реже. В этом случае performance на кэше никак не поможет понять, что будет в реальности на production.

[Ljolja]

В Postgres хорошо исполняются hash merged queries по сравнению с sort merged и это надо учитывать, когда вы их пишите.
Например, если у вас большая таблица T с полем id то "select distinct id from T" будет почти всегда хуже чем "select id from T group by id".

+1 very good point
@TS, как уже сказали выше, на надо пытаться почистить кеш, надо смотреть / стараться улучшить те цифры, что показывает explain

[Likenew]

Я не знаю, как сделан постгрес, но впечатлена. Даже не могу представить, как ето можно. Пример, исходный запрос выполняется долго, на експлейне смотришь, вначале соединяет большие таблицы, а в самом конце потом применяет фильтрами из <where clause>. Потом, я переделываю запрос, выношу фильтры во <from clause>, чтобы ограничить выборку и соединять маленькие остатки таблиц, они идут в левой части в < left join>, и всегда <seq. scan>. Вижу улучшение. И потом, когда запускаешь старый запрос, то на експлейне уже находишь фильтры в самом начале! Как оно научилось??!! Правда такое происходило с относительно простыми запросами.

[АццкоМото]

Я не знаю, как сделан постгрес, но впечатлена.

Any sufficiently advanced technology is indistinguishable from magic.
как известно, Артур Кларк

Как оно научилось??!!

Элементарно, Уотсон!
как известно, Конан Дойль, тоже Артур

[oleg lebedev]

Я не знаю, как сделан постгрес, но впечатлена.

Если у вас был опыт с Ораклом, то будете впечатлены ещё больше. Postgres даёт полное понимание что такое транзакция. Если в вашей функции есть truncate или даже drop таблицы и вы это уже апроизвели, то вы всё равно сможете откатить транзакцию и всё восстановится как и прежде.
В Оракле есть неприятная ошибка 0155 (Snapshot too old), а в Postgres вы ничего подобного не увидете даже если транзакция длится сутки и больше. Ну и др.

[Интеррапт]

Да, Постгрес с транзакциями всегда хорошо дружил. Кстати, все никак руки не дойдут с Postgres-XC поиграться. Кто-нибудь пробовал?

[Ljolja]

Я не знаю, как сделан постгрес, но впечатлена. Даже не могу представить, как ето можно. Пример, исходный запрос выполняется долго, на експлейне смотришь, вначале соединяет большие таблицы, а в самом конце потом применяет фильтрами из <where clause>. Потом, я переделываю запрос, выношу фильтры во <from clause>, чтобы ограничить выборку и соединять маленькие остатки таблиц, они идут в левой части в < left join>, и всегда <seq. scan>. Вижу улучшение. И потом, когда запускаешь старый запрос, то на експлейне уже находишь фильтры в самом начале! Как оно научилось??!! Правда такое происходило с относительно простыми запросами.

в постгресе есть kоманда analyze, кот. позволяет ему собрать статистику по указаной таблице (в обшем случае не только по таблице), good practice использовать ее после +/- значительного изменения в таблице. Если все таблицы были т.об. проаналиированы, оно должно научится даже до 1го селеkта.
Даже как-то можно увидеть распределение, но я не помню как

[Ljolja]

Если у вас был опыт с Ораклом, то будете впечатлены ещё больше. Postgres даёт полное понимание что такое транзакция. Если в вашей функции есть truncate или даже drop таблицы и вы это уже апроизвели, то вы всё равно сможете откатить транзакцию и всё восстановится как и прежде.

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

[oleg lebedev]

Если у вас был опыт с Ораклом, то будете впечатлены ещё больше. Postgres даёт полное понимание что такое транзакция. Если в вашей функции есть truncate или даже drop таблицы и вы это уже апроизвели, то вы всё равно сможете откатить транзакцию и всё восстановится как и прежде.

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

Вы что-то недопоняли.
Backup тут совсем не причём. Мы говорили про транзакции, а это совсем другое.

Commit - это завершение транзакциии. Есть ещё другой способ завершить транзакцию. Это - rollback. В этом случае всё возвращается в исходную позицию, как до начала транзакции. Oracle и Postgres по разному это понимают. Postgres - это multiversion база и её понимание, что такое транзакция намного более глубокое, чем у Oracle. Посмотрите псевдокод:

transaction start
-- table T exists in both Oracle and PostgreSql
drop table T;
-- table T doesn't exists in either Oracle or PostgreSql
rollback;
-- transaction finished

-- table T exists in PostgreSql
-- table T doesn't exists in Oracle

Oracle говорит, мол что DDL (drop, truncate и пр.) - это не DML и не подлежит откату. Возможно это соответствует SQL стандарту, но Postgres - идёт дальше в этом деле.