Skip site navigation (1) Skip section navigation (2)

Re: Slow query problem

From: Bradley Tate <btate(at)objectmastery(dot)com>
To: Dennis Björklund <db(at)zigo(dot)dhs(dot)org>
Cc: Richard Huxton <dev(at)archonet(dot)com>,pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow query problem
Date: 2004-01-09 14:53:41
Message-ID: 3FFEC075.2090001@objectmastery.com (view raw or flat)
Thread:
Lists: pgsql-performance
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....;




In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2004-01-09 15:07:09
Subject: Re: Slow query problem
Previous:From: Richard van den BergDate: 2004-01-09 14:12:42
Subject: Explain not accurate

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group