pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rob Nagler <nagler@bivio.biz>
To: pgsql-performance@postgresql.org
Subject: Re: How to force Nested Loop plan?
Date: Sat, 30 Aug 2003 18:08:32 -0600
Message-ID: <16209.15488.141000.729032@gargle.gargle.HOWL> (raw)
In-Reply-To: <4040.1062281441@sss.pgh.pa.us>
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>
	<4040.1062281441@sss.pgh.pa.us>

Tom Lane writes:
> Keep in mind though that you seem to be experimenting with a
> fully-cached database; you may find that the planner's beliefs more
> nearly approach reality when actual I/O has to occur.

My hope is that the entire database should fit in memory.  This may
not be in the case right now with only 1GB, but it should be close.
The pgsql/data/base/NNN directory is about 1.5GB on production.  I'm
pretty sure with constant vacuuming, we could keep that size down.
A pgdump is about 60MB now, growing at about .5MB a day.

> Another thing I'd be interested to know about is how closely the
> physical order of the table entries correlates with min_date_time.

Probably "pretty close".  The primary key of aa_t is (bb_id,
server_id), and bb_id is a sequence.  aa_t is updated heavily on
production, but these tests are on a fresh import so vacuuming and
index order is not a factor.  We do a reload every now and then to
improve performance on production.  min_date_time is highly correlated
with bb_id, because both are increasing constantly.  server_id is one
of 16 values.

> A high correlation reduces the actual cost of the indexscan (since
> visiting the rows in index order becomes less of a random-access
> proposition).  We are aware that the planner doesn't model this effect
> very well at present ...

Oracle's optimizer is lacking here, too.  The best optimizer I've seen
was at Tandem, and even then hints were required.

Are there plans for explicit hints to the planner?

Thanks,
Rob





view thread (13+ messages)  latest in thread

Message-ID: <16209.15488.141000.729032@gargle.gargle.HOWL>
Permalink:  ../16209.15488.141000.729032@gargle.gargle.HOWL/
Also on:    postgresql.org/message-id/16209.15488.141000.729032@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.15488.141000.729032@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> <4040.1062281441@sss.pgh.pa.us> <16209.15488.141000.729032@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