Re: anti-join chosen even when slower than old plan

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: anti-join chosen even when slower than old plan
Date: 2010-11-11 18:58:49
Message-ID: 19088.1289501929@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Robert Haas 2010-11-11 19:02:14 Re: anti-join chosen even when slower than old plan
Previous Message Alex Hunsaker 2010-11-11 18:54:37 Re: CREATE INDEX as bottleneck