pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rob Nagler <nagler@bivio.biz>
To: PgSQL Performance ML <pgsql-performance@postgresql.org>
Subject: Re: How to force Nested Loop plan?
Date: Sat, 30 Aug 2003 17:59:33 -0600
Message-ID: <16209.14949.507000.384083@gargle.gargle.HOWL> (raw)
In-Reply-To: <1062278901.669.597.camel@haggis>
References: <16208.41885.960000.12706@gargle.gargle.HOWL>
	<24637.1062255721@sss.pgh.pa.us>
	<16208.50934.351000.90704@gargle.gargle.HOWL>
	<2074.1062260382@sss.pgh.pa.us>
	<16209.3105.94000.204936@gargle.gargle.HOWL>
	<1062278901.669.597.camel@haggis>

Ron Johnson writes:
> Dumb question: given your out-of-the-box satisfaction, could it be
> that postgresql.conf hasn't been tweaked?

Here are the modified values:

shared_buffers = 8000
wal_buffers = 80
sort_mem = 32000
effective_cache_size = 400000
random_page_cost = 4
autocommit = false
timezone = UTC

I had run a test with effective_cache_size to high value to see what
would happen.  Also adjusted random_page_cost:

random_page_cost effective_cache_size	elbow
    4	    	    	40000	    	675
    .5	    	    	40000	    	592
    .1	    	    	40000	    	392
    4	    	    	1000	    	30

My conclusion is that random_page_cost should be left alone and
effective_cache_size higher is better.

BTW, the hardware is 2 x 2.4ghz Xeon, 1.2GB, SCSI (linux software
raid) with 10K disks.  This is close to the production box.  Although
we are planning on adding more memory to production.

Rob





view thread (13+ messages)  latest in thread

Message-ID: <16209.14949.507000.384083@gargle.gargle.HOWL>
Permalink:  ../16209.14949.507000.384083@gargle.gargle.HOWL/
Also on:    postgresql.org/message-id/16209.14949.507000.384083@gargle.gargle.HOWL

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: nagler@bivio.biz
  Subject: Re: How to force Nested Loop plan?
  In-Reply-To: <16209.14949.507000.384083@gargle.gargle.HOWL>
  References: <16208.41885.960000.12706@gargle.gargle.HOWL> <24637.1062255721@sss.pgh.pa.us> <16208.50934.351000.90704@gargle.gargle.HOWL> <2074.1062260382@sss.pgh.pa.us> <16209.3105.94000.204936@gargle.gargle.HOWL> <1062278901.669.597.camel@haggis> <16209.14949.507000.384083@gargle.gargle.HOWL>

* 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