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

W dniu 23 lipca 2010 10:51 użytkownik Piotr Gasidło
<quaker@barbara.eu.org> napisał:
> 2010/7/23 A. Kretschmer <andreas.kretschmer@schollglas.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