From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rob Nagler <nagler@bivio.biz>
Cc: pgsql-performance@postgresql.org
Subject: Re: How to force Nested Loop plan?
Date: Sat, 30 Aug 2003 18:10:41 -0400
Message-ID: <4040.1062281441@sss.pgh.pa.us> (raw)
In-Reply-To: <16209.3105.94000.204936@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>
Rob Nagler <nagler@bivio.biz> writes:
> What I'm not sure is why does it decide to switch modes so "early",
> i.e., at about 5% of the table size or less?
Given the default cost parameters and cost models, that's the correct
place to switch. Since the estimate evidently doesn't match reality
for your case, you might want to play with the parameters. Reducing
random_page_cost would be the first thing I'd try. Some people think
that increasing effective_cache_size is a good idea too, though I feel
that that has only marginal impact on the planner's choices.
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.
Another thing I'd be interested to know about is how closely the
physical order of the table entries correlates with min_date_time.
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 ...
regards, tom lane
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: tgl@sss.pgh.pa.us, nagler@bivio.biz
Subject: Re: How to force Nested Loop plan?
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>
* 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