Уже голову сломал в борьбе с этим оптимизатором.
create temp table _ps_ as select 2026::integer as device_id,
'14.02.2012'::timestamp as t1, '15.02.2012'::timestamp as t2;
explain
SELECT sd.* FROM _ps_, sens_data AS sd
WHERE sd.device_id = _ps_.device_id AND sd.time_stamp BETWEEN _ps_.t1 AND _ps_.t2
Hash Join (cost=5152217.36..10815889.00 rows=24742899 width=29)
Hash Cond: (_ps_.device_id = sd.device_id)
Join Filter: ((sd.time_stamp >= _ps_.t1) AND (sd.time_stamp <= _ps_.t2))
-> Seq Scan on _ps_ (cost=0.00..19.90 rows=990 width=20)
-> Hash (cost=2646998.16..2646998.16 rows=129562816 width=29)
-> Seq Scan on sens_data sd (cost=0.00..2646998.16 rows=129562816 width=29)
Если сказать set seq_page_cost to 100;
Nested Loop (cost=0.00..98786037.54 rows=24742899 width=29)
-> Seq Scan on _ps_ (cost=0.00..1009.90 rows=990 width=20)
-> Index Scan using sens_data_device_id_key on sens_data sd (cost=0.00..99345.48 rows=24993 width=29)
Index Cond: ((sd.device_id = _ps_.device_id) AND (sd.time_stamp >= _ps_.t1) AND (sd.time_stamp <= _ps_.t2))
Понятно, что реально второй запрос несравнимо быстрее, а вот как это объяснить Pg?
Откуда он вообще эти цифры берет? Делал и ANALYZE и даже VACUUM ANALYZE.
После Sybase SQL Anywhere
После Sybase SQL Anywhere оптимизатор PostgreSQL - это "ваще мрак"! Я не уверен, что последний в состоянии адекватно оптимизировать сложные запросы. Так что, миграция с ASA стала похожа на пересаживание с автомата на механику (если сравнивать с автомобилями
). Поигрался с параметрами оптимизатора в postgresql.conf - только расстроился ещё больше, т.к. прироста производительности по сравнению с дефолтными настройками не заметил. Придётся разбивать сложные запросы на составляющие, а потом сливать в один. Короче, брать на себя работу оптимизатора. Другого выхода не вижу. Рад буду ошибиться.
Вы бы лучше сделали
Вы бы лучше сделали сравнительный тест производительности любимого вами Sybase и PostgreSQL на одном железе и одних и тех же данных. Тогда был бы повод говорить, где и что плохо. Вы не обнаружили прироста производительности по сравнению с дефолтными настройками? Так по-моему это говорит о том, что по дефолту настроено хорошо!
PostgreSQL не один год от роду, используется во многих коммерческих проектах. Если бы у него всё было так плохо как вы пишите - кому бы он был нужен?
Боюсь, проблема глубже...
Сравниваю план выполнения одного и того же запроса на Sybase SQL Anywhere и PostgreSQL 9.1.2 и вижу отличия в логике использования индексов не в пользу последнего. Там, где ASA использует одновременно 2 индекса для отработки ограничения в WHERE по 2 полям таблицы, PG берёт 1 индекс и перебор записей для другого поля. И порядок отработки JOIN`ов в PG - другой, неоптимальный...
Я и пишу это всё в надежде, что меня ткнут носом в ошибки настройки параметров. А пока подбор параметров конфигурации в postgresql.conf мне ничего не дал. Не хотелось бы переписывать многоэтажные запросы, доставшиеся в наследство от ASA, надеюсь, что удастся запустить оптимизатор PG в требуемом режиме, чтоб хотя бы повторить скорость выполнения на ASA. Кстати, о железе... PG крутится на более производительном железе. 4 ядра против 2 и 16 ГБ против 8. В обоих случаях - Fedora 16 (x64 bit). На PG-шной kernel.shmmax=8GB.
shared_buffers = 4GB
temp_buffers = 16MB
seq_page_cost = 1.0
random_page_cost = 3.0
cpu_tuple_cost = 0.001
cpu_index_tuple_cost = 0.0005
cpu_operator_cost = 0.0025
effective_cache_size = 14GB
Структура, данные и индексы - те же, что и на ASA. Максимально приближены.
Где я ошибся?..
PG, к счастью, ни при чём.
Прошу прощения, что вклинился в тему, увидев живой диалог. Но, тема для меня весьма актуальна, как и для любого "мигранта" с другой СУБД. Я тут немного погорячился, греша на возможную "простоватость" оптимизатора PG, но при этом не учёл одну деталь: прочитанное мною накануне заявление на одном из ресурсов, посвящённом PG, о том, что, "начиная с 8.х-й версии конструкции expr IN (select field...) и exists (select 1 ... where field=expr...) уравнены в скорости за счёт оптимизации IN (select ...)" - оказалось далёким от истины. Замена первого на второе дала требуемый прирост скорости в миллион раз (по кол-ву записей), о чём спешу сообщить всем, кому интересно. Граждане, тщательно проверяйте сообщения "бывалых" товарищей на форумах! Этим вы сэкономите время и нервы! Слава PG!
Нюансов как говорится полно.
Нюансов как говорится полно. `EXIST` быстрее `IN`, `INNER JOIN` быстрее `OUTER JOIN`, `ORDER BY ... DESC LIMIT 1` быстрее MAX(), и т.д.
Я не понял суть вопроса. Что
Я не понял суть вопроса. Что не нравится?
Если Seq Scan, то PostgreSQL сам знает когда использовать индекс, а когда Seq Scan выбирая оптимальное решение для производительности. Если что-то другое, объясните подробней что.
Не нравится время выполнения
Не нравится время выполнения запроса. С SeqScan - ~10мин, с индексом ~2сек.
"сам знает" - если-бы. С чего собственно он решил что SeqScan в данном случае оптимальнее? У него должна быть статистика по этой таблице. 130 млн. записей в seqscan не могут быть оптимальным решением при наличии индекса хотябы по одной колонке.
Он ведь смог оценить вес запросов: (cost=0.00..99345.48 rows=24993 против cost=0.00..2646998.16 rows=129562816)
Вопрос собственно такой - я что-то упустил, и pg не имеет полной статистики, неправильно построен запрос, или крутить параметры оптимизатора "the only way"?
Кстати если временной интервал задать константами - индекс задействуется.
К сожалению у меня нет ваших
К сожалению у меня нет ваших данных, чтобы поэкспериментировать.
А что даст (сорри синтаксис проверять негде, но думаю поправите, если где ошибся):
Результат у этих запросов должен быть одинаков, но сами запросы не одинаковы.
Результат, ровно как и план
Результат, ровно как и план выполнения одинаков. В данном случае, что INNER JOIN, что CROSS JOIN (который воспринимается неявно) всё равно он их разворачивает.
P.S. Pg 8.0 помнится таких вольностей себе не позволял. `A JOIN B` был реально другой запрос нежели `B JOIN A`.