Hi Andreas,
Interesting problem - Like Justin I am running Postgres 7.1.3 + Mandrake 8.
Your query :
SELECT
hauptdaten_fall.meldekategorie,
count(hauptdaten_fall.id) AS Anz
FROM hauptdaten_fall
WHERE (((hauptdaten_fall.istaktuell)=20))
GROUP BY hauptdaten_fall.meldekategorie
ORDER BY hauptdaten_fall.meldekategorie
takes 19 s on my old hardware ( P2 333Mhz + 384Mb + 2x20Gb ATA)
I can get some improvement by making sort_mem=20480 :
The query then takes 8s - still a bit slow - Mysql does it in 2 s :-(
Increasing sort_mem>20480 did not give any further improvement.
Creating indexes, clustering,fooling about with enable_seqscan +
cpu_tuple_cost did not help at all.
As noted by others it appears that data access is not the issue - as the
query :
SELECT
count(hauptdaten_fall.id) AS Anz
FROM hauptdaten_fall
WHERE (((hauptdaten_fall.istaktuell)=20))
takes only 2 s - so I am guessing that the 8s result is about as good as can
be gotten without delving into the PG code for GROUP BY access.
A slightly complex workaround for better performance is to use a summary
table :
CREATE TABLE hauptdaten_fall_sum(meldekategorie varchar(10),
istaktuell integer,
cnt integer)
and maintain it via triggers on hauptdaten_fall
The offending query then becomes :
SELECT meldekategorie,cnt
FROM hauptdaten_fall_sum
WHERE istaktuell=20;
which is unmeasurably fast ( i.e 0 s ) on my system.
Hope it helps or gives food for thought
regards
Mark
