pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: tv@fuzzy.cz
To: Humair Mohammed <humairm@hotmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query Performance SQL Server vs. Postgresql
Date: Mon, 22 Nov 2010 12:00:15 +0100
Message-ID: <132e294a67bfcf8b3078cac8693c6f48.squirrel@sq.gransy.com> (raw)
In-Reply-To: <COL115-W422C281502B5C2F62AE90AA83D0@phx.gbl>
References: <COL115-W5250BF8BDC50E0A64DABE4A8360@phx.gbl>
	<AANLkTin+MPhrYUzkCZjdAMLhF3KKZCCuvDm0=Npf9jz7@mail.gmail.com>
	<COL115-W3987D9D7DE908C5C47792DA8380@phx.gbl>
	<AANLkTi=yFV69yNZpHzKiZRtZ0X0PtKcpOrMNNkAo+Si6@mail.gmail.com>
	<COL115-W186A7B93051E6907958ED6A8380@phx.gbl>
	<AANLkTinQmaQ5Ui-vQwfqWFtOTCj76NWFBr6VDKMdVUCs@mail.gmail.com>
	<COL115-W6193714B75A26E58EA773AA83C0@phx.gbl>
	<3ba1d0462bc7df1c3eb5a6d4777ce08a.squirrel@sq.gransy.com>
	<22586.1290359761@sss.pgh.pa.us>
	<F70A4A8C-A76B-4139-9D24-346A1DCB2CAF@gmail.com>
	<COL115-W422C281502B5C2F62AE90AA83D0@phx.gbl>

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




view thread (28+ messages)  latest in thread

Message-ID: <132e294a67bfcf8b3078cac8693c6f48.squirrel@sq.gransy.com>
Permalink:  ../132e294a67bfcf8b3078cac8693c6f48.squirrel@sq.gransy.com/
Also on:    postgresql.org/message-id/132e294a67bfcf8b3078cac8693c6f48.squirrel@sq.gransy.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tv@fuzzy.cz, humairm@hotmail.com
  Subject: Re: Query Performance SQL Server vs. Postgresql
  In-Reply-To: <132e294a67bfcf8b3078cac8693c6f48.squirrel@sq.gransy.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox