From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Mladen Gogala <mladen.gogala@vmsinfo.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: anti-join chosen even when slower than old plan
Date: Thu, 11 Nov 2010 14:35:56 -0500
Message-ID: <19796.1289504156@sss.pgh.pa.us> (raw)
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>
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
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: 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: <19796.1289504156@sss.pgh.pa.us>
* 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