From: Craig A. James <cjames@modgraph-usa.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Overriding the optimizer
Date: Thu, 15 Dec 2005 18:16:08 -0800
Message-ID: <43A22368.8060307@modgraph-usa.com> (raw)
In-Reply-To: <18537.1134692629@sss.pgh.pa.us>
References: <200512141603.52583.blargity@gmail.com>
<7484.1134600423@sss.pgh.pa.us>
<1134629567.14248.27.camel@firebolt>
<200512150148.16125.blargity@gmail.com>
<1134644526.14248.60.camel@firebolt>
<43A1C40D.8040006@paradise.net.nz>
<43A1F6DB.3080805@modgraph-usa.com>
<18537.1134692629@sss.pgh.pa.us>
Tom,
>>I see this over and over. Tweak the parameters to "force" a certain
>>plan, because there's no formal way for a developer to say, "I know
>>the best plan."
>
> I think you've misunderstood those conversations entirely. The point
> is not to force the planner into a certain plan, it is to explore what's
> going on with a view to understanding why the planner isn't making a
> good choice, and thence hopefully improve the planner in future.
No, I understood the conversations very clearly. But no matter how clever the optimizer, it simply can't compete with a developer who has knowledge that Postgres *can't* have. The example of a user-written function is obvious.
>>There isn't a database in the world that is as smart as a developer,
>
> People who are convinced they are smarter than the machine are often
> wrong ;-).
Often, but not always -- as I noted in my original posting. And when the developer is smarter than Postgres, and Postgres makes the wrong choice, what is the developer supposed to do? This isn't academic -- the wrong plans Postgres makes can be *catastrophic*, e.g. turning a 3-second query into a three-hour query.
How about this: Instead of arguing in the abstract, tell me in concrete terms how you would address the very specific example I gave, where myfunc() is a user-written function. To make it a little more challenging, try this: myfunc() can behave very differently depending on the parameters, and sometimes (but not always), the application knows how it will behave and could suggest a good execution plan.
(And before anyone suggests that I rewrite myfunc(), I should explain that it's in the class of NP-complete problems. The function is inherently hard and can't be made faster or more predictable.)
The example I raised in a previous thread, of irregular usage, is the same: I have a particular query that I *always* want to be fast even if it's only used rarely, but the system swaps its tables out of the file-system cache, based on "low usage", even though the "high usage" queries are low priority. How can Postgres know such things when there's no way for me to tell it?
The answers from the Postgres community were essentially, "Postgres is smarter than you, let it do its job." Unfortunately, this response completely ignores the reality: Postgres is NOT doing its job, and can't, because it doesn't have enough information.
Craig
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: cjames@modgraph-usa.com, tgl@sss.pgh.pa.us
Subject: Re: Overriding the optimizer
In-Reply-To: <43A22368.8060307@modgraph-usa.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