Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows

От: Andy Colson
Тема: Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows
Дата: ,
Msg-id: 4CFE7964.80206@squeakycode.net
(см: обсуждение, исходный текст)
Ответ на: 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, )

On 12/7/2010 11:34 AM, Tom Polak wrote:
> We are in the process of deciding on how to proceed on a database
> upgrade.  We currently have MS SQL 2000 running on Windows 2003 (on my
> test server).  I was shocked at the cost for MS SQL 2008 R2 for a new
> server (2 CPU license).  I started comparing DB’s and came across
> postgresql.  It seemed to be exactly what I was after.  All of our
> programming is in ASP.net.  Since I am running MSSQL 2000 I have no
> benefit for .Net integration, so it is not a concern.
>
> I ran a head to head test of MS SQL 2000 and Postgresql 9.0.  Both are
> running on Windows 2003.  What I found was quite surprising and I am
> wondering if anyone can point out what is going on here.
> Here is the test I ran.
> I created 2 tables, the main table had 5 fields with a serial ID field.
> The second table linked to table 1 for a state field.
>
> I had ASP.net via MSSQL create 1,000 records in the main table. Took
> 9.85 seconds to complete.
> Next I had ASP.net via Postgresql create 1,000 records.  Took .65625
> seconds.
> Postgresql smoked MS SQL server on that test.

did you play with the postgresql.conf file?  Maybe turn off fsync?  I'd
guess the above is mssql is flushing to disk while PG isnt.

>
> Next test is to use ASP.net and join all 1,000 rows with table 2 and
> then display the text out.
>
> MS SQL took 0.76 seconds to display
> select name,address,city,state,statename,stateid,other from pgtemp1 left
> join pgtemp2 on state=stateid
>
> Then I did the same test via Postgresql and it took 8.85 seconds!  I
> tried it again as I thought I did something wrong.  I did a few tweaks
> such as increasing the shared buffers.  Still the best I could get it to
> was 7.5 seconds.  This is insanely slow compared to MSSQL 2000.  What am
> I missing.  Here is my SQL statement for postgresql:
> select name,address,city,state,statename,stateid,other from pgtemp1 left
> join pgtemp2 on state=stateid
>
> Any ideas on why the Postgres server is soooo much slower on the
> joins?   I am trying to understand what is going on here so please don’t
> flame me.  Any advice is appreciated.
>

Did you create an index?  That'd be my first guess.  Also, can you run
the sql from the command line client (psql) and see if it takes that
long?  While your in psql, stick a 'explain analyze' infront of your
query, and let's see its output.

Also, as a fair warning: mssql doesn't really care about transactions,
but PG really does.  Make sure all your code is properly starting and
commiting transactions.

-Andy


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

От: Ivan Voras
Дата:
Сообщение: Re: Performance under contention
От: Robert Haas
Дата:
Сообщение: Re: Performance under contention