From: Mladen Gogala <mladen.gogala@vmsinfo.com>
To: Kenneth Marshall <ktm@rice.edu>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
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 09:15:58 -0500
Message-ID: <4CDBFA9E.5030007@vmsinfo.com> (raw)
In-Reply-To: <20101111135122.GB14016@aart.is.rice.edu>
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>
<12595.1289430435@sss.pgh.pa.us>
<AANLkTin2KxVit+eKb3Dc45ZitD5YACMvt9oVGe6K1vTP@mail.gmail.com>
<20101111135122.GB14016@aart.is.rice.edu>
Kenneth Marshall wrote:
> I agree with the goal of avoiding the need for a GUC. This needs to
> be as automatic as possible. One idea I had had was computing a value
> for the amount of cache data in the system by keeping a sum or a
> weighted sum of the table usage in the system. Smaller tables and
> indexes would contribute a smaller amount to the total, while larger
> indexes and tables would contribute a larger amount. Then by comparing
> this running total to the effective_cache_size, set the random and
> sequential costs for a query. This would allow the case of many 4MB
> tables to favor disk I/O more than memory I/O. The weighting could
> be a function of simultaneous users of the table. I know this is a
> bit of hand-waving but some sort of dynamic feedback needs to be
> provided to the planning process as system use increases.
>
> Regards,
> Ken
>
>
Kenneth, you seem to be only concerned with the accuracy of the planning
process, not with the plan stability. As a DBA who has to monitor real
world applications, I find things like an execution plan changing with
the use of the system to be my worst nightmare. The part where you say
that "this needs to be as automatic as possible" probably means that I
will not be able to do anything about it, if the optimizer, by any
chance, doesn't get it right. That looks to me like an entirely wrong
way to go.
When application developer tunes the SQL both him and me expect that SQL
to always perform that way, not to change the execution plan because the
system is utilized more than it was 1 hour ago. Nobody seems to have
taken my suggestion about having a parameter
which would simply "invent" the percentage out of thin air seriously,
because it's obviously not accurate.
However, the planner accuracy is not the only concern. Running
applications on the system usually requires plan stability. Means of
external control of the execution plan, DBA knobs and buttons that can
be turned and pushed to produce the desired plan are also very much desired.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com
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: mladen.gogala@vmsinfo.com, ktm@rice.edu, robertmhaas@gmail.com, tgl@sss.pgh.pa.us, Kevin.Grittner@wicourts.gov
Subject: Re: anti-join chosen even when slower than old plan
In-Reply-To: <4CDBFA9E.5030007@vmsinfo.com>
* 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