To: Robert Haas <robertmhaas@gmail.com>
cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	Mladen Gogala <mladen.gogala@vmsinfo.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: anti-join chosen even when slower than old plan 
In-reply-to: <AANLkTi=dnDwLEyxienHs7ZGP1k18YQzaQMPY7KP-hjMT@mail.gmail.com> 
References: <4CD9663502000025000374B1@gw.wicourts.gov>
	<20961.1289347680@sss.pgh.pa.us>
	<4CDA62C60200002500037512@gw.wicourts.gov>
	<AANLkTi=eNXKEBo5dXJDd4+aHhfmkibH++fqjj12+DA3o@mail.gmail.com>
	<4CDACBBF020000250003755B@gw.wicourts.gov>
	<4CDB7BAA.7020506@vmsinfo.com>
	<4CDBB0A40200002500037571@gw.wicourts.gov>
	<AANLkTi=dzhRTTNm1LkyNCAC5+PWbp1jWtKFh4T1Bu4nj@mail.gmail.com>
	<18450.1289499783@sss.pgh.pa.us> <19088.1289501929@sss.pgh.pa.us>
	<AANLkTi=dnDwLEyxienHs7ZGP1k18YQzaQMPY7KP-hjMT@mail.gmail.com>
Comments: In-reply-to Robert Haas <robertmhaas@gmail.com>
	message dated "Thu, 11 Nov 2010 14:05:41 -0500"
Date: Thu, 11 Nov 2010 14:35:56 -0500
Message-ID: <19796.1289504156@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Robert Haas <robertmhaas@gmail.com> writes:
> Yeah.  For Kevin's case, it seems like we want the caching percentage
> to vary not so much based on which table we're hitting at the moment
> but on how much of it we're actually reading.

Well, we could certainly take the expected number of pages to read and
compare that to effective_cache_size.  The thing that's missing in that
equation is how much other stuff is competing for cache space.  I've
tried to avoid having the planner need to know the total size of the
database cluster, but it's kind of hard to avoid that if you want to
model this honestly.

Would it be at all workable to have an estimate that so many megs of a
table are in cache (independently of any other table), and then we could
scale the cost based on the expected number of pages to read versus that
number?  The trick here is that DBAs really aren't going to want to set
such a per-table number (at least, most of the time) so we need a
formula to get to a default estimate for that number based on some simple
system-wide parameters.  I'm not sure if that's any easier.

BTW, it seems that all these variants have an implicit assumption that
if you're reading a small part of the table it's probably part of the
working set; which is an assumption that could be 100% wrong.  I don't
see a way around it without trying to characterize the data access at
an unworkably fine level, though.

			regards, tom lane