explain analyze taking longer than executing the query?

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: explain analyze taking longer than executing the query?
Date: 2006-11-17 02:46:02
Message-ID: 1163731562.4514.19.camel@dogma.v10.wvs
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Version 8.2beta3.

If I EXPLAIN ANALYZE the query, it reports a time of about 2600 ms. If I
run the query in psql, I can tell visibly that the query takes less time
to execute than 2 seconds. Even stranger, if I wrap the query in a SQL
function and EXPLAIN ANALYZE a query that selects from the SQL function
(which returns the exact same results), that takes about 500 ms.

What am I missing about EXPLAIN ANALYZE that is causing that much
overhead? The query returns 10K rows exactly, out of a 1M record test
dataset.

I attached my queries below.

Regards,
Jeff Davis

jdavis=# explain analyze select id from mytable group by id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=18393.38..18395.38 rows=200 width=4) (actual
time=2603.204..2615.503 rows=10000 loops=1)
-> Seq Scan on mytable (cost=0.00..15988.70 rows=961870 width=4)
(actual time=0.033..1291.057 rows=1000000 loops=1)
Total runtime: 2625.892 ms
(3 rows)

jdavis=# create or replace function mytable_query() returns setof int
language sql as $$ select id from mytable group by id $$;
CREATE FUNCTION
jdavis=# explain analyze select * from mytable_query();
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Function Scan on mytable_query (cost=0.00..12.50 rows=1000 width=4)
(actual time=501.459..513.624 rows=10000 loops=1)
Total runtime: 524.288 ms
(2 rows)

jdavis=#

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Russell Smith 2006-11-17 03:27:56 Re: Eliminating bad characters from a database for upgrading
Previous Message Jeff Davis 2006-11-17 02:33:53 Re: PostgreSQL: Question about rules