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.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
