Re: Performance question

On Mon, Sep 10, 2001 at 02:34:25PM +0200, Tille, Andreas wrote:
> On Mon, 10 Sep 2001 Herbert(dot)Liechti(at)thinx(dot)ch wrote:
>
> > Use explain. Explain tells you the query plan of the optimizer.
> >
> > explain SELECT .....;
> Thanks I just found the thread "Index usage question" and tried to make
> some profit from it:
>
> explain 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;
>
> NOTICE: QUERY PLAN:
>
> Aggregate (cost=35267.33..36154.62 rows=17746 width=16)
> -> Group (cost=35267.33..35710.98 rows=177458 width=16)
> -> Sort (cost=35267.33..35267.33 rows=177458 width=16)
> -> Seq Scan on hauptdaten_fall (cost=0.00..15024.12 rows=177458 width=16)
>
>
There must be a problem with your indeces. I tried the following:
temp=# CREATE TABLE Hauptdaten_Fall (
temp(# MeldeKategorie text,
temp(# ID integer,
temp(# IstAktuell integer);
CREATE
temp=# explain SELECT MeldeKategorie,count(ID) FROM Hauptdaten_Fall
temp-# WHERE IstAktuell=20 GROUP BY MeldeKategorie
temp-# ORDER BY MeldeKategorie;
NOTICE: QUERY PLAN:

Aggregate (cost=22.67..22.72 rows=1 width=16)
-> Group (cost=22.67..22.69 rows=10 width=16)
-> Sort (cost=22.67..22.67 rows=10 width=16)
-> Seq Scan on hauptdaten_fall (cost=0.00..22.50 rows=10 width=16)

EXPLAIN
temp=# CREATE INDEX hfia ON Hauptdaten_Fall (IstAktuell);
CREATE
temp=# explain SELECT MeldeKategorie,count(ID) FROM Hauptdaten_Fall
temp-# WHERE IstAktuell=20 GROUP BY MeldeKategorie
temp-# ORDER BY MeldeKategorie;
NOTICE: QUERY PLAN:

Aggregate (cost=8.30..8.35 rows=1 width=16)
-> Group (cost=8.30..8.33 rows=10 width=16)
-> Sort (cost=8.30..8.30 rows=10 width=16)
-> Index Scan using hfia on hauptdaten_fall (cost=0.00..8.14 rows=10 width=16)

EXPLAIN
temp=#

Which shows quite clearly that an index scan will improve the situation drastically. Even more
so for you because the table seems to have very many rows in it.

- Einar Karttunen