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

On Fri, Feb 22, 2002 at 05:23:40PM -0500, Francisco Reyes wrote:
> On Sat, 23 Feb 2002, Martijn van Oosterhout wrote:
>
> > On Fri, Feb 22, 2002 at 01:53:40PM -0500, Francisco Reyes wrote:
> > > Is Oracle better at aggregate functions?
> > > Another point worth mentioning. The Oracle table has 40K records less.
> > > Postgresql has 770K, Oracld has 730K.
> >
> > Posssibly. At the moment postgres will grab all the data from the table,
> > sort it, group it and then aggregate. Oracle is probably cleverer and just
> > aggregates directly. On that many rows it probably makes a difference.
>
> How could it be done in a more clever fashion?
> Or how would something be aggregated "directly"?

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.

The advantage is that instead of sorting all the data in memory or possibly
disk, you only have to do a sequential scan and store a structure
proportional to the number of *output* rows.

Where I work this would be cool because we routinely aggregate a million
rows into about 7 groups so this would really help.

I looked into this once but got stuck in the hashing step. The rest looked
ok.
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> If the company that invents a cure for AIDS is expected to make their
> money back in 17 years, why can't we ask the same of the company that
> markets big-titted lip-syncing chicks and goddamn cartoon mice?