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.