pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bradley Tate <btate@objectmastery.com>
To: pgsql-performance@postgresql.org
Subject: Slow query problem
Date: Thu, 08 Jan 2004 16:52:05 +1100
Message-ID: <3FFCF005.70108@objectmastery.com> (raw)

Hi,

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 invheadref, invprodref, sum(units)
from invtran
group by invheadref, invprodref

RESULTS
pg 7.3.4  -  5.5 min
pg 7.4.0  -  10 min
fb 1.0.3   -  64 sec
fb 1.5     -   44 sec

* The invtran table has about 2.5 million records, invheadref and 
invprodref are both char(10) and indexed.
* shared_buffers = 12000 and sort_mem = 8192 are the only changes I've 
made to postgresql.conf, with relevant changes to shmall and shmmax.

This is an explain analyse plan from postgresql 7.4:

                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------ 

GroupAggregate  (cost=572484.23..601701.15 rows=1614140 width=39) 
(actual time=500091.171..554203.189 rows=147621 loops=1)
   ->  Sort  (cost=572484.23..578779.62 rows=2518157 width=39) (actual 
time=500090.939..527500.940 rows=2521530 loops=1)
         Sort Key: invheadref, invprodref
         ->  Seq Scan on invtran  (cost=0.00..112014.57 rows=2518157 
width=39) (actual time=16.002..25516.917 rows=2521530 loops=1)
 Total runtime: 554826.827 ms
(5 rows)

Am I correct in interpreting that most time was spent doing the sorting? 
Explain confuses the heck out of me and any help on how I could make 
this run faster would be gratefully received.

Cheers,

Bradley.




view thread (11+ messages)  latest in thread

Message-ID: <3FFCF005.70108@objectmastery.com>
Permalink:  ../3FFCF005.70108@objectmastery.com/
Also on:    postgresql.org/message-id/3FFCF005.70108@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
  Subject: Re: Slow query problem
  In-Reply-To: <3FFCF005.70108@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