This is a multi-part message in MIME format.
--bound1124085115
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 7bit
One little thing. Did you shutdown sql2000 while testing postgresql? Remember that postgresql uses system cache.
Sql2000uses 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
andI'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.
--bound1124085115--
