PostgreSQL Strange Timings

published at 21 Mar, 2010 by Szymon LipiƄski tags: database efficiency postgresql

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.