Re: Performance question (stripped down the problem)

> Hello once more,
>
> I stripped the database down to one single table with only the relevant
> columns. A pg_dump can be found under
>
> http://www.foodborne-net.de/~tillea/ifsg/ifsgtest.dump.bz2
>
> I would really like it if you try the query
>
Hi Andreas
I tried it. See my actions below. The main performance boost is
reached by creating an index and disabling the sequential scan:

-------------------------------------------------------
Without any index;
-------------------------------------------------------

time psql tt <<END
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;
END

real 0m18.128s
user 0m0.010s
sys 0m0.010s

-------------------------------------------------------
create index ix_1 on hauptdaten_fall(meldekategorie);
-------------------------------------------------------

Same statement

real 0m18.259s
user 0m0.020s
sys 0m0.010s

no difference

-------------------------------------------------------
now disable seqscan:
-------------------------------------------------------
time psql tt <<END
set enable_seqscan = off;
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;
END

real 0m3.701s
user 0m0.010s
sys 0m0.000s

Best regards
Herbie
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti http://www.thinx.ch
ThinX networked business services Adlergasse 5, CH-4500 Solothurn
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~