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

Re: Query Performance SQL Server vs. Postgresql

From: tv(at)fuzzy(dot)cz
To: "Humair Mohammed" <humairm(at)hotmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query Performance SQL Server vs. Postgresql
Date: 2010-11-22 11:00:15
Message-ID: 132e294a67bfcf8b3078cac8693c6f48.squirrel@sq.gransy.com (view raw or flat)
Thread:
Lists: pgsql-performance
>
>
> Correct, the optimizer did not take the settings with the pg_ctl reload
> command. I did a pg_ctl restart and work_mem now displays the updated
> value. I had to bump up all the way to 2047 MB to get the response below
> (with work_mem at 1024 MB I see 7 seconds response time) and with 2047 MB
> (which is the max value that can be set for work_mem - anything more than
> that results in a FATAL error because of the limit) the results are below.

Hm, can you post explain plan for the case work_mem=1024MB. I guess the
difference is due to caching. According to the explain analyze, there are
just cache hits, no reads.

Anyway the hash join uses only about 40MB of memory, so 1024MB should be
perfectly fine and the explain plan should be exactly the same as with
work_mem=2047MB. And the row estimates seem quite precise, so I don't
think there's some severe overestimation.

Tomas


In response to

Responses

pgsql-performance by date

Next:From: Samuel GendlerDate: 2010-11-22 11:02:37
Subject: Re: Query Performance SQL Server vs. Postgresql
Previous:From: Omar KilaniDate: 2010-11-22 10:01:54
Subject: Re: Performance under contention

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