To: Greg Stark <gsstark@mit.edu>
Cc: Christoph Haller <ch@rodos.fzk.de>,
	paul@tmsl.demon.co.uk (Paul Thomas), pgsql-sql@postgresql.org
Subject: Re: Index not used - now me 
In-reply-to: <878yjcntra.fsf@stark.xeocode.com> 
References: <200402091502.QAA19246@rodos> <878yjcntra.fsf@stark.xeocode.com>
Comments: In-reply-to Greg Stark <gsstark@mit.edu>
	message dated "09 Feb 2004 10:43:21 -0500"
Date: Mon, 09 Feb 2004 11:38:42 -0500
Message-ID: <20551.1076344722@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Greg Stark <gsstark@mit.edu> writes:
> You could also try lowering random_page_cost. Some people find as low as 1.2
> or so to be useful, but that would almost certainly be lying to postgres about
> the costs of random access and would cause it to use index scans aggressively
> even when they're not faster.

If the database is small enough to mostly fit in kernel disk cache, then
a very low random_page_cost is telling the truth, not lying.  One might
also think about raising the various CPU_xxx cost parameters, since CPU
effort becomes proportionally much more significant when "I/O" only
requires a trip to kernel buffers.

However, Christoph's example looks to me like a classic case of testing
a toy table and assuming the results will extrapolate to production-size
cases.  This table is small enough that it hardly matters which method
the planner chooses.  I'd counsel being very wary about adjusting the
cost settings based on only this example.

			regards, tom lane