On Fri, 9 Jan 2004, Richard Huxton wrote: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 (?)
Not sure if that would make a difference here, since the whole table is being read.select invheadref, invprodref, sum(units)For the above query, shouldn't you have one index for both columns
from invtran
group by invheadref, invprodref
(invheadref, invprodref). Then it should not need to sort at all to do the
grouping and it should all be fast.
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.
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....;
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
