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: <18450.1289499783@sss.pgh.pa.us> 
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>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Thu, 11 Nov 2010 13:23:03 -0500"
Date: Thu, 11 Nov 2010 13:58:49 -0500
Message-ID: <19088.1289501929@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

I wrote:
> I do think that something based around a settable-per-table caching
> percentage might be a reasonable way to proceed.

BTW ... on reflection it seems that this would *not* solve the use-case
Kevin described at the start of this thread.  What he's got AIUI is some
large tables whose recent entries are well-cached, and a lot of queries
that tend to hit that well-cached portion, plus a few queries that hit
the whole table and so see largely-not-cached behavior.  We can't
represent that very well with a caching knob at the table level.  Either
a high or a low setting will be wrong for one set of queries or the
other.

It might work all right if he were to partition the table and then have
a different caching value attached to the currently-latest partition,
but that doesn't sound exactly maintenance-free either.  Also, that only
works with the current statically-planned approach to partitioned
tables.  I think where we're trying to go with partitioning is that
the planner doesn't consider the individual partitions, but the executor
just hits the right one at runtime --- so cost modifiers attached to
individual partitions aren't going to work in that environment.

The most practical solution for his case still seems to be to twiddle
some GUC or other locally in the maintenance scripts that do the
full-table-scan queries.  Unfortunately we don't have an equivalent
of per-session SET (much less SET LOCAL) for per-relation attributes.
Not sure if we want to go there.

			regards, tom lane