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=#
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 |