Re: Speed comparison to Oracle. Why was this query slower - Mailing list pgsql-general

From Francisco Reyes
Subject Re: Speed comparison to Oracle. Why was this query slower
Date
Msg-id 20020222222546.C87438-100000@zoraida.natserv.net
Whole thread Raw
In response to Re: Speed comparison to Oracle. Why was this query slower  (Bruce Momjian <pgman@candle.pha.pa.us>)
List pgsql-general
On Fri, 22 Feb 2002, Bruce Momjian wrote:

> Tom Lane wrote:
> > Martijn van Oosterhout <kleptog@svana.org> writes:
> > > On Fri, Feb 22, 2002 at 05:23:40PM -0500, Francisco Reyes wrote:
> > >>> Is Oracle better at aggregate functions?
> > >> How could it be done in a more clever fashion?
> >
> > > By hashing. Get a hash table. For each row, hash the grouping rows to lookup
> > > the intermediate aggregate stage to aggregate this row into. At the end, run
> > > through your hash dumping the results.
>
> Was the original users doing GROUP BY with the aggregate?  I don't
> remember.

I started the thread. Not sure if you refered to me when you wrote about
the "original user".

If so my query was something along the lines:
select field1, count(*) from mytable group by field1;

The table is 770K records and the result took 15 seconds on PostgreSQL. It
only took 10 seconds on Oracle. The Oracle table had 730K rows.

Other queries which would do also a sequential scan, but with a smaller
result set was faster on Postgresql. About 2 seconds on Postgresql and 3
to 4 seconds on Oracle. That other query was:
select field1, count(*) from mytable group by field1 where value between
val1 and val2;

The second query operated on a set of a couple of thousand records.


pgsql-general by date:

Previous
From: missive@frontiernet.net (Lee Harr)
Date:
Subject: Re: Date problem
Next
From: Justin Clift
Date:
Subject: Re: game db