DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=TdhcQ2F6JTQLqUc4/VDk5ijuyx5ZolPZf0rhEf0z3qe0Ewex1NyeQYlLdWQf+X1u3R
	gGi2RKhhrzYDQh0uqzV5EXQLU8Y2QP8rwjjjrgHy2Jgp6DxQjccgtcDbgWiaS9OTVhfD
	OHKfUpzx6GlXhdQraq+EGGbWTUmDDLrFO4D24=
MIME-Version: 1.0
In-Reply-To: <4BBBEED4.1000807@postnewspapers.com.au>
References: <4BBB604F.6060005@ca.com> <4BBBEED4.1000807@postnewspapers.com.au>
Date: Wed, 7 Apr 2010 14:39:26 -0400
Message-ID: <n2m603c8f071004071139o7d29d15avd0a43d36ea7c9e57@mail.gmail.com>
Subject: Re: query slow; strace output worrisome
From: Robert Haas <robertmhaas@gmail.com>
To: Craig Ringer <craig@postnewspapers.com.au>
Cc: Brian Cox <brian.cox@ca.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Type: text/plain; charset=ISO-8859-1

On Tue, Apr 6, 2010 at 10:32 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> On 7/04/2010 12:24 AM, Brian Cox wrote:
>>
>> On 04/06/2010 01:18 AM, Craig Ringer [craig@postnewspapers.com.au] wrote:
>>>
>>> I'm wondering if the issue is with strace rather than Pg. That is to
>>> say, that strace is trying to print:
>>
>> Thanks, Craig: I do think that this is a strace issue.
>>
>>> As for what Pg is doing: creat() returns -1 on error and a file
>>> descriptor otherwise, so the return value appears to indicate success.
>>> I'm kind of wondering what the Pg backend is actually _doing_ though -
>>> if it was using sort temp files you'd expect
>>> open()/write()/read()/close() not just creat() calls.
>>
>> My quesiton exactly: what is the backend doing calling creat() over and
>> over again? Note that this query does complete -- in 30-40 mins.
>
> I'd assume it was tempfile creation, but for the fact that there's nothing
> but creat() calls.
>
> However, we can't trust strace. There may be more going on that is being
> hidden from strace via limitations on the ptrace syscall imposed by SELinux
> / AppArmor / whatever.
>
> I suggest turning on the logging options in Pg that report use of tempfiles
> and disk-spilled sorts, then have a look and see if Pg is in fact using
> on-disk temp files for sorts or materialized data sets.
>
> If it is, you might find that increasing work_mem helps your query out.

Yeah, I'd start with EXPLAIN and then, if you can wait long enough,
EXPLAIN ANALYZE.

You'll probably find it's doing a big sort or a big hash join.

...Robert