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

Re: Slow query problem

From: Dennis Björklund <db(at)zigo(dot)dhs(dot)org>
To: Bradley Tate <btate(at)objectmastery(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow query problem
Date: 2004-01-09 07:29:57
Message-ID: Pine.LNX.4.44.0401090825500.13405-100000@zigo.dhs.org (view raw or flat)
Thread:
Lists: pgsql-performance
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 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.

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.

-- 
/Dennis Björklund


In response to

Responses

pgsql-performance by date

Next:From: Richard HuxtonDate: 2004-01-09 08:54:46
Subject: Re: Slow query problem
Previous:From: Tom LaneDate: 2004-01-09 05:12:07
Subject: Re: Slow query problem

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