Re: query slow; strace output worrisome

On 7/04/2010 12:24 AM, Brian Cox wrote:
> On 04/06/2010 01:18 AM, Craig Ringer [craig(at)postnewspapers(dot)com(dot)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