From: Josh Berkus <josh@agliodbs.com>
To: Steinar H. Gunderson <sgunderson@bigfoot.com>
To: pgsql-performance@postgresql.org
Subject: Re: Odd sorting behaviour
Date: Wed, 14 Jul 2004 18:41:01 -0700
Message-ID: <200407141841.01863.josh@agliodbs.com> (raw)
In-Reply-To: <20040708101913.GA15871@uio.no>
References: <20040708101913.GA15871@uio.no>
Steinar,
> - The "subquery scan o12" phase outputs 1186 rows, yet 83792 are sorted.
Where
> do the other ~82000 rows come from? And why would it take ~100ms to sort
the
> rows at all? (In earlier tests, this was _one full second_ but somehow
that
> seems to have improved, yet without really improving the overall query
time.
I'm puzzled by the "83792" rows as well. I've a feeling that Explain Analyze
is failing to output a step.
> - Why does it use uid_index for an index scan on the table, when it
obviously
> has no filter on it (since it returns all the rows)?
In order to support the merge join. It should be a bit faster to do the sort
using the index than the actual table. Also, because you pass the <> 0
condition.
> Furthermore, why would
> this take half a second? (The machine is a 950MHz machine with SCSI
disks.)
I don't see half a second here.
> - Also, the outer sort (the sorting of the 58792 rows from the merge join)
> is slow. :-)
I don't see a sort after the merge join. Which version are we talking about?
I'm looking at the 7.4 version because that outputs more detail.
Most of your time is spent in that merge join. Why don't you try doubling
sort_mem temporarily to see how it does? Or even raising shared_buffers?
--
-Josh Berkus
Aglio Database Solutions
San Francisco
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: josh@agliodbs.com, sgunderson@bigfoot.com
Subject: Re: Odd sorting behaviour
In-Reply-To: <200407141841.01863.josh@agliodbs.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