Difference between explain analyze and real execution time

From: Artur Zając <azajac(at)ang(dot)com(dot)pl>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Difference between explain analyze and real execution time
Date: 2010-11-15 08:21:34
Message-ID: 000001cb849e$1dce2720$596a7560$@ang.com.pl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I have some simple query (executed with time command):

time psql -c 'explain analyze SELECT te.idt FROM t_positions AS te JOIN
t_st AS stm ON (te.idt=stm.idt AND 4=stm.idm) WHERE te.idtr IN (347186)'

QUERY
PLAN

----------------------------------------------------------------------------
----------------------------------------------------------------------------

Nested Loop (cost=0.00..33.33 rows=2 width=4) (actual time=0.297..0.418
rows=3 loops=1)

-> Index Scan using t_positions_index1 on t_positions te
(cost=0.00..8.43 rows=3 width=4) (actual time=0.140..0.148 rows=3 loops=1)

Index Cond: (idtr = 347186)

-> Index Scan using t_st_index4 on t_st stm (cost=0.00..8.29 rows=1
width=4) (actual time=0.078..0.079 rows=1 loops=3)

Index Cond: ((stm.idt = te.idt) AND (4 = stm.idm))

Total runtime: 0.710 ms

(6 rows)

real 0m3.309s

user 0m0.002s

sys 0m0.002s

Why there is so big difference between explain analyze (0.710 ms) and real
execution time (3309 ms)? Any suggestions?

Psql only execution time:

time psql -c 'explain analyze SELECT blabla()'

ERROR: function blabla() does not exist

real 0m0.011s

user 0m0.001s

sys 0m0.004s

SELECT version();

PostgreSQL 9.0.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20070502 (Red Hat 4.1.2-12), 32-bit

-------------------------------------------

Artur Zajac

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Robert Haas 2010-11-15 15:06:04 Re: Running PostgreSQL as fast as possible no matter the consequences
Previous Message Mladen Gogala 2010-11-15 00:32:54 Re: MVCC performance issue