Обсуждение: Getting query plan alternatives from query planner?

Поиск
Список
Период
Сортировка

Getting query plan alternatives from query planner?

От
Stefan Keller
Дата:
Hi,

I'd like to know from the query planner which query plan alternatives
have been generated and rejected. Is this possible?

--Stefan


Re: Getting query plan alternatives from query planner?

От
Tom Lane
Дата:
Stefan Keller <sfkeller@gmail.com> writes:
> I'd like to know from the query planner which query plan alternatives
> have been generated and rejected. Is this possible?

No, not really.  People have occasionally hacked the planner to print
rejected paths before they're discarded, but there's no convenient way
to do anything except send the data to the postmaster log, which isn't
all that convenient.  A bigger problem is that people who are asking
for this typically imagine that the planner generates complete plans
before rejecting them; which it does not.  Path alternatives are rejected
whenever possible before moving up to the next join level, so that what
we have rejected is actually just a plan fragment in most cases.

            regards, tom lane


Re: Getting query plan alternatives from query planner?

От
Stefan Keller
Дата:
Hi Tom

You wrote:
> Path alternatives are rejected
> whenever possible before moving up to the next join level, so that what
> we have rejected is actually just a plan fragment in most cases.

Thanks for the quick answer. This sounds like a fair implementation decision.

Background for asking this is of course, that one want's 1. to
understand and 2. influence the optimizer in cases where one thinks
that the planner is wrong :-).

So, the bottom line is
1. that PostgreSQL doesn't offer no means to understand the planner
except EXPLAIN-ing the chosen plan?
2. and there's no road map to introduce planner hinting (like in
EnterpriseDB or Ora)?

Regards, Stefan

2014-03-20 18:08 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
> Stefan Keller <sfkeller@gmail.com> writes:
>> I'd like to know from the query planner which query plan alternatives
>> have been generated and rejected. Is this possible?
>
> No, not really.  People have occasionally hacked the planner to print
> rejected paths before they're discarded, but there's no convenient way
> to do anything except send the data to the postmaster log, which isn't
> all that convenient.  A bigger problem is that people who are asking
> for this typically imagine that the planner generates complete plans
> before rejecting them; which it does not.  Path alternatives are rejected
> whenever possible before moving up to the next join level, so that what
> we have rejected is actually just a plan fragment in most cases.
>
>                         regards, tom lane


Re: Getting query plan alternatives from query planner?

От
Atri Sharma
Дата:



On Fri, Mar 21, 2014 at 1:07 PM, Stefan Keller <sfkeller@gmail.com> wrote:
Hi Tom

You wrote:
> Path alternatives are rejected
> whenever possible before moving up to the next join level, so that what
> we have rejected is actually just a plan fragment in most cases.

Thanks for the quick answer. This sounds like a fair implementation decision.

Background for asking this is of course, that one want's 1. to
understand and 2. influence the optimizer in cases where one thinks
that the planner is wrong :-).

So, the bottom line is
1. that PostgreSQL doesn't offer no means to understand the planner
except EXPLAIN-ing the chosen plan?
2. and there's no road map to introduce planner hinting (like in
EnterpriseDB or Ora)?


We recently had some discussion for planner hints. There is no plan for having planner hints ATM. However, we are looking at ways at which we can improve the query planner for some cases where it makes statistical bad estimations and gives bad plans.

Regards,

Atri

--
Regards,
 
Atri
l'apprenant

Re: Getting query plan alternatives from query planner?

От
Craig James
Дата:
There have been many discussions about adding hints to Postgres over the years. All have been firmly rejected by the Postgres developers, with well-argued reasons.  Search the archives to learn more about this topic.

On the other hand, Postgres does have hints.  They're just called settings. You can disable certain types of joins with SET commands. On top of that, there are "fences" that the optimizer can't cross that you can use to force the optimizer to consider certain sub-queries separately (e.g. "offset 0" on a subquery).

Craig


On Fri, Mar 21, 2014 at 12:51 AM, Atri Sharma <atri.jiit@gmail.com> wrote:



On Fri, Mar 21, 2014 at 1:07 PM, Stefan Keller <sfkeller@gmail.com> wrote:
Hi Tom

You wrote:
> Path alternatives are rejected
> whenever possible before moving up to the next join level, so that what
> we have rejected is actually just a plan fragment in most cases.

Thanks for the quick answer. This sounds like a fair implementation decision.

Background for asking this is of course, that one want's 1. to
understand and 2. influence the optimizer in cases where one thinks
that the planner is wrong :-).

So, the bottom line is
1. that PostgreSQL doesn't offer no means to understand the planner
except EXPLAIN-ing the chosen plan?
2. and there's no road map to introduce planner hinting (like in
EnterpriseDB or Ora)?


