Re: [PERFORM] Slow query problem

2004-01-09 Thread Stephan Szabo
On Fri, 9 Jan 2004, Richard Huxton wrote: > On Friday 09 January 2004 08:57, 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 quer

Re: [PERFORM] Slow query problem

2004-01-09 Thread Tom Lane
Richard Huxton <[EMAIL PROTECTED]> writes: >> 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. > Sorry - not being clear. I can see how it _might_ help, but will the planner > take into account the fact tha

Re: [PERFORM] Slow query problem

2004-01-09 Thread Bradley Tate
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

Re: [PERFORM] Slow query problem

2004-01-09 Thread Richard Huxton
On Friday 09 January 2004 08:57, 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 > > > (i

Re: [PERFORM] Slow query problem

2004-01-09 Thread Dennis Björklund
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 th

Re: [PERFORM] Slow query problem

2004-01-09 Thread Richard Huxton
On Friday 09 January 2004 07:29, Dennis Björklund wrote: > On Thu, 8 Jan 2004, Bradley Tate 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).

Re: [PERFORM] Slow query problem

2004-01-09 Thread Dennis Björklund
On Thu, 8 Jan 2004, Bradley Tate wrote: > We've set up a little test box (1GHz Athlon, 40G IDE drive, 256M RAM, > Redhat 9) to do some basic comparisons between postgresql and firebird > 1.0.3 and 1.5rc8. Mostly the results are comparable, with one > significant exception. > > QUERY > select i

Re: [PERFORM] Slow query problem

2004-01-08 Thread Tom Lane
Mike Glover <[EMAIL PROTECTED]> writes: > You should bump sort_mem as high as you can stand. with only 8MB sort > memory available, you're swapping intermediate sort pages to disk -- > a lot. Try the query with sort_mem set to 75MB (to do the entire sort in > memory). 7.4 will probably flip over

Re: [PERFORM] Slow query problem

2004-01-08 Thread Bruno Wolff III
On Thu, Jan 08, 2004 at 19:27:16 -0800, Mike Glover <[EMAIL PROTECTED]> wrote: > > You should bump sort_mem as high as you can stand. with only 8MB sort > memory available, you're swapping intermediate sort pages to disk -- > a lot. Try the query with sort_mem set to 75MB (to do the entire sort

Re: [PERFORM] Slow query problem

2004-01-08 Thread Mike Glover
On Thu, 08 Jan 2004 16:52:05 +1100 Bradley Tate <[EMAIL PROTECTED]> wrote: > Am I correct in interpreting that most time was spent doing the > sorting? looks so. your table is about 70MB total size, and its getting loaded completely into memory (you have 12000 * 8k = 96M available). 26s to load