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

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


Здесь

Разумеется, запрос создан программно и выполняется в течении дня в тысячах комбинаций параметров ("and true" в условиях означает, что в этом месте фильтр не подставлен, со всеми фильтрами запрос получится длиннее). '04:00' это часовой пояс пользователя, выполняющего запрос, тоже принимает разные значения в зависимости от местонахождения пользователя.


В запросах, зависящих от этого запроса все еще веселее. Кстати, база небольшая и занимает место на диске всего лишь в 2,5 раза больше доступной ОЗУ. В случае, когда ОЗУ< 10% от размера базы планировщик на сложных запросах вообще временами вытворяет нечто странное - выполнив запрос, признает план выполнения настолько плохим, что кардинально меняет его и снова признает отвратительным и снова бросается в очередную крайность...

Что касается ссылок на документацию - см. работу сборщика статистики и механизм использования собранной статистики планировщиком. Если честно, серьезно я в этом так и не разобрался - детальной документации на механизм работы нет, а в исходники лезть не захотелось, ограничился тестами над несколькими своими базами данных. Замечу еще о найденном гистерезисе - поменяв настройки в конфиге и поработав с ними мы можем вернуться к старым настройкам и получить _иные_ планы выполнения запросов. В итоге приходим к тому, что необходимо сделать бэкап, удалить кластер, создать с новыми настройками, запустить и залить бэкап. Имхо, это как раз та проблема, из-за которой новички (и не только) не могут настроить нормально свои базы - после множества изменений в конфиге (и перезапусков кластера) сборщик статистики накапливает такой мусор, от которого уже не может избавиться и такая база никогда не сможет работать нормально. Если же залить бэкап на заново созданную базу с выставленными настройками, планировщик работает намного стабильнее и эффективнее.

tadmin MBGгистерезисе - поменяв настройки в конфиге и поработав с ними мы можем вернуться к старым настройкам и получить _иные_ планы выполнения запросов. В итоге приходим к тому, что необходимо сделать бэкап, удалить кластер, создать с новыми настройками, запустить и залить бэкап.
я это тоже наблюдал, только мои проблемы ушли после рестарта постгреса.

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

MBGПлан выполнения меняется только в том случае, когда постгрес вместо аналитического построения плана выбирает эмпирику.
А как определить, какой именно алгоритм был использован для построения плана запроса?[/quot]

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

LeXa NalBat
MBGЧто касается ссылок на документацию - см. работу сборщика статистики и механизм использования собранной статистики планировщиком. Если честно, серьезно я в этом так и не разобрался - детальной документации на механизм работы нет, а в исходники лезть не захотелось, ограничился тестами над несколькими своими базами данных.Мне тоже в исходники заглядывать не хочется. Поэтому и хотелось бы ссылку на информацию о данном факте или тест, демонстрирующий его.

Понимаю, вы хотите ссылку на документацию, но еще раз повторю - не нашел четкого описания таких вещей. Ну нигде не написано, что в движке есть принципиально неустранимые проблемы и вы можете столкнуться с чем угодно при разработке сложной системы. Это проблемы довольно общего характера и касаются не кодирования а теории устойчивости. Какие-то проблемы отражены в форумах, за те лет пять, что работаю с постгресом, на многое натыкался. На испанском языке несколько проблем подобного плана озвучено, на русском по постгресу даже смотреть нечего, в рассылке разработчиков (на английском) тоже много проблем всплывает, полагаю, если вы всерьез работаете с постгресом, то уж рассылку отслеживаете регулярно.
Пример запроса, на котором я отловил проблему, см. выше, саму базу выложить не могу. И проблема с этим запросом совсем не в кэшировании таблиц, это-то как раз решается - например, по крону с помощью dd несложно поднять нужные таблицы/индексы в кэш. Проблема в том, что при пересечении множества таблиц постгрес пытается построить план выполнения, комбинируя порядок объединения таблиц. Это теоретически неразрешимая задача, ибо количество комбинаций растет как факториал. При объединении более чем 5-7 таблиц путь только один - брать таблицы в том порядке, как они заданы в запросе. Чтобы полностью полагаться на сборщик статистики, необходимо принципиально обеспечить отрицательную обратную связь, в то время как сейчас она периодически сменяется положительной обратной связью и начинается блуждание (см. адаптивный поиск) в работе планировщика. То есть понятно, что реализация неустойчива, можно написать тесты, которые это показывают на конкретных данных, я делал тесты на своих данных в трех проектах, везде одно и то же - при объединении многих таблиц работа планировщика неустойчика, но переписав запрос определенным способом (находится эмпирически) можно добиться сходимости к приемлимому плану запроса.

