Re: Performance question (stripped down the problem)

On Wed, 19 Sep 2001, Tom Lane wrote:

> No. In the first place, there's no extra sort: the planner is well
> aware that our current GROUP BY implementation produces ordered output.
> In the second place, there's no guarantee that GROUP BY will always
> produce ordered output in the future --- we are thinking about changing
> over to a hash-table-based implementation of grouping. If you want
> ordered output, you should say ORDER BY, not try to outsmart the system.
And even if I would do so - sorting 51 items takes quite nothing. So
the problem is anywhere else. I did further tests and got:

SELECT Count(Hauptdaten_Fall.ID) AS Anz FROM Hauptdaten_Fall WHERE (((Hauptdaten_Fall.IstAktuell)=20)) ;

anz
--------
177458
(1 row) ---------> 2 Seconds

... that means longer than MS-SQL server takes with the additional GROUB BY

and

SELECT Hauptdaten_Fall.MeldeKategorie, Count(Hauptdaten_Fall.ID) AS Anz FROM Hauptdaten_Fall WHERE (((Hauptdaten_Fall.IstAktuell)=20)) GROUP BY Hauptdaten_Fall.MeldeKategorie;

... without ORDER BY but this doesn´t matter regarding the result set
(well it is as ordered as it would be with the ORDER BY clause) and
the time which stays at 18 seconds (after I increased sort_mem = 2048
it was *slightly* faster - 20 seconds for default sort_mem).

So the real bottleneck seems to be the GROUP BY.

Any chances that this could be changed in future PostgreSQL versions?
This slowness makes use of PostgreSQL impossible for our application.

Kind regards

Andreas.