From: Craig Ringer <craig@postnewspapers.com.au>
To: Piotr Gasidło <quaker@barbara.eu.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Big difference in time returned by EXPLAIN ANALYZE SELECT ... AND SELECT ...
Date: Mon, 26 Jul 2010 17:15:12 +0800
Message-ID: <4C4D5220.9010101@postnewspapers.com.au> (raw)
In-Reply-To: <AANLkTinHPkEDijSHbxY3KxygO2SWy6XMPLvn=CvnWdG8@mail.gmail.com>
References: <AANLkTinHPkEDijSHbxY3KxygO2SWy6XMPLvn=CvnWdG8@mail.gmail.com>
On 26/07/10 16:35, Piotr Gasidło wrote:
> Hello,
>
> I've found strange problem in my database (8.4.4, but also 9.0beta3,
> default postgresql.conf, shared_buffers raised to 256MB).
>
> EXPLAIN ANALYZE SELECT ...
> Total runtime: 4.782 ms
> Time: 25,970 ms
>
> SELECT ...
> ...
> (21 rows)
>
> Time: 23,042 ms
>
> Test done in psql connected by socket to server (same host, using
> \timing to get runtime).
>
> Does big difference in "Total runtime" and "Time" is normal?
Given that EXPLAIN ANALYZE doesn't transfer large rowsets to the client,
it can't really be time taken to transfer the data, which is the usual
difference between 'explain analyze' timings and psql client-side timings.
Given that, I'm wondering if the difference in this case is planning
time. I can't really imagine the query planner taking 20 seconds (!!) to
run, though, no matter how horrifyingly complicated the query and table
structure were, unless there was something going wrong.
Another possibility, then, is that for some reason queries are being
delayed from starting or delayed before results are being returned, so
the server completes them in a short amount of time but it takes a while
for psql to find out they're finished.
In your position, at this point I'd be doing things like hooking a
debugger up to the postgres backend and interrupting its execution
periodically to see what it's up to while this query runs. I'd also be
using wireshark to look at network activity to see if there were any
clues there. I'd be using "top", "vmstat" and "iostat" to examine
system-level load if it was practical to leave the system otherwise
idle, so I could see if CPU/memory/disk were in demand, and for how long.
--
Craig Ringer
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: craig@postnewspapers.com.au, quaker@barbara.eu.org
Subject: Re: Big difference in time returned by EXPLAIN ANALYZE SELECT ... AND SELECT ...
In-Reply-To: <4C4D5220.9010101@postnewspapers.com.au>
* 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