DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=WFkTjG1Nzv1Q6BreeadWhrwQjFCtkZcKxPZkOj50IDVXeJYeMkQF7QNlQH53GPPDCTgxXwnSzF/CSO+/xY5o/R6EwzFqR6OGrJjL0knzCnfclEobo1QPJhf3y4klhBJebY2J9eQwaN8IkSMqO1YJnGtQdRQZbyP9VhcPXG8YvUA=;
Message-ID: <926836.80178.qm@web39707.mail.mud.yahoo.com>
Date: Thu, 11 Nov 2010 09:05:56 -0800 (PST)
From: Bob Lunney <bob_lunney@yahoo.com>
Subject: Re: anti-join chosen even when slower than old plan
To: Kenneth Marshall <ktm@rice.edu>, mladen.gogala@vmsinfo.com
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: <4CDBFA9E.5030007@vmsinfo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

=0A--- On Thu, 11/11/10, Mladen Gogala <mladen.gogala@vmsinfo.com> wrote:=
=0A=0A> From: Mladen Gogala <mladen.gogala@vmsinfo.com>=0A> Subject: Re: [P=
ERFORM] anti-join chosen even when slower than old plan=0A> To: "Kenneth Ma=
rshall" <ktm@rice.edu>=0A> Cc: "Robert Haas" <robertmhaas@gmail.com>, "Tom =
Lane" <tgl@sss.pgh.pa.us>, "Kevin Grittner" <Kevin.Grittner@wicourts.gov>, =
"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>=0A> D=
ate: Thursday, November 11, 2010, 9:15 AM=0A> Kenneth Marshall wrote:=0A> >=
 I agree with the goal of avoiding the need for a GUC.=0A> This needs to=0A=
> > be as automatic as possible. One idea I had had was=0A> computing a val=
ue=0A> > for the amount of cache data in the system by keeping=0A> a sum or=
 a=0A> > weighted sum of the table usage in the system. Smaller=0A> tables =
and=0A> > indexes would contribute a smaller amount to the=0A> total, while=
 larger=0A> > indexes and tables would contribute a larger amount.=0A> Then=
 by comparing=0A> > this running total to the effective_cache_size, set=0A>=
 the random and=0A> > sequential costs for a query. This would allow the=0A=
> case of many 4MB=0A> > tables to favor disk I/O more than memory I/O. The=
=0A> weighting could=0A> > be a function of simultaneous users of the table=
. I=0A> know this is a=0A> > bit of hand-waving but some sort of dynamic fe=
edback=0A> needs to be=0A> > provided to the planning process as system use=
=0A> increases.=0A> > =0A> > Regards,=0A> > Ken=0A> > =0A> >=A0=A0=A0=0A> K=
enneth, you seem to be only concerned with the accuracy of=0A> the planning=
 process, not with the plan stability. As a DBA=0A> who has to monitor real=
 world applications, I find things=0A> like an execution plan changing with=
 the use of the system=0A> to be my worst nightmare. The part where you say=
 that "this=0A> needs to be as automatic as possible" probably means that I=
=0A> will not be able to do anything about it, if the optimizer,=0A> by any=
 chance, doesn't get it right. That looks to me like=0A> an entirely wrong =
way to go.=0A> When application developer tunes the SQL both him and me=0A>=
 expect that SQL to always perform that way, not to change=0A> the executio=
n plan because the system is utilized more than=0A> it was 1 hour ago. Nobo=
dy seems to have taken my suggestion=0A> about having a parameter=0A> which=
 would simply "invent" the percentage out of thin air=0A> seriously, becaus=
e it's obviously not accurate.=0A> However, the planner accuracy is not the=
 only concern.=0A> Running applications on the system usually requires plan=
=0A> stability. Means of=0A> external control of the execution plan, DBA kn=
obs and=0A> buttons that can be turned and pushed to produce the desired=0A=
> plan are also very much desired.=0A> =0A> -- Mladen Gogala Sr. Oracle DBA=
=0A> 1500 Broadway=0A> New York, NY 10036=0A> (212) 329-5251=0A> www.vmsinf=
o.com =0A> =0A=0AMladen,=0A=0ABeen there, done that with Oracle for more ye=
ars than I care to remember or admit.  Having the necessary knobs was both =
daunting and a godsend, depending on if you could find the right one(s) to =
frob during production use, and you turned them the right way and amount.  =
I personally find having less knobbage with PostgreSQL to be a huge benefit=
 over Oracle.  In that spirit, I offer the following suggestion: (Ken's ori=
ginal suggestion inspired me, so if I misunderstand it, Ken, please correct=
 me.)=0A=0AWhat if the code that managed the shared buffer cache kept track=
 of how many buffers were in the cache for each table and index?  Then the =
optimizer could know the ratio of cached to non-cached table of index buffe=
rs (how many pages are in PG's buffer cache vs. the total number of pages r=
equired for the entire table, assuming autovacuum is working well)  and pla=
n accordingly.  It would even be possible to skew the estimate based on the=
 ratio of shared_buffers to effective_cache_size.  The optimizer could then=
 dynamically aadjust the random and sequential costs per query prior to pla=
nning, with (hopefully) plans optimized to the current condition of the ser=
ver and host caches just prior to execution.=0A=0AThere are lots of assumpt=
ions here, the primary ones being the shared buffer cache's state doesn't c=
hange significantly between the start of planning and actual execution time=
, and the host is dedicated to running the database and nothing else that w=
ould trash the host's file system cache.  I admit that I haven't looked at =
the code for this yet, so I don't know if I'm on to something or off in the=
 weeds.=0A=0ARegards,=0A=0ABob Lunney=0A=0A=0A=0A=0A