DomainKey-Signature: a=rsa-sha1; s=beta; d=google.com; c=nofws; q=dns;
	h=dkim-signature:message-id:date:from:user-agent:
	mime-version:to:cc:subject:references:in-reply-to:content-type:
	content-transfer-encoding:x-system-of-record;
	b=A20hnSO7iyRdgPb/ivtO5fI84KFbRrbrbueEMwrTrYSD6orSvct5K1REDFgThv3b/
	BcsrFvT+MO4A2jIyGVCvw==
Message-ID: <4EAECA0B.8050908@google.com>
Date: Mon, 31 Oct 2011 17:17:15 +0100
From: =?ISO-8859-1?Q?S=F6ren_Meyer-Eppler?= <soerenme@google.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL 9.0.4 blocking in lseek?
References: <4EA82B7C.1040008@google.com> <9398.1319761978@sss.pgh.pa.us>
	<4EAEAC21.5080209@google.com> <27430.1320074881@sss.pgh.pa.us>
In-Reply-To: <27430.1320074881@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

> Note the file number appearing in the lseeks, run "lsof -p PID" against
> the backend process to discover the actual filename of that file, then
> look for a match to the filename in pg_class.relfilenode.

Will do. I need to reproduce the error first which may take a while.

>> I have attached two of the offending execution plans. Anything obviously
>> wrong with them?
>
> What makes you say these are "offending execution plans"?  Both of them
> seem to be completing just fine.

That's exactly the point. The plan looks good, the execution times will 
usually be good. But sometimes, for no immediately obvious reasons, 
they'll run for hours.
I know these are the offending queries because this is what
"select now() - query_start, current_query from pg_stat_activity"
will tell me.

We execute these queries from a Java program via JDBC 
(postgresql-9.1-901.jdbc4.jar). But since little data is being 
transferred between the Java client and the database I hope that cannot 
be the issue.

Anything else I should look out for/log during the next test run?

    Sören