Poor performance on simple queries compared to sql server express

From: "Adam Ma'ruf" <adam(dot)maruf(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Poor performance on simple queries compared to sql server express
Date: 2013-08-26 05:09:59
Message-ID: CAOy5j_Ok1nis33fFTKzj4haqRa3LK28jkJJby186VZyJ3jaRkA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

I wasn't whether or not to mail to the novice mailing list of this one.
Since this is performance related I'm posting it here, but I am definitely
a novice at postgresql - converting from mssql just now.

I have a ~2.5gb table with ~5M rows of data. A query that groups by two
fields and sums a floating field takes approximately 122 seconds. The
equivalent query takes ~ 8seconds in my previous sql server express
installation.

I've tried to vary the parameters in postgresql.conf:
I've tried wavering shared buffers from 512mb to 4000mb
and working_mem from 64mb to 4000mb (i thought this might be the answer
since the execution plan (referenced below) indicates that the sort relies
on an External Merge Disk method)
I've increased the default_statistics_target to 10000 and full vacuum
analyzed
I realize there are no indexes on this table. My main concern is why I
can't get this to run as fast as in sql server express (which also has no
indexes, and the same query takes about 8 seconds)

My system: Windows Professional 64-bit
8 gb of ram
Intel i5-220M CPU @ 2.5GHz

Here is the link to the execution plan: http://explain.depesz.com/s/Ytx3

Thanks a lot in advance and do let me know if you require any more
information to make an informed opinion,
A

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Pavel Stehule 2013-08-26 06:36:37 Re: Poor performance on simple queries compared to sql server express
Previous Message Richard Huxton 2013-08-23 08:14:23 Re: PostgreSQL 9.2.4 very slow on laptop with windows 8