Re: Performance question (stripped down the problem)

On Thu, 20 Sep 2001, Einar Karttunen asked me for query plans for
both M$ SQL and postgresql:

M$ SQL:

|--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1005])))
|--Stream Aggregate(GROUP BY:([Hauptdaten_Fall].[MeldeKategorie])
DEFINE:([Expr1005]=Count(*)))
|--Index
Scan(OBJECT:([IfSG].[dbo].[Hauptdaten_Fall].[IX_MeldeKategorie]),
ORDERED FORWARD)

Postgresql:

time psql ifsg <<...
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=32881.62..33768.91 rows=17746 width=16)
-> Group (cost=32881.62..33325.27 rows=177458 width=16)
-> Sort (cost=32881.62..32881.62 rows=177458 width=16)
-> Seq Scan on hauptdaten_fall (cost=0.00..15024.12 rows=177458 width=16)

real 0m1.382s
user 0m0.040s
sys 0m0.020s

And the other case with enforcing index scan:

time psql ifsg <<...
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)

real 0m0.102s (for sure it´s faster to have a plan if enforced ...)
user 0m0.030s
sys 0m0.020s

Does this help in any way? If I´m not completely wrong also M$ SQL
server prefers to use the index ix_meldekategorie.

Kind regards

Andreas.