Отдалить включение эвристики можно, но не намного. Когда-то я немало времени потратил, разбираясь с тем, как это сделать, а когда число таблиц в запросе перевалило за десяток, обнаружил, что контролировать построение плана я в принципе не могу. Создание цепочки временных видов и таблиц ситуацию значительно улучшают, но при переносе данных в эскулайт те же самые запросы работают в разы быстрее - и это притом, что запросы написаны именно для постгреса... То есть оказалось, что для систем аналитики проще сменить СУБД, чем так мучаться. Мне не требуется какая-то неестественная логика планировщика, достаточно, если он будет полностью контролируем, так, что план выполнения строго детерминирован самим запросом.

Сейчас еще одна проблема появилась - пострес требует администрирования при серьезном изменении объемов данных, железа и т.п. А с каждым годом на одного администратора приходится все больше серверов. Пока я занимался настройкой постгреса на одном рабочем и одном тестовом сервере для одного проекта, еще ничего, но когда проектов стало три... В общем, уже просто времени нет на настройку постгреса. Со встраиваемой базой есть тот плюс, что все определяется на этапе разработки, установленная система практически не требует сопровождения (даже бэкап можно делать просто скопировав файлы баз данных).

Была мысль сделать форк постгреса, выбросив значительную часть унаследованного кода, и сделав строго детерминированный планировщик, но многоядерные процессоры (осенью интел обещает 8-ми ядерники) и дешевые SATA-диски огромного объема (через 2 года тошиба обещает 5-гиг диски) привели к мысли, что блокировочник ныне оптимальный вариант, а версионник уже не нужен (эпоха версионников - это было время работы пользователей в консоли, когда запросы вводились и выполнялись медленно, а сейчас запросы генерируются автоматически, заранее предсказуемы и могут быть выполнены очень быстро или хотя бы разделены на серию быстро выполняемых запросов, также использую создание реплики основной базы для работы каждого пользователя). Да и версионные файловые системы в линуксе со временем станут стабильнее и будут годны для использования на продакшен-серверах.

LeXa NalBat MBGПонимаю, вы хотите ссылку на документацию, но еще раз повторю - не нашел четкого описания таких вещей. Ну нигде не написано, что в движке есть принципиально неустранимые проблемы...влияние на выбор плана а) статистики предыдущих запросов или б) знания о поднятии табличных данных в кэш - это должно позиционироваться как фича, а не "неустранимая проблема", и следовательно должно быть упоминание об этом (например в документации).


Вот о сборе статистики (командой analyze):

http://www.postgresql.org/docs/8.1/static/runtime-config-query.html

http://www.postgresql.org/docs/8.1/static/planner-stats.html

Указано, что "на лету" статистика не обновляется - "For efficiency reasons, reltuples and relpages are not updated on-the-fly, and so they usually contain somewhat out-of-date values.", возможно, это конфликтует с тем, что данные по использованию shared_buffers доступны в реальном времени - то есть сборщик статистики утверждает, что данное значение в таблице встречается очень часто, при этом таблица частично в shared_buffers и выбирается полный перебор, а на деле выясняется, что несколько минут назад таблица была обновлена и искомые значения очень редки, так что полный перебор явная ошибка и нужен был индексный поиск (хотя индексы, возможно, пришлось бы еще с диска поднять).

Вот параметр, отвечающий за размер общего кэша - shared_buffers. Постгрес отслеживает, какие именно данные сейчас в кэше и соответственно меняет план запроса. А еще старается оценить кэширование операционной системой - effective_cache_size. Как именно управление ресурсами в этом самом shared_buffers происходит тоже где-то было описано, хотя и не очень подробно. Текущее состояние буфера влияет на работу директив enable_* для каждой из таблиц - если таблица в кэше, одно поведение, а если в кэше индексы - другое.

Вообще-то все это и есть достижение и при десятке-другом уникальных запросов к базе ведет себя как полагается. При миллионах же _разных_ запросов ситуация меняется и явно это не было предусмотрено в 80-х годах при создании постгреса.

