pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bradley Tate <btate@objectmastery.com>
To: Dennis Björklund <db@zigo.dhs.org>
Cc: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query problem
Date: Sat, 10 Jan 2004 01:53:41 +1100
Message-ID: <3FFEC075.2090001@objectmastery.com> (raw)
In-Reply-To: <Pine.LNX.4.44.0401090955490.13405-100000@zigo.dhs.org>
References: <Pine.LNX.4.44.0401090955490.13405-100000@zigo.dhs.org>

Dennis Björklund wrote:

>On Fri, 9 Jan 2004, Richard Huxton wrote:
>
>  
>
>>>>select invheadref, invprodref, sum(units)
>>>>from invtran
>>>>group by invheadref, invprodref
>>>>        
>>>>
>>>For the above query, shouldn't you have one index for both columns
>>>(invheadref, invprodref). Then it should not need to sort at all to do the
>>>grouping and it should all be fast.
>>>      
>>>
>>Not sure if that would make a difference here, since the whole table is being 
>>read. 
>>    
>>
>
>The goal was to avoid the sorting which should not be needed with that 
>index (I hope). So I still think that it would help in this case.
>
>  
>
Thanks for the advice. I tried creating a compound index along with 
clustering the invtran table on it, adding another 512MB RAM, increasing 
shared_buffers to 60000 and increasing sort_mem to 100MB, playing with 
effective cache size in postgresql.conf. This cut the execution time 
down to 4 minutes, which was helpful but still way behind firebird. 
There was still an awful lot of disk activity while it was happening 
which seems to imply lots of sorting going on (?)

Invtran is a big table but it is clustered and static i.e. no updates, 
select statements only.

Mostly my performance problems are with sorts - group by, order by. I 
was hoping for better results than I've been getting so far.

Thanks.

p.s.
Can someone confirm whether this should work from pgadmin3? i.e.  will 
the size of the sort_mem be changed for the duration of the query or 
session?

set sort_mem  to 100000;
select ....etc....;






view thread (11+ messages)  latest in thread

Message-ID: <3FFEC075.2090001@objectmastery.com>
Permalink:  ../3FFEC075.2090001@objectmastery.com/
Also on:    postgresql.org/message-id/3FFEC075.2090001@objectmastery.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: btate@objectmastery.com, db@zigo.dhs.org, dev@archonet.com
  Subject: Re: Slow query problem
  In-Reply-To: <3FFEC075.2090001@objectmastery.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