From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chris Hutchinson <chris@hutchinsonsoftware.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: EXPLAIN ANALYZE much slower than running query normally
Date: Mon, 11 Oct 2004 20:28:28 -0400
Message-ID: <25940.1097540908@sss.pgh.pa.us> (raw)
In-Reply-To: <IDEOKBCDGGIDOBADNGAPGEFEGBAA.chris@hutchinsonsoftware.com>
References: <IDEOKBCDGGIDOBADNGAPGEFEGBAA.chris@hutchinsonsoftware.com>
"Chris Hutchinson" <chris@hutchinsonsoftware.com> writes:
> Running a trivial query in v7.4.2 (installed with fedora core2) using
> EXPLAIN ANALYZE is taking considerably longer than just running the query
> (2mins vs 6 secs). I was using this query to quickly compare a couple of
> systems after installing a faster disk.
Turning on EXPLAIN ANALYZE will incur two gettimeofday() kernel calls
per row (in this particular plan), which is definitely nontrivial
overhead if there's not much I/O going on. I couldn't duplicate your
results exactly, but I did see a test case with 2.5 million one-column
rows go from <4 seconds to 21 seconds, which makes the cost of a
gettimeofday about 3.4 microseconds on my machine (Fedora Core 3, P4
running at something over 1Ghz). When I widened the rows to a couple
hundred bytes, the raw runtime went up to 30 seconds and the analyzed
time to 50, so the overhead per row is pretty constant, as you'd expect.
Some tests with a simple loop around a gettimeofday call yielded a value
of 2.16 microsec/gettimeofday, so there's some overhead attributable to
the EXPLAIN mechanism as well, but the kernel call is clearly the bulk
of it.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, chris@hutchinsonsoftware.com
Subject: Re: EXPLAIN ANALYZE much slower than running query normally
In-Reply-To: <25940.1097540908@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox