Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id D9B381336B42 for ; Tue, 7 Dec 2010 16:14:45 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 37970-10 for ; Tue, 7 Dec 2010 20:14:38 +0000 (UTC) X-Greylist: delayed 00:16:02.25806 by SQLgrey-1.7.6 Received: from gpdnet.co.uk (gpdnet.plus.com [212.56.100.243]) by mail.postgresql.org (Postfix) with SMTP id 3C4F4133655C for ; Tue, 7 Dec 2010 16:14:38 -0400 (AST) Received: (qmail 18958 invoked by uid 453); 28 Nov 2010 19:12:16 -0000 Received: from pc-00002.gpdnet.co.uk (HELO [192.168.1.2]) (192.168.1.2) by gpdnet.co.uk (qpsmtpd/0.83) with (AES256-SHA encrypted) ESMTPS; Sun, 28 Nov 2010 19:12:16 +0000 Message-ID: <4CFE91ED.3010508@gpdnet.co.uk> Date: Tue, 07 Dec 2010 19:58:37 +0000 From: Gary Doades User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-GB; rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows References: <378e24d1e3af88a49fbbb7c4d26f441a@mail.gmail.com> <4CFE7964.80206@squeakycode.net> <4CFE8E59.2010505@squeakycode.net> In-Reply-To: <4CFE8E59.2010505@squeakycode.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201012/91 X-Sequence-Number: 41543 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.