ignitorСтоит ли продолжать и смотреть в сторону кластеризации индексов? http://postgrestips.blogspot.com/2007/06/cluster.html... применяется операция кластеризации (cluster) таблиц по указанному индексу, которая упорядочивает индексы ... http://www.postgresql.org/docs/8.2/static/sql-cluster.htmlcluster a table according to an indexКластеризуется таблица, а не индекс.

ignitorвыполняется по старой таблице
--Total query runtime: 43985 ms.
--2224 rows retrieved.

по новой, распределенной таблице
--Total query runtime: 231156 ms.
--2224 rows retrieved.

причем повторный запрос по обеим таблицам дает одно и то же время (видимо работает кэш)
--Total query runtime: 4828 ms.
--2224 rows retrieved. ignitorEXPLAIN ANALYZE по запросу со старой таблицей

-> Bitmap Heap Scan on accanl (cost=85.59..4951.53 rows=1342 width=16) (actual time=39.763..6831.005 rows=2251 loops=1)
Recheck Cond: ((accsin1 = ANY ('{401,402,403,404,405,406,407}'::bpchar[])) OR (accsin2 = ANY ('{40802,40804,40805,40806,40807}'::bpchar[])))
Filter: ((accanl_fund = 56537) AND ((dateclose >= '2006-07-01'::date) OR (dateclose IS NULL)) AND (dateopen <= '2006-07-31'::date))
-> BitmapOr (cost=85.59..85.59 rows=4518 width=0) (actual time=2.349..2.349 rows=0 loops=1)
-> Bitmap Index Scan on idx30 (cost=0.00..54.80 rows=3336 width=0) (actual time=1.598..1.598 rows=3330 loops=1)
Index Cond: (accsin1 = ANY ('{401,402,403,404,405,406,407}'::bpchar[]))
-> Bitmap Index Scan on idx31 (cost=0.00..30.11 rows=1181 width=0) (actual time=0.747..0.747 rows=1116 loops=1)
Index Cond: (accsin2 = ANY ('{40802,40804,40805,40806,40807}'::bpchar[]))

-> Index Scan using idx_dia_1 on fvaluedia (cost=0.00..2.48 rows=1 width=8) (actual time=1.221..1.498 rows=1 loops=2251)
Index Cond: (fvaluedia.idrow = accanl.id)
Filter: ((idfeature = 749630) AND ((fvalue)::text <> ALL (('{Накопительный,"Счет КБК"}'::character varying[])::text[])) AND (idfeature IS NOT NULL))

тоже с новой

-> Bitmap Heap Scan on accanl (cost=85.59..4951.53 rows=1342 width=16) (actual time=3.092..64.353 rows=2251 loops=1)
Recheck Cond: ((accsin1 = ANY ('{401,402,403,404,405,406,407}'::bpchar[])) OR (accsin2 = ANY ('{40802,40804,40805,40806,40807}'::bpchar[])))
Filter: ((accanl_fund = 56537) AND ((dateclose >= '2006-07-01'::date) OR (dateclose IS NULL)) AND (dateopen <= '2006-07-31'::date))
-> BitmapOr (cost=85.59..85.59 rows=4518 width=0) (actual time=2.146..2.146 rows=0 loops=1)
-> Bitmap Index Scan on idx30 (cost=0.00..54.80 rows=3336 width=0) (actual time=1.539..1.539 rows=3330 loops=1)
Index Cond: (accsin1 = ANY ('{401,402,403,404,405,406,407}'::bpchar[]))
-> Bitmap Index Scan on idx31 (cost=0.00..30.11 rows=1181 width=0) (actual time=0.603..0.603 rows=1116 loops=1)
Index Cond: (accsin2 = ANY ('{40802,40804,40805,40806,40807}'::bpchar[]))

-> Index Scan using idx_dia_1 on fvaluedia (cost=0.00..2.88 rows=1 width=8) (actual time=0.024..0.030 rows=1 loops=2251)
Index Cond: (fvaluedia.idrow = accanl.id)
Filter: ((idfeature = 749630) AND ((fvalue)::text <> ALL (('{Накопительный,"Счет КБК"}'::character varying[])::text[])) AND (idfeature IS NOT NULL))В этом сравнительном тесте вы допустили ошибку. Видимо "кэш сработал" не только при повторных запросах к обоим таблицам, но и например при запросе к новой таблице. Обратите внимание, что одинаковые части запроса выполняются на несколько порядков разное время: "Bitmap Heap Scan on accanl" 6831.005 против 64.353, и "Index Scan using idx_dia_1 on fvaluedia" 1.498 против 0.030. Для корректного тестирования надо смотреть время выполнения второго подряд запроса (данные уже загружены в кэш), либо "шаманством" очищать кэш.

ignitorЧудеса - сделал две вьюхи

результаты поразительные

--Total query runtime: 38797 ms.
--260805 rows retrieved.

--Total query runtime: 2812 ms.
--260805 rows retrieved.

