Re: Slow query problem

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....;