2010/7/26 Vitalii Tymchyshyn <tivv00@gmail.com>:
> 26.07.10 12:15, Craig Ringer написав(ла):
> (...)
> Piotr: You can try preparing your statement and then analyzing execute time
> to check if this is planning time.
You are right.
I've done simple PREPARE (without params, etc).
> REPARE query as select oc_h_id,oc_duration,SUM(oc_count) FROM ocache WHERE oc_date_from >= '2010-07-22'::date AND
oc_date_from>= '2010-07-24'::date AND oc_h_id =
ANY('{"32842","3095","27929","2229","22769","3098","33433","22559","226","2130","226","2130","2229","3095","3098","22559","22769","27929","32842","33433"}'::int[])
GROUPBY oc_h_id, oc_duration;
PREPARE
Time: 19,873 ms
> EXPLAIN ANALYZE EXECUTE query;
...
Total runtime: 3.237 ms
Time: 5,118 ms
> EXECUTE query;
oc_h_id | oc_duration | sum
---------+-------------+------
27929 | 7 | 546
3098 | 7 | 552
27929 | 14 | 512
3098 | 14 | 444
22769 | 14 | 984
32842 | 14 | 444
27929 | 22 | 4
27929 | 15 | 44
32842 | 7 | 552
22769 | 7 | 1356
2229 | 7 | 496
226 | 14 | 536
2130 | 7 | 536
2130 | 14 | 448
226 | 7 | 584
2229 | 14 | 400
33433 | 14 | 444
3095 | 7 | 552
33433 | 7 | 552
3095 | 14 | 444
27929 | 8 | 40
(21 rows)
Time: 3,494 ms
The time matches EXPLAIN ANALYZE runtime.
Compared to not prepared query, its big difference!
> select oc_h_id,oc_duration,SUM(oc_count) FROM ocache WHERE oc_date_from >= '2010-07-22'::date AND oc_date_from >=
'2010-07-24'::dateAND oc_h_id =
ANY('{"32842","3095","27929","2229","22769","3098","33433","22559","226","2130","226","2130","2229","3095","3098","22559","22769","27929","32842","33433"}'::int[])
GROUPBY oc_h_id, oc_duration;
oc_h_id | oc_duration | sum
---------+-------------+------
27929 | 7 | 546
3098 | 7 | 552
27929 | 14 | 512
3098 | 14 | 444
22769 | 14 | 984
32842 | 14 | 444
27929 | 22 | 4
27929 | 15 | 44
32842 | 7 | 552
22769 | 7 | 1356
2229 | 7 | 496
226 | 14 | 536
2130 | 7 | 536
2130 | 14 | 448
226 | 7 | 584
2229 | 14 | 400
33433 | 14 | 444
3095 | 7 | 552
33433 | 7 | 552
3095 | 14 | 444
27929 | 8 | 40
(21 rows)
Time: 22,571 ms
Ok. Is there any way to tune postgresql, to shorten planning time for
such queries?
--
Piotr Gasidło
