From: Andres Freund <andres@anarazel.de>
To: pgsql-performance@postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Mladen Gogala <mladen.gogala@vmsinfo.com>
Subject: Re: anti-join chosen even when slower than old plan
Date: Thu, 11 Nov 2010 21:11:04 +0100
Message-ID: <201011112111.05511.andres@anarazel.de> (raw)
In-Reply-To: <19088.1289501929@sss.pgh.pa.us>
References: <4CD9663502000025000374B1@gw.wicourts.gov>
<18450.1289499783@sss.pgh.pa.us>
<19088.1289501929@sss.pgh.pa.us>
On Thursday 11 November 2010 19:58:49 Tom Lane wrote:
> 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.
As dicussed in another thread some time ago another possibility is to probe
how well the data i cached using mincore() or similar...
While it presents problem with cache ramp-up it quite cool for other use-cases
(like this one).
Andre
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andres@anarazel.de, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, Kevin.Grittner@wicourts.gov, mladen.gogala@vmsinfo.com
Subject: Re: anti-join chosen even when slower than old plan
In-Reply-To: <201011112111.05511.andres@anarazel.de>
* 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