Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows

От: Kevin Grittner
Тема: Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows
Дата: ,
Msg-id: 4CFE635F02000025000383D3@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Tom Polak)
Список: pgsql-performance

Скрыть дерево обсуждения

Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Tom Polak, )
 Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  ("Kevin Grittner", )
 Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Andy Colson, )
 Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Andres Freund, )
 Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Craig James, )
 Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Andy Colson, )
  Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Richard Broersma, )
   Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Kenneth Marshall, )
    Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Andy Colson, )
     Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Tom Polak, )
      Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Craig James, )
      Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Gary Doades, )
      Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  ("Kevin Grittner", )
      Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  ("Pierre C", )
       Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Tom Polak, )
        Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Craig James, )
         Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Scott Marlowe, )
          Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Gael Le Mignot, )
           Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Scott Marlowe, )
           Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  ("Pierre C", )
        Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Richard Broersma, )
         Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Justin Pitts, )
        Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Rob Wultsch, )
        Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Robert Haas, )
         Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Andy Colson, )
         Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Tom Polak, )
          Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Robert Haas, )
        Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Andy Colson, )
  Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Gary Doades, )
 Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Mladen Gogala, )

Tom Polak <> 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


В списке pgsql-performance по дате сообщения:

От: Robert Haas
Дата:
Сообщение: Re: Performance under contention
От: Vlad Arkhipov
Дата:
Сообщение: Re: Slow BLOBs restoring