Re: Big difference in time returned by EXPLAIN ANALYZE SELECT ... AND SELECT ...

2010/7/26 Vitalii Tymchyshyn <tivv00(at)gmail(dot)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[]) GROUP BY 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'::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[]) GROUP BY 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