> > How many rows are in the table?
> # select count(*) from Hauptdaten_Fall ;
> count
> --------
> 257530
> (1 row)
>
> > How many
> > rows actually have IstAktuell=20 (is 177458 a reasonable estimate?).
> Yes.
> ifsg=# select count(*) from Hauptdaten_Fall WHERE IstAktuell=20;
> count
> --------
> 177458
> (1 row)
>
> Moreover I tried explain with:
>
> set enable_seqscan = off;
> 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=0.00..146770.97 rows=17746 width=16)
> -> Group (cost=0.00..146327.32 rows=177458 width=16)
> -> Index Scan using ix_meldekategorie_hauptdaten_fa on hauptdaten_fall (cost=0.00..145883.68 rows=177458
width=16)
>
> I wonder, why the Index IX_IstAktuell_Hauptdaten_Fall for IstAktuell is not
> used and moreover why the query takes now 127s with enable_seqscan = off
> against 32s with the default setting.
Well, the index isn't used because it estimates (apparently
correctly) that not using it is cheaper. Because the information about
whether a row is valid is kept in the heap, for each index hit, the
heap needs to be read to see if the row is visible. This results in
jumping about the heap file with seeks and such plus the index
search itself. When most of the rows are going to be returned, the
sequence scan will generally be cheaper.
Alot of the real time may be being spent in the sort step. You may want
to raise the amount of memory used for sorting and see if that helps.