vyegorovMaxim Boguk1)И так... prepared запрос планируется на этапе когда параметры запроса еще не известны.
2)В случае партиционирования это приводит к тому что план ВСЕГДА будет включать в себя обработку всех партиций,
так как построить универсальный план по другому - невозможно.
Начиная с версии 9.2
PostgreSQL поддерживает параметризованные подготовленные запросы (вторая звездочка).
Другое дело, что были жалобы (я не смогу найти быстро), что первые 4 исполнения проходят быстро, а потом планировщик переклинивает и он выбирает не оптимальные планы. Вроде эта тема была закрыта (патчами).
В связи с этим 2 вопроса:
какие конкретно версии (интересуют все 3 цифры) вы тестировали? в смысле — все ли апдейты стоят?
каким будет поведение на 9.1?
Тут коротко на этот вопрос не ответишь.
Часть1:
Как работают параметризованные prepared запросы - строятся 2 плана общий в начале и частный с учетом параметров при первом вызове.
Далее пока данный prepared запрос работает только с теми параметрами которые подходят под частный план - все в теории хорошо, но стоит 1 раз вызвать этот же prepared запрос с другими параметрами как он НЕОБРАТИМО переключится на generic plan и будет его использовать все время жизни prepared запроса. Иногда это приводит к милым эффектам - вот есть большое java приложение, вот оно сделало prepared запрос для какой то большой партиционированной таблицы. Далее 99.999% случаев запросы работают с текущим месяцем данные которого успешно закешированны в базе, потом какой то менеджер решает случайно посмотреть данные за прошлый месяц и тут наступает ад, так как этот prepared запрос (а они в java Дооолго живут) начинает перебирать архив партиций с данными за последние 10 лет которые лежат на медленных SAS дисках просто для истории, а он может вызываться часто. Вот так сервер работал-работал и упс. Я такую проблему как то 2 недели ловил.
Часть2:
Касательно " в теории хорошо" - на практике в 9.2.10 (последнем) поведение следующее у меня получается:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
prepare test(timestamp, integer) as select * from resstat where to_date=$1 and lbill_id=$2 limit 10;
PREPARE
explain analyze execute test('2015-03-03 00:00:00'::timestamp, 1000);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..20.24 rows=2 width=100) (actual time=0.088..0.088 rows=0 loops=1)
-> Result (cost=0.00..20.24 rows=2 width=100) (actual time=0.085..0.085 rows=0 loops=1)
-> Append (cost=0.00..20.24 rows=2 width=100) (actual time=0.084..0.084 rows=0 loops=1)
-> Seq Scan on resstat (cost=0.00..0.00 rows=1 width=116) (actual time=0.002..0.002 rows=0 loops=1)
Filter: ((to_date = '2015-03-03 00:00:00'::timestamp without time zone) AND (lbill_id = 1000))
-> Index Scan using resstat_2015_03_lbill_id_idx on resstat_2015_03 resstat (cost=0.00..20.24 rows=1 width=83) (actual time=0.082..0.082 rows=0 loops=1)
Index Cond: (lbill_id = 1000)
Filter: (to_date = '2015-03-03 00:00:00'::timestamp without time zone)
Total runtime: 9.192 ms
так повторяется 5 раз... на 6 раз получается план перебтирающий все партции (6 лет архива), и остается таким до победы.
billing=# explain analyze execute test('2015-03-03 00:00:00'::timestamp, 1000);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..184.76 rows=10 width=83) (actual time=1.570..1.570 rows=0 loops=1)
-> Result (cost=0.00..2900.68 rows=157 width=83) (actual time=1.566..1.566 rows=0 loops=1)
-> Append (cost=0.00..2900.68 rows=157 width=83) (actual time=1.565..1.565 rows=0 loops=1)
-> Seq Scan on resstat (cost=0.00..0.00 rows=1 width=116) (actual time=0.002..0.002 rows=0 loops=1)
Filter: ((to_date = $1) AND (lbill_id = $2))
-> Index Scan using idx_resstat_2009_01_lbill_id on resstat_2009_01 resstat (cost=0.00..23.30 rows=1 width=80) (actual time=0.035..0.035 rows=0 loops=1)
Index Cond: (lbill_id = $2)
Filter: (to_date = $1)
-> Index Scan using idx_resstat_2009_02_lbill_id on resstat_2009_02 resstat (cost=0.00..23.59 rows=1 width=80) (actual time=0.013..0.013 rows=0 loops=1)
Index Cond: (lbill_id = $2)
Filter: (to_date = $1)
....
Так что проблему не исправили. Что в сочетании с изложенным в Часть1 делает лобовое использование партиций в сочетании с prepared запросами наработоспособным. Увы.
PS: пойду bug report напишу.
--Maxim Boguk
www.postgresql-consulting.ru
