Tom Polak <tom(at)rockfordarearealtors(dot)org> wrote:
> I did not tweak anything after installing either system.
PostgreSQL is set up with defaults such that it will start up and
run on the most ancient an underpowered system people are likely to
have lying around. It is expected that people will tune it for
serious production use, although people often run for years before
they hit a case where the tuning makes enough of a difference that
they do something about it. For guidelines see this page:
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
You can get a quick comparison without doing any tuning, but it
won't tell you much about how something else compares to PostgreSQL
when it is configured for production use.
> The hardware it is running on is fairly good, dual Xeon CPUs, 4 GB
> of RAM, Raid 5.
For comparison, I would set shared_buffers to at least 200 MB,
effective_cache_size to 2 to 3 GB, and I would probably drop both
seq_page_cost and random_page_cost to 0.1, unless you actually
expect to be using a database large enough that the active portion
won't be cached. (In that case, a test with tiny tables *really*
means nothing, though.) There are other settings that will also
help.
> "Nested Loop Left Join (cost=0.00..138.04 rows=1001 width=1298)
> (actual time=0.036..4.679 rows=1001 loops=1)"
> "Total runtime: 5.128 ms"
The 0.036 ms is how long it took to produce the first row of the
result once it started running, 4.679 ms is the total run time, and
5.128 includes miscellaneous other time, such as planning time. Of
course, the EXPLAIN ANALYZE adds some overhead, so the actual run
time would normally be faster, and with tuning it might be still
faster.
> The general question comes down to, can I expect decent perfomance
> from Postgresql compared to MSSQL.
That has been my experience. There's something about your runtime
environment which isn't playing well with PostgreSQL. If it were
me, I would make sure that as little of my stack as possible
depended on products provided by anyone with an interest in seeing
PostgreSQL look bad compared to the alternative. I can think of at
least one company with fourteen thousand reasons to do so.
-Kevin