Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows

От: Gary Doades
Тема: Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows
Дата: ,
Msg-id: 4CFE91ED.3010508@gpdnet.co.uk
(см: обсуждение, исходный текст)
Ответ на: Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows  (Andy Colson)
Список: 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 07/12/2010 7:43 PM, Andy Colson wrote:
> On 12/7/2010 1:22 PM, Justin Pitts wrote:
>>>
>>> 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
>>
>> I do not understand that statement. Can you explain it a bit better?
>
> In mssql you can write code that connects to the db, fire off updates
> and inserts, and then disconnects.  I believe mssql will keep all your
> changes, and the transaction stuff is done for you.
>
> In PG the first statement you fire off (like an "insert into" for
> example) will start a transaction.  If you dont commit before you
> disconnect that transaction will be rolled back.  Even worse, if your
> program does not commit, but keeps the connection to the db open, the
> transaction will stay open too.
As far as I know both MS SQL and  and Postgres work just the same as
regards explicit and implicit (autocommit) transactions, only the
underlying storage/logging mechanisms are different.

Transactions shouldn't make ay real difference to the select/join
performance being complained about though. It's already stated that the
insert performance of postgres far exceeds SQL Server, which is my
experience also.

As already suggested, until we see the exact table definitions including
indexes etc. there's no real way to tell what the problem is. How many
rows are in the second table? It really shouldn't take that much time to
read 1000 rows unless you have a bizarrely slow hard disk.

It would be nice to eliminate any programmatic or driver influence too.
How does the SQL select execute in enterprise manager for mssql and psql
or pgadmin for postgres?

Cheers,
Gary.



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

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