Вообщето по explain видно что до вьюх набор из 7 месяцев поднимался с диска 2224 раза, а в последнем варианте только 1 раз.Ничего не видно. :-) Что вы сравниваете? А: запрос с "group by", возвращающий 2224 строки, при котором "набор из 7 месяцев поднимался с диска 2224 раза" с Б: запросом с view, который без "group by", и возвращает 260805 строк? Нет уж, давайте сравнивать планы выполнения запросов, возвращающих одинаковые результаты. Для этого приведите пожалуйста план выполнения аналогичного запроса без view, к которому относится ваш комментарий "Total query runtime: 38797 ms. 260805 rows retrieved".

MBGПри использовании группировок планировщик запроса часто себя ведет, скажем так, неадекватно. http://www.redivi.com/~bob/oscon2005_pgsql_pdf/OSCON_Explaining_Explain_Public.pdfYou are not smarter than the planner (where you != Tom):-)

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

ignitorЕще одна неприятность - раньше простой и быстрый запрос:

select restanl_date from restanl where restanl_date<='2007-07-01' order by restanl_date desc limit 1

теперь выполняется жутко долгоИнтересная проблема. Пока не могу придумать, как ее решить. :-( Разработчики оптимизировали запросы "order by limit", но не для партиционных таблиц с учетом условий check.

MBG LeXa NalBatКластеризуется таблица, а не индекс.Где-то в доках видел, что и файл индекса упорядочивается.сомневаюсь

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

MBGВозможно, что там немного замудренный английский и в глаза это не бросается.

"During the cluster operation, a temporary copy of the table is created that contains the table data in the index order. Temporary copies of each index on the table are created as well. Therefore, you need free space on disk at least equal to the sum of the table size and the index sizes."О, великий и "замудренный" английский язык. :-) Можете перевести цитату, которую вы привели... Индексы перестраиваются, но не кластеризуются (как написал ignitor) и не упорядочиваются (как написали вы в своей статье).

PS: не закрывайте вопросы раньше времени :-)

MBG LeXa NalBat MBGПотому лучше итерационный подход - планировщик по отдельности оптимизирует выполнение каждого запроса и дело в шляпе. И запросы простые и работают быстро.Объясните пожалуйста подробнее этот "итерационный подход", если можно с примером.Примеры были по ссылке. http://postgrestips.blogspot.com/2007/07/temp.html Вы имеете в виду "пример использования временных объектов для построения отчета (выдернул из функции на pltcl)"?

Для начала переведу для вас. "Temporary copies of each index on the table are created as well." "Также создаются временные копии всех индексов таблицы." Перечитаем еще раз вместе "СОЗДАЮТСЯ КОПИИ ИНДЕКСОВ". И все, более ничего не написано касательно индексов.

MBGЕсли индексы строятся упорядоченно и по кластеризованным данным, вы считаете, что они не кластеризованы?Не могу ответить на ваш вопрос, потому что не знаю, что такое "кластеризованный индекс". В постгресе обычно индексы это B-деревья. Я не вижу разницы, строить их "упорядоченно", или нет, все равно получится B-дерево. Как его можно кластеризовать? Не понимаю.

MBGВ цитате перечислены необходимые и достаточные условия кластеризованности индекса в строгом смысле (т.е. и сами данные тоже кластеризованы).В цитате "в строгом смысле" лишь написано "СОЗДАЮТСЯ КОПИИ ИНДЕКСОВ". Какие-либо необходимые и достаточные условия неопределенного понятия "кластеризованность индекса" в ней отсутствуют.

MBGПодразумеваю, что индексы кластеризованы по значениям данных... <В этом> случае и так все очевидно... Но нам и выборка требуется набора данных, а не набора индексов, так что абсолютно бесполезно было бы кластеризовать индекс по его собственным значениям - это привело бы к неоправданным затратам при построении итоговой выборки.Что такое "блабуда"? Это слово я только что придумал из "bla-bla-bla" и "лабуда".

А вот что такое "индексы кластеризованы", "кластеризовать индекс"? Это вы придумали, вы и объясните пожалуйста.

MBG"пример использования временных объектов для построения отчета (выдернул из функции на pltcl)"

Вместе с комментариями.Я стараюсь искать решение на SQL, и чаще всего оно находится. Но, должен признать, иногда приходится использовать plpgsql или другие процедурные языки.

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

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

( PS: Можно кластеризовать таблицу, а потом удалить индекс. :-) Таблица останется кластеризованной. По функционалу, который соответствует индексу. )

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

MBGИ пользовательский интерфейс на sql пишите? Круто :-)Скорее наоборот. У нас много (возможно слишком много) логики находится в приложении. Но как-то (другой) программист написал функцию на plpgsql, с течением времени ее логику приходилось усложнять, потом еще. Потом поняли, что делать очередное усложнение средствами plpgsql слишком сложно, и перенесли ее в приложение переписав на perl, после чего нервы она больше не трепала. :-)