Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows - Mailing list pgsql-performance

On 12/7/10 9: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
Windows2003 (on my test server).  I was shocked at the cost for MS SQL 2008 R2 for a new server (2 CPU license).  I
startedcomparing DB’s and came across postgresql.  It seemed to be exactly what I was after.  All of our programming is
inASP.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
quitesurprising 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
statefield. 

Did you run ANALYZE on the database after creating it and loading the data?  If not, do it and try again (along with
theother suggestions you'll get here).  ANALYZE gathers the statistics that allow the planner to do its job.  Without
statistics,all bets are off. 

Craig

> 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.
>
> 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.
Thisis 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
hereso please don’t flame me.  Any advice is appreciated. 
>
> *Thanks,
> Tom Polak
> Rockford Area Association of Realtors
> */
> The information contained in this email message is intended only for the use of the individual or entity named.  If
thereader of this email is not the intended recipient or the employee or agent responsible for delivering it to the
intendedrecipient, you are hereby notified that any dissemination, distribution or copying of this email is strictly
prohibited. If you have received this email in error, please immediately notify us by telephone and reply email.  Thank
you./
>
> /Although this email and any attachments are believed to be free of any viruses or other defects that might affect
anycomputer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is
freeof viruses, and the Rockford Area Association of Realtors hereby disclaims any liability for any loss or damage
thatresults./ 
>