Re: Performance question

On Mon, Sep 10, 2001 at 02:34:25PM +0200, Tille, Andreas wrote:
> On Mon, 10 Sep 2001 Herbert.Liechti@thinx.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