PostgreSQL Strange Timings
A very simple query. PostgreSQL 8.4. I have no idea why the simple query
is 7 times faster than the EXPLAIN ANALYZE
version. Any ideas? Anyone?
SELECT COUNT(*), xtype
FROM test
GROUP BY xtype
ORDER BY xtype;
count | xtype
---------+-------
669000 | A
84000 | B
63000 | D
15000 | E
159000 | G
7866000 | H
1000000 | N
144000 | NI
(8 rows)
Time: 3366,822 ms
explain analyze
SELECT COUNT(*), xtype
FROM test
GROUP BY xtype
ORDER BY xtype;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Sort (cost=243136.22..243136.24 rows=8 width=2) (actual time=24544.883..24544.889 rows=8 loops=1)
Sort Key: xtype
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=243136.00..243136.10 rows=8 width=2) (actual time=24544.838..24544.848 rows=8 loops=1)
-> Seq Scan on test (cost=0.00..193136.00 rows=10000000 width=2) (actual time=0.012..11501.738 rows=10000000 loops=1)
Total runtime: 24544.980 ms
(6 rows)
Edition:
It turned out that there is a problem with the gettimeofday()
function as Tom Lane wrote:
You’ve got a machine where gettimeofday() is really slow.
This is common on cheap PC hardware
– http://archives.postgresql.org/pgsql-general/2010-03/msg00770.php
And I really have no idea what to do.