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

Sure

I just upgraded to 9.2.4. The query is:
SELECT quebec_four
, sierra
, SUM(dollaramount) as dollaramount
FROM alpha_quebec_echo
GROUP BY quebec_four
, sierra

alpha_quebec_echo has 5,409,743 rows and 39 columns. Quebec_four and
sierra are both varchar, dollar amount is a floating point field. It has
no indexes (but neither did the mssql express table). Any other details
you need?

Thanks,
A

On Mon, Aug 26, 2013 at 2:36 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>wrote:

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