We recently had some discussion for planner hints. There is no plan for having planner hints ATM. However, we are looking at ways at which we can improve the query planner for some cases where it makes statistical bad estimations and gives bad plans.

Regards,

Atri

--
Regards,
 
Atri
l'apprenant

Re: Getting query plan alternatives from query planner?

От
Tom Lane
Дата:
Craig James <cjames@emolecules.com> writes:
> There have been many discussions about adding hints to Postgres over the
> years. All have been firmly rejected by the Postgres developers, with
> well-argued reasons.  Search the archives to learn more about this topic.

To clarify: there are good reasons not to like what Oracle calls hints.
On the other hand, the concept of hints that tell the planner what
selectivity or rowcount to expect (as opposed to trying to control the
plan directly) has met with generally more positive reviews.  There's
no specific design yet, and certainly no implementation roadmap, but
I'd not be surprised if we get something like that a few years down
the road.

            regards, tom lane


Re: Getting query plan alternatives from query planner?

От
Shaun Thomas
Дата:
On 03/21/2014 08:34 AM, Craig James wrote:

> There have been many discussions about adding hints to Postgres over the
> years. All have been firmly rejected by the Postgres developers, with
> well-argued reasons.  Search the archives to learn more about this topic.

While that's true, and I agree with the sentiment, it could also be
argued that what we have now is actually worse than hints.

I've been bitten several times by wrong query plans. The cause is
usually due to bad correlation estimates or edge-cases due to incomplete
stats. Aside from cranking default_statistics_target up to 10,000, these
issues tend to get solved through optimization fences. Reorganize a
query into a CTE, or use the (gross) OFFSET 0 trick. How are these
nothing other than unofficial hints?

Well... they're worse, really. Hints can be deprecated, disabled in
configs, or ignored in extreme cases. Optimization fences are truly
forever. Unless of course they're removed. In which case, a bunch of
queries that exploited them will suddenly perform a whole lot worse,
causing organizations to delay upgrading PostgreSQL.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email


Re: Getting query plan alternatives from query planner?

От
Kevin Grittner
Дата:
Shaun Thomas <sthomas@optionshouse.com> wrote:

> these issues tend to get solved through optimization fences.
> Reorganize a query into a CTE, or use the (gross) OFFSET 0 trick.
> How are these nothing other than unofficial hints?

Yeah, the cognitive dissonance levels get pretty high around this
issue.  Some of the same people who argue strenuously against
adding hints about what plan should be chosen also argue against
having clearly equivalent queries optimize to the same plan because
they find the fact that they don't useful for coercing a decent
plan sometimes.  That amounts to a hint, but obscure and
undocumented.  (The OP may be wondering what this "OFFSET 0 trick"
is, and how he can use it.)

> Well... they're worse, really. Hints can be deprecated, disabled
> in configs, or ignored in extreme cases. Optimization fences are
> truly forever.

+1

With explicit, documented hints, one could search for hints of a
particular type should the optimizer improve to the point where
they are no longer needed.  It is harder to do that with subtle
differences in syntax choice.  Figuring out which CTEs or LIMITs
were chosen because they caused optimization barriers rather than
for their semantic merit takes some effort.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



Re: Getting query plan alternatives from query planner?

От
Eric Schwarzenbach
Дата:
I don't know how anyone else feels about this, as I don't think I've
seen this ever suggested, but my ideal would be a way to configure the
database to recognize specific queries and to have a way of influencing
its plan choice for that query. I'm intentionally wording that last part
vaguely, as I'm not sure what would be best or practical there. Ideally,
perhaps, would be to be able to store a particular plan for that query
and have it always use it.

I don't want either hints OR fence distortions in my application code,
which might have to work with different versions of PostgreSQL with
different optimization characteristics, different servers with different
performance characteristics, or even different database products
entirely. A solution to a server-side problem should live on the server
not on the client. That's why I've always preferred PostgeSQL's server
settings for tweaking the optimizer to the hints offered by other products.

On 4/14/2014 10:39 AM, Kevin Grittner wrote:
> Shaun Thomas <sthomas@optionshouse.com> wrote:
>
>> these issues tend to get solved through optimization fences.
>> Reorganize a query into a CTE, or use the (gross) OFFSET 0 trick.
>> How are these nothing other than unofficial hints?
> Yeah, the cognitive dissonance levels get pretty high around this
> issue.  Some of the same people who argue strenuously against
> adding hints about what plan should be chosen also argue against
> having clearly equivalent queries optimize to the same plan because
> they find the fact that they don't useful for coercing a decent
> plan sometimes.  That amounts to a hint, but obscure and
> undocumented.  (The OP may be wondering what this "OFFSET 0 trick"
> is, and how he can use it.)
>
>> Well... they're worse, really. Hints can be deprecated, disabled
>> in configs, or ignored in extreme cases. Optimization fences are
>> truly forever.
> +1
>
> With explicit, documented hints, one could search for hints of a
> particular type should the optimizer improve to the point where
> they are no longer needed.  It is harder to do that with subtle
> differences in syntax choice.  Figuring out which CTEs or LIMITs
> were chosen because they caused optimization barriers rather than
> for their semantic merit takes some effort.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
>



Re: Getting query plan alternatives from query planner?

От
Craig James
Дата:
Shaun Thomas <sthomas@optionshouse.com> wrote:

these issues tend to get solved through optimization fences.
Reorganize a query into a CTE, or use the (gross) OFFSET 0 trick.
How are these nothing other than unofficial hints?
Yeah, the cognitive dissonance levels get pretty high around this
issue.  Some of the same people who argue strenuously against
adding hints about what plan should be chosen also argue against
having clearly equivalent queries optimize to the same plan because
they find the fact that they don't useful for coercing a decent
plan sometimes.  That amounts to a hint, but obscure and
undocumented.  (The OP may be wondering what this "OFFSET 0 trick"
is, and how he can use it.)

+1. I've said this or something like it at least a half-dozen times. Postgres DOES have hints, they're just obscure, undocumented and hard to use. If a developer chooses to use them, they become embedded in the app and forgotten. They're hard to find because there's nothing explicit in the SQL to look for. You have to know to look for things like "OFFSET" or "SET ...". Five years down the road when the developer is long gone, who's going to know why "... OFFSET 0" was put in the code unless the developer made careful comments?
 
With explicit, documented hints, one could search for hints of a
particular type should the optimizer improve to the point where
they are no longer needed.  It is harder to do that with subtle
differences in syntax choice.  Figuring out which CTEs or LIMITs
were chosen because they caused optimization barriers rather than
for their semantic merit takes some effort.

Exactly.

I'll make a bet here. I'll bet that the majority of large Postgres installations have at least one, probably several, SQL statements that have been "hinted" in some way, either with CTEs or LIMITs, or by using SET to disable a particular query type, and that these "hints" are critical to the system's performance.

The question is not whether to have hints. The question is how to expose hints to users.

Craig

Re: Getting query plan alternatives from query planner?

От
Stefan Keller
Дата:
Hi Craig and Shawn

I fully agree with your argumentation.
Who's the elephant in the room who is reluctant to introduce explicit hints?

-S.


2014-04-14 17:35 GMT+02:00 Craig James <cjames@emolecules.com>:
Shaun Thomas <sthomas@optionshouse.com> wrote:

these issues tend to get solved through optimization fences.
Reorganize a query into a CTE, or use the (gross) OFFSET 0 trick.
How are these nothing other than unofficial hints?
Yeah, the cognitive dissonance levels get pretty high around this
issue.  Some of the same people who argue strenuously against
adding hints about what plan should be chosen also argue against
having clearly equivalent queries optimize to the same plan because
they find the fact that they don't useful for coercing a decent
plan sometimes.  That amounts to a hint, but obscure and
undocumented.  (The OP may be wondering what this "OFFSET 0 trick"
is, and how he can use it.)

+1. I've said this or something like it at least a half-dozen times. Postgres DOES have hints, they're just obscure, undocumented and hard to use. If a developer chooses to use them, they become embedded in the app and forgotten. They're hard to find because there's nothing explicit in the SQL to look for. You have to know to look for things like "OFFSET" or "SET ...". Five years down the road when the developer is long gone, who's going to know why "... OFFSET 0" was put in the code unless the developer made careful comments?
 
With explicit, documented hints, one could search for hints of a
particular type should the optimizer improve to the point where
they are no longer needed.  It is harder to do that with subtle
differences in syntax choice.  Figuring out which CTEs or LIMITs
were chosen because they caused optimization barriers rather than
for their semantic merit takes some effort.

Exactly.

I'll make a bet here. I'll bet that the majority of large Postgres installations have at least one, probably several, SQL statements that have been "hinted" in some way, either with CTEs or LIMITs, or by using SET to disable a particular query type, and that these "hints" are critical to the system's performance.

The question is not whether to have hints. The question is how to expose hints to users.

Craig


Re: Getting query plan alternatives from query planner?

От
Heikki Linnakangas
Дата:
On 04/14/2014 09:36 PM, Stefan Keller wrote:
> Who's the elephant in the room who is reluctant to introduce explicit hints?

Please read some of the previous discussions on this. Like this, in this
very same thread:

http://www.postgresql.org/message-id/15381.1395410811@sss.pgh.pa.us

I'd like to have explicit hints, *of the kind explained in that
message*. Hints that tell the planner what the data distribution is
like. Hints to override statistics and heuristics used by the planner.

- Heikki