Message-ID: <4CFEB124.3010400@gpdnet.co.uk>
Date: Tue, 07 Dec 2010 22:11:48 +0000
From: Gary Doades <gpd@gpdnet.co.uk>
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>
	<AANLkTi=HiO8ErFwzqBvgjXXHRUMKeUWqCpL-p9Y+oL_-@mail.gmail.com>
	<4CFE8E59.2010505@squeakycode.net>
	<AANLkTin4K86j2-GOUfmdXQ8UKv_j9V8ZRiRBEbbXj+jc@mail.gmail.com>
	<20101207201028.GE4028@aart.is.rice.edu>
	<4CFE97B4.8090708@squeakycode.net>
	<fd4bd31b523753145cf67ae6e6cb4aec@mail.gmail.com>
In-Reply-To: <fd4bd31b523753145cf67ae6e6cb4aec@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 07/12/2010 9:29 PM, Tom Polak wrote:
>
>  From EXPLAIN ANALYZE I can see the query ran much faster.
> "Nested Loop Left Join  (cost=0.00..138.04 rows=1001 width=1298) (actual
> time=0.036..4.679 rows=1001 loops=1)"
> "  Join Filter: (pgtemp1.state = pgtemp2.stateid)"
> "  ->   Seq Scan on pgtemp1  (cost=0.00..122.01 rows=1001 width=788)
> (actual time=0.010..0.764 rows=1001 loops=1)"
> "  ->   Materialize  (cost=0.00..1.01 rows=1 width=510) (actual
> time=0.000..0.001 rows=1 loops=1001)"
> "        ->   Seq Scan on pgtemp2  (cost=0.00..1.01 rows=1 width=510)
> (actual time=0.006..0.008 rows=1 loops=1)"
> "Total runtime: 5.128 ms"
>
> The general question comes down to, can I expect decent perfomance from
> Postgresql compared to MSSQL.  I was hoping that Postgresql 9.0 beat MSSQL
> 2000 since MS 2000 is over 10 years old.
>
So postgres actually executed the select in around 5 miiliseconds. 
Pretty good I would say. The problem therefore lies not with postgres 
itself, but what is done with the results afterwards? Assuming that this 
is pure local and therefore no network issues, perhaps there is a 
performance issue in this case with the Npgsql driver? Someone who knows 
more about this driver could perhaps shed some light on this?

I have used .NET (C#) with postgres before, but only using the odbc 
driver. Perhaps you could try that instead (using OdbcCommand, 
OdbcDataReader etc.).

I mainly use ruby (jruby) with postgres both under linux and Windows, 
but I can certainly process 1000 records of similar structure in well 
under 1 second.

Cheers,
Gary.