pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (15+ messages)  latest in thread

Message-ID: <200407141841.01863.josh@agliodbs.com>
Permalink:  ../200407141841.01863.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200407141841.01863.josh@agliodbs.com

 · 

reply

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