From: Matteo Beccati <php@beccati.com>
To: Claudio Freire <klaussfreire@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Sören Meyer-Eppler <soerenme@google.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL 9.0.4 blocking in lseek?
Date: Thu, 29 Dec 2011 10:03:28 +0100
Message-ID: <4EFC2CE0.7050001@beccati.com> (raw)
In-Reply-To: <4EFB62F1.2000700@beccati.com>
References: <4EA82B7C.1040008@google.com>
<9398.1319761978@sss.pgh.pa.us>
<4EFAE7F6.30603@beccati.com>
<4EFB59BC.9000208@beccati.com>
<CAGTBQpZfxmdvoLOt7V5QrU8MYdtR2zq-FWYeQ3ZaEKjYVo5w1g@mail.gmail.com>
<4EFB62F1.2000700@beccati.com>
On 28/12/2011 19:41, Matteo Beccati wrote:
> On 28/12/2011 19:07, Claudio Freire wrote:
>> On Wed, Dec 28, 2011 at 3:02 PM, Matteo Beccati <php@beccati.com> wrote:
>>> The query eventually completed in more than 18h. For comparison a normal
>>> run doesn't take more than 1m for that specific step.
>>>
>>> Do you think that bad stats and suboptimal plan alone could explain such
>>> a behaviour?
>>
>> Did you get the explain analyze output?
>
> Unfortunately I stopped it as I thought it wasn't going to return
> anything meaningful. I've restarted the import process and it will break
> right before the problematic query. Let's see if I can get any more info
> tomorrow.
So, I'm running again the EXPLAIN ANALYZE, although I don't expect it to
return anytime soon.
However I've discovered a few typos in the index creation. If we add it
to the fact that row estimates are off for this specific query, I can
understand that the chosen plan might have been way far from optimal
with some badly picked statistics.
This is the explain analyze of the query with proper indexes in place.
As you can see estimates are still off, even though run time is ~20s:
http://explain.depesz.com/s/1UY
For comparison, here is the old explain output:
http://explain.depesz.com/s/TqD
The case is closed and as Tom pointed out already the lseek-only
activity is due to the fact that the table is fully cached in the shared
buffers and a sequential scan inside a nested loop is consistent with it.
Sorry for the noise.
Cheers
--
Matteo Beccati
Development & Consulting - http://www.beccati.com/
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: php@beccati.com, klaussfreire@gmail.com, tgl@sss.pgh.pa.us, soerenme@google.com
Subject: Re: PostgreSQL 9.0.4 blocking in lseek?
In-Reply-To: <4EFC2CE0.7050001@beccati.com>
* 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