| 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 |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Richard Huxton | 2004-01-09 08:54:46 | Re: Slow query problem |
| Previous Message | Tom Lane | 2004-01-09 05:12:07 | Re: Slow query problem |