Skip site navigation (1) Skip section navigation (2)

Re: Query Performance SQL Server vs. Postgresql

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "tv(at)fuzzy(dot)cz" <tv(at)fuzzy(dot)cz>, Humair Mohammed <humairm(at)hotmail(dot)com>, "pavel(dot)stehule(at)gmail(dot)com" <pavel(dot)stehule(at)gmail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Query Performance SQL Server vs. Postgresql
Date: 2010-11-21 18:55:54
Message-ID: F70A4A8C-A76B-4139-9D24-346A1DCB2CAF@gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Nov 21, 2010, at 12:16 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> tv(at)fuzzy(dot)cz writes:
>>> Second, I modified the work_mem setting to 2GB (reloaded config) and I see
>>> a response time of 38 seconds. Results below from EXPLAIN ANALYZE:
> 
>> How did you reload the config? Using 'kill -HUP pid'? That should work
>> fine. Have you cheched 'work_mem' after the reload?
> 
>> Because the explain plans are exactly the same (structure, estimated
>> costs). The really interesting bit is this and it did not change at all
> 
>>   Buckets: 1024 Batches: 64  Memory Usage: 650kB
> 
> If that didn't change, I'm prepared to bet that the OP didn't actually
> manage to change the active value of work_mem.

Yep.  All this speculation about slow disks and/or COALESCE strikes me as likely totally off-base. I think the original poster needs to run "show work_mem" right before the EXPLAIN ANALYZE to make sure the new value they set actually stuck. There's no reason for the planner to have used only 650kB if work_mem is set to anything >=2MB.

...Robert

In response to

Responses

pgsql-performance by date

Next:From: Ivan VorasDate: 2010-11-22 00:15:43
Subject: Performance under contention
Previous:From: Tom LaneDate: 2010-11-21 17:16:01
Subject: Re: Query Performance SQL Server vs. Postgresql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group