Re: Performance question (stripped down the problem)

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