Re: Performance question (stripped down the problem)

Tille, Andreas writes:

> 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;

> (which should just measure the time needed for this task).  It took my
> E250 (2x400MHz, 2GB) server 20 seconds and this is definitely much to
> long for our application.

I loaded this into 7.2 development sources and it ran 35 seconds
wall-clock time on a much smaller machine.  After I ran what in 7.1 would
be VACUUM ANALYZE it took about 22 seconds.  The difference was that it
was using the index on hauptdaten_fall.istaktuell when it shouldn't.
(Try EXPLAIN to see what it does in your case.  If it's still using the
index you might want to force enable_indexscan = off.)

I also got a minuscule speed-up by replacing the Count(Hauptdaten_Fall.ID)
with Count(*), which acts differently with respect to nulls, so it depends
whether you want to use it.

Besides that, I don't see anything blatantly obvious to speed this up.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter