Re: Speed comparison to Oracle. Why was this query slower

On Fri, 22 Feb 2002, Bruce Momjian wrote:

> Tom Lane wrote:
> > Martijn van Oosterhout <kleptog(at)svana(dot)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.