Re: Poor performance on simple queries compared to sql server express

Hello

It is little bit strange - can you send a info about your PostgreSQL
version, send a query, and table description?

In this case, PostgreSQL should to use a hash aggregate, but from some
strange reason, pg didn't do it.

Second strange issue is speed of external sort - it is less than I can
expect.

What I know - a usual advice for MS Win is setting minimal shared bufferes
- 512MB can be too much there.

Regards

Pavel Stehule

2013/8/26 Adam Ma'ruf <adam(dot)maruf(at)gmail(dot)com>

> 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
>