Re: Difference between EXPLAIN ANALYZE SELECT ... total runtime and SELECT ... runtime

W dniu 23 lipca 2010 10:51 użytkownik Piotr Gasidło
<quaker(at)barbara(dot)eu(dot)org> napisał:
> 2010/7/23 A. Kretschmer <andreas(dot)kretschmer(at)schollglas(dot)com>:
>> Maybe cheaply or virtuell hardware?
> (...)
>> There are some issues with functions
>> like gettimoofday(), see here:
> (..)

Just tested it on my workstation. No vserver. The same result.

EXPLAIN ANALYZE SELECT ...

Total runtime: 72.745 ms
Time: 1916,269 ms

SELECT ...

Time: 2121,198 ms

The table structure is:

ocaches
ocaches_2010_06_0 (inherits ocaches)
ocaches_2010_06_1 (inherits ocaches)
ocaches_2010_06_2 (inherits ocaches)
ocaches_2010_06_3 (inherits ocaches)
ocaches_2010_06_4 (inherits ocaches)
....
ocaches_2013_06_4 (inherits ocaches)

Constraint checks by date on each partition.

\d ocaches
Table "ocaches"
Column | Type | Modifiers
-----------------------+-----------------------------+-----------
oc_count | integer |
oc_h_id | integer |
oc_date_from | date |
oc_date_to | date |
oc_duration | integer |
...

Many columns mainly integer and integer[].

Index on column oc_h_id on each paritioned table.

query:

SELECT
oc_h_id,oc_duration,SUM(oc_count) FROM ocaches_joined 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;

--
Piotr Gasidło