Message-ID: <4BBBEED4.1000807@postnewspapers.com.au>
Date: Wed, 07 Apr 2010 10:32:52 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-GB;
	rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
To: Brian Cox <brian.cox@ca.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: query slow; strace output worrisome
References: <4BBB604F.6060005@ca.com>
In-Reply-To: <4BBB604F.6060005@ca.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.

--
Craig Ringer