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