pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
To: pgsql-performance@postgresql.org
Subject: Re: Hash or merge join instead of inner loop
Date: Tue, 10 Jun 2003 14:26:17 +0530
Message-ID: <3EE5EA89.19260.8C8B669@localhost> (raw)
In-Reply-To: <274.1055225711@sss.pgh.pa.us>
References: <20030609204009.GJ40542@flake.decibel.org>

On 10 Jun 2003 at 2:15, Tom Lane wrote:

> There's been some discussion about that before; you could check the
> archives (now that they're up again ;-)).  I believe that the planner
> overestimates the cost of a nestloop with inner indexscan, because it
> costs the indexscans as though each one is an independent ab-initio
> index search.  In reality, most of the upper btree levels will no doubt
> stay in memory during such a query, and so this estimate charges many
> more reads than really occur.  Fixing this is on the todo list, but no
> one's got to it yet.  (It's not clear to me how to put the consideration
> into the planner's cost algorithms in a clean way.)

Just being naïve here, but if planner and executor account for shared 
buffers+effective OS cache, even a boolean choice could be a start.

Say a query needs 100MB of data according to estimates so if shared 
buffers+effective OS cache covers that, we can lower the cost.

May be we should have two config. parameters for tuple cost? Disk read tuple 
cost and memory read tuple cost. Later being 1/10th of former?

Bye
 Shridhar

--
All new:	Parts not interchangeable with previous model.




view thread (5+ messages)  latest in thread

Message-ID: <3EE5EA89.19260.8C8B669@localhost>
Permalink:  ../3EE5EA89.19260.8C8B669@localhost/
Also on:    postgresql.org/message-id/3EE5EA89.19260.8C8B669@localhost

 · 

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: shridhar_daithankar@persistent.co.in
  Subject: Re: Hash or merge join instead of inner loop
  In-Reply-To: <3EE5EA89.19260.8C8B669@localhost>
  References: <20030609204009.GJ40542@flake.decibel.org> <3EE5EA89.19260.8C8B669@localhost>

* 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