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