Gary,
> In this example the statistics don't matter. The plans used were the same
for
> MSSQL and Postgres. I was trying to eliminate the difference in plans
> between the two, which obviously does make a difference, sometimes in
> MSSQL favour and sometimes the other way round. Both systems, having
> decided to do the same index scan, took noticably different times. The
> Postgres database was fully vacuumed and analysed anyway.
It's also quite possble the MSSQL simply has more efficient index scanning
implementation that we do. They've certainly had incentive; their storage
system sucks big time for random lookups and they need those fast indexes.
(just try to build a 1GB adjacency list tree on SQL Server. I dare ya).
Certainly the fact that MSSQL is essentially a single-user database makes
things easier for them. They don't have to maintain multiple copies of the
index tuples in memory. I think that may be our main performance loss.
--
-Josh Berkus
Aglio Database Solutions
San Francisco