From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Monnerie <lists.michael.monnerie@is.it-management.at>
Cc: pgsql-admin@postgresql.org
Subject: Re: EXPLAIN time difference in real
Date: Sun, 27 Apr 2014 11:43:09 -0400
Message-ID: <18171.1398613389@sss.pgh.pa.us> (raw)
In-Reply-To: <535CE2A4.6060606@is.it-management.at>
References: <535BACAC.90600@is.it-management.at>
<CADyrUxNYE53ccK9OSsS1t+XU0C_COdozCqB3ps6quwjgBx8-Kw@mail.gmail.com>
<535BF163.9070401@is.it-management.at>
<7956.1398536863@sss.pgh.pa.us>
<535CE2A4.6060606@is.it-management.at>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Michael Monnerie <lists.michael.monnerie@is.it-management.at> writes:
> Am 26.04.2014 20:27, schrieb Tom Lane:
>> Could the planning time possibly account for that? How long does a
>> plain EXPLAIN of the same query take? regards, tom lane
> But I see lots of log entries with this query, all around 1200ms. That's
> why I started investigating:
Hmm ... that leads to a different idea: maybe you have got some other
operation that is (repeatedly) holding an exclusive lock on one of these
tables for about 1200ms? The time needed to acquire AccessShareLock on
a table being selected from is spent in the parser, so that wouldn't be
accounted for either by EXPLAIN's total runtime.
That theory isn't totally satisfying because you wouldn't expect unrelated
processes to all suffer the same wait time, but it's worth eliminating
lock waits as a factor in this. Try turning on log_lock_waits with a
threshold of a few hundred ms.
> Here's the EXPLAIN w/o ANALYZE (exactly the same):
This output doesn't answer my question, which is how long did the EXPLAIN
command take? You could run it with psql's \timing turned on.
> PS: Is there anything I could possibly optimize in that query by another
> index? I don't see anything obvious.
Optimization isn't the problem here: whatever is delaying these queries is
happening outside execution proper.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, lists.michael.monnerie@is.it-management.at
Subject: Re: EXPLAIN time difference in real
In-Reply-To: <18171.1398613389@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