Что касается "14.3. Controlling the Planner with Explicit JOIN Clauses." - дело в том, что при этом скорость выполнения запроса ниже! Тот план, что является оптимальным, аналитически выполняется медленнее. Я так думаю, что дело в вытеснении данных из буфера - навязывая план выполнения, мы приводим постгрес к необходимости поднять с диска данные и записать в кэш. Если же постгрес сам выбирает план запроса, он минимизирует изменение кэша. Повторно запрос выполняется быстро, но при десятках транзакций в секунду (больше при т аком подходе не получается выжать) постоянная перегенерация кэша просаживает производительность очень сильно (при том, что при автоматическом выборе плана постгрес и несколько сотен транзакций в секунду может вытянуть). Явно не хватает возможности выполнить запрос с указанным планом без внесения изменений в кэш, иначе получается неоправданно затратное выполнение запроса.

LeXa NalBatofftopic. предлагаю другой путь - для каждой таблицы оценить кол-во строк удовлетворяющих ограничениям (кроме джоинов), и начинать с самого меньшего джоинить по HashJoin или NestedLoop. сложность O(n).
Вы предлагаете реализовать планировщик запросов на уровне приложения? И это при том, что запрос
"select count(*) from ... where ..." работает очень медленно? А explain возвращает недостоверные данные -опять же пресловутое "For efficiency reasons, reltuples and relpages are not updated on-the-fly, and so they usually contain somewhat out-of-date values." (ошибается сильно, даже сразу после запуска analyze). Плюс к этому остается неопределенность в том, будет ли постгрес искать по индексу или поднимать саму таблицу. Ага, и этим тоже можно попробовать вручную управлять, но тогда в sql-базе данных нет никакого смысла - проще и эффективнее в tokyocabinet или berkeleydb данные хранить. Хоть что-то планировщик должен делать, иначе зачем он?

APM MBG ... и дешевые SATA-диски огромного объема (через 2 года тошиба обещает 5-гиг диски) ...

Запутался. Щас какой год?

Разумеется, 5 Тб :-)

LeXa NalBat...

В таком случае расскажите нам, как измеряется стоимость I/O при планировании запроса. А также почему при намного более низкой стоимости чтения из кэша и используемой методике определения кэшированных данных стоимость I/O по вашему мнению не меняется.

LeXa NalBat
MBG"For efficiency reasons, reltuples and relpages are not updated on-the-fly, and so they usually contain somewhat out-of-date values." (ошибается сильно, даже сразу после запуска analyze).однако, сразу после запуска ANALYZE эти значения должны соответствовать действительности.

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

Считаете, что параметр effective_cache_size это обман и на самом деле планировщик не использует эту информацию? А также информацию о стоимости ввода/вывода для диска и ОЗУ... По документации, shared_biffers к планировщику отношения не имеет, но то, что я поднимаю таблицу с помощью dd в кэш ОС помогает не везде и не всегда и по симптомам похоже на то, что все-таки оценивается наличие данных в shared_biffers и при планировании запроса предусматривается выборка в первую очередь из него. При чекпойнтах выполняется сохранение данных из shared_biffers, значит, хранимые в этом буфере данные просто отсутствуют на диске и планировщик должен знать, какие данные откуда взять, чтобы не напороться на их некорректную версию или вообще отсутствие нужных данных. Как в этом ракурсе вы оцениваете заявление о независимости планировщика от shared_biffers? Явно зависит, а кот как именно реализован алгоритм, можно найти только в коде. Кстати, LRU алгоритм был модифицирован и не раз с версии 7.4, а в документации обновления соответствующих разделов я не заметил. Так что есть большие сомнения, насколько документация соответствует последним версиям постгреса в плане описания работы планировщика (хотя описанием это сложно назвать, скорее, упоминание).
Если верить документации, то и explain работает абсолютно точно, пока нет модификации данных, при этом почему-то никто не сообщает в этом контексте, что модификация данных имеет быть при запуске читающих транзакций (опосредованно, через версионный механизм - модификация не содержимого, а номера актуальной версии этого содержимого). Существующие проблемы я перечислил выше, не все, конечно, но многие. Ссылку вам на описание аналогичной ситуации привели, причем там неоднозначность возникает на стандартном наборе тестов. Вы же реагируете в точности как разработчики постгреса - "я этого не понимаю и не нашел в документации, значит, проигнорирую". Вы ищите проблемы и несоответствия в документации или на реальной системе? Попробуйте потестировать и увидите сами, что документация далеко не так хорошо соответствует действительности.