One little thing. Did you shutdown sql2000 while testing postgresql? Remember that postgresql uses system cache. Sql2000 uses a large part of memory as buffer and it will not be available to operating system. I must say that, probably, results will be the same, but it will be a better test.

> I'm guessing the reason your query is faster with SQLServer is because
> of how postgres handles MVCC. Basically, it still has to fetch the main
> page to determine if a row exists. While SQL server doesn't do MVCC, so
> it can just look things up in the index.

Another thing [almost offtopic]:
I would like to add something to understand what does MVCC means and what are the consecuences.
MVCC: multiversion concurrency control. (ehhh...)

Just do this.

Open two psql sessions. Do this:
Session 1:
begin;
update any_table set any_column = 'value_a' where other_column = 'value_b'
-- do not commit
Session 2:
select any_table where other_column = 'value_b'
Watch the result.
Session 1:
commit;
Session 2:
select any_table where other_column = 'value_b'
Watch the result.

Now open two session in query analyzer. Do the same thing:
Session 1:
begin tran
update any_table set any_column = 'value_a' where other_column = 'value_b'
-- do not commit
Session 2:
select any_table where other_column = 'value_b'
Wait for result.
Wait... wait... (Oh, a lock! Ok, when you get tired, go back to session 1.)
Session 1:
commit
Session 2:
Then watch the result.

Which one was faster?

["very, very offtopic"]
Ok. This comparition is just as useless as the other one, because it's comparing oranges with apples (It's funny anyway). I was just choosing an example in which you can see the best of postgresql against 'not so nice' behavior of mssql2000 (no service pack, it's my desktop system, I'll do the same test later with SP4 and different isolation levels and I'll check results). Furthermore, MSSQL2000 is 5 years old now. Does anybody has the same cellular phone, or computer? (I don't want to know :-) ). The big question is 'What do you need?'. No system can give you all. That's marketing 'sarasa'.

Sorry for my english and the noise. [End of offtopic]

Long life, little spam and prosperity.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

>
> One little thing. Did you shutdown sql2000 while testing
> postgresql? Remember that postgresql uses system cache.
> Sql2000 uses a large part of memory as buffer and it will not
> be available to operating system. I must say that, probably,
> results will be the same, but it will be a better test.
>

Shutting done SQL2000 has no effect on PG performancies.

Stephane.




---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq