To: Jesper Krogh <jesper@krogh.cc>
cc: pgsql-performance@postgresql.org
Subject: Re: planner costs in "warm cache" tests 
In-reply-to: <4C02A342.40801@krogh.cc> 
References: <4C02A342.40801@krogh.cc>
Comments: In-reply-to Jesper Krogh <jesper@krogh.cc>
	message dated "Sun, 30 May 2010 19:41:22 +0200"
Date: Sun, 30 May 2010 14:34:51 -0400
Message-ID: <14644.1275244491@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Jesper Krogh <jesper@krogh.cc> writes:
> testdb=# set seq_page_cost = 0.00001;
> SET
> testdb=# set random_page_cost = 0.00001;
> SET

Well, hmm, I really doubt that that represents reality either.  A page
access is by no means "free" even when the page is already in cache.
I don't recall anyone suggesting that you set these numbers to less
than perhaps 0.01.

In the case at hand, the problem is that the planner is preferring using
an indexscan to an after-the-fact sort to obtain the specified result
ordering.  Making page fetches look too cheap definitely plays into
that.  There may also be a statistical problem, if the location of the
desired records isn't independent of the accession_number ordering, but
you're not doing yourself any favors by pushing the planner cost
parameters several orders of magnitude outside the design envelope.

			regards, tom lane