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