pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedGetting query plan alternatives from query planner?
12+ messages / 8 participants
[nested] [flat]
* Getting query plan alternatives from query planner?
@ 2014-03-20 16:53 Stefan Keller <sfkeller@gmail.com>
2014-03-20 17:08 ` Re: Getting query plan alternatives from query planner? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Stefan Keller @ 2014-03-20 16:53 UTC (permalink / raw)
To: pgsql-performance
Hi,
I'd like to know from the query planner which query plan alternatives
have been generated and rejected. Is this possible?
--Stefan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Getting query plan alternatives from query planner?
2014-03-20 16:53 Getting query plan alternatives from query planner? Stefan Keller <sfkeller@gmail.com>
@ 2014-03-20 17:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-03-21 07:37 ` Re: Getting query plan alternatives from query planner? Stefan Keller <sfkeller@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2014-03-20 17:08 UTC (permalink / raw)
To: Stefan Keller <sfkeller@gmail.com>; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Getting query plan alternatives from query planner?
2014-03-20 16:53 Getting query plan alternatives from query planner? Stefan Keller <sfkeller@gmail.com>
2014-03-20 17:08 ` Re: Getting query plan alternatives from query planner? Tom Lane <tgl@sss.pgh.pa.us>
@ 2014-03-21 07:37 ` Stefan Keller <sfkeller@gmail.com>
2014-03-21 07:51 ` Re: Getting query plan alternatives from query planner? Atri Sharma <atri.jiit@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Stefan Keller @ 2014-03-21 07:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Getting query plan alternatives from query planner?
2014-03-20 16:53 Getting query plan alternatives from query planner? Stefan Keller <sfkeller@gmail.com>
2014-03-20 17:08 ` Re: Getting query plan alternatives from query planner? Tom Lane <tgl@sss.pgh.pa.us>
2014-03-21 07:37 ` Re: Getting query plan alternatives from query planner? Stefan Keller <sfkeller@gmail.com>
@ 2014-03-21 07:51 ` Atri Sharma <atri.jiit@gmail.com>
2014-03-21 13:34 ` Re: Getting query plan alternatives from query planner? Craig James <cjames@emolecules.com>
0 siblings, 1 reply; 12+ messages in thread
From: Atri Sharma @ 2014-03-21 07:51 UTC (permalink / raw)
To: Stefan Keller <sfkeller@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
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*
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Getting query plan alternatives from query planner?
2014-03-20 16:53 Getting query plan alternatives from query planner? Stefan Keller <sfkeller@gmail.com>
2014-03-20 17:08 ` Re: Getting query plan alternatives from query planner? Tom Lane <tgl@sss.pgh.pa.us>
2014-03-21 07:37 ` Re: Getting query plan alternatives from query planner? Stefan Keller <sfkeller@gmail.com>
2014-03-21 07:51 ` Re: Getting query plan alternatives from query planner? Atri Sharma <atri.jiit@gmail.com>
@ 2014-03-21 13:34 ` Craig James <cjames@emolecules.com>
2014-03-21 14:06 ` Re: Getting query plan alternatives from query planner? Tom Lane <tgl@sss.pgh.pa.us>
2014-03-21 19:43 ` Re: Getting query plan alternatives from query planner? Shaun Thomas <sthomas@optionshouse.com>
0 siblings, 2 replies; 12+ messages in thread
From: Craig James @ 2014-03-21 13:34 UTC (permalink / raw)
To: Atri Sharma <atri.jiit@gmail.com>; +Cc: Stefan Keller <sfkeller@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
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*
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Getting query plan alternatives from query planner?
2014-03-20 16:53 Getting query plan alternatives from query planner? Stefan Keller <sfkeller@gmail.com>
2014-03-20 17:08 ` Re: Getting query plan alternatives from query planner? Tom Lane <tgl@sss.pgh.pa.us>
2014-03-21 07:37 ` Re: Getting query plan alternatives from query planner? Stefan Keller <sfkeller@gmail.com>
2014-03-21 07:51 ` Re: Getting query plan alternatives from query planner? Atri Sharma <atri.jiit@gmail.com>
2014-03-21 13:34 ` Re: Getting query plan alternatives from query planner? Craig James <cjames@emolecules.com>
@ 2014-03-21 14:06 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 12+ messages in thread
From: Tom Lane @ 2014-03-21 14:06 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: Atri Sharma <atri.jiit@gmail.com>; Stefan Keller <sfkeller@gmail.com>; pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Getting query plan alternatives from query planner?
2014-03-20 16:53 Getting query plan alternatives from query planner? Stefan Keller <sfkeller@gmail.com>
2014-03-20 17:08 ` Re: Getting query plan alternatives from query planner? Tom Lane <tgl@sss.pgh.pa.us>
2014-03-21 07:37 ` Re: Getting query plan alternatives from query planner? Stefan Keller <sfkeller@gmail.com>
2014-03-21 07:51 ` Re: Getting query plan alternatives from query planner? Atri Sharma <atri.jiit@gmail.com>
2014-03-21 13:34 ` Re: Getting query plan alternatives from query planner? Craig James <cjames@emolecules.com>
@ 2014-03-21 19:43 ` Shaun Thomas <sthomas@optionshouse.com>
2014-04-14 14:39 ` Re: Getting query plan alternatives from query planner? Kevin Grittner <kgrittn@ymail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Shaun Thomas @ 2014-03-21 19:43 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; Atri Sharma <atri.jiit@gmail.com>; +Cc: Stefan Keller <sfkeller@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Getting query plan alternatives from query planner?
2014-03-20 16:53 Getting query plan alternatives from query planner? Stefan Keller <sfkeller@gmail.com>
2014-03-20 17:08 ` Re: Getting query plan alternatives from query planner? Tom Lane <tgl@sss.pgh.pa.us>
2014-03-21 07:37 ` Re: Getting query plan alternatives from query planner? Stefan Keller <sfkeller@gmail.com>
2014-03-21 07:51 ` Re: Getting query plan alternatives from query planner? Atri Sharma <atri.jiit@gmail.com>
2014-03-21 13:34 ` Re: Getting query plan alternatives from query planner? Craig James <cjames@emolecules.com>
2014-03-21 19:43 ` Re: Getting query plan alternatives from query planner? Shaun Thomas <sthomas@optionshouse.com>
@ 2014-04-14 14:39 ` Kevin Grittner <kgrittn@ymail.com>
2014-04-14 14:59 ` Re: Getting query plan alternatives from query planner? Eric Schwarzenbach <subscriber@blackbrook.org>
0 siblings, 1 reply; 12+ messages in thread
From: Kevin Grittner @ 2014-04-14 14:39 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; Atri Sharma <atri.jiit@gmail.com>; Shaun Thomas <sthomas@optionshouse.com>; +Cc: Stefan Keller <sfkeller@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Getting query plan alternatives from query planner?
2014-03-20 16:53 Getting query plan alternatives from query planner? Stefan Keller <sfkeller@gmail.com>
2014-03-20 17:08 ` Re: Getting query plan alternatives from query planner? Tom Lane <tgl@sss.pgh.pa.us>
2014-03-21 07:37 ` Re: Getting query plan alternatives from query planner? Stefan Keller <sfkeller@gmail.com>
2014-03-21 07:51 ` Re: Getting query plan alternatives from query planner? Atri Sharma <atri.jiit@gmail.com>
2014-03-21 13:34 ` Re: Getting query plan alternatives from query planner? Craig James <cjames@emolecules.com>
2014-03-21 19:43 ` Re: Getting query plan alternatives from query planner? Shaun Thomas <sthomas@optionshouse.com>
2014-04-14 14:39 ` Re: Getting query plan alternatives from query planner? Kevin Grittner <kgrittn@ymail.com>
@ 2014-04-14 14:59 ` Eric Schwarzenbach <subscriber@blackbrook.org>
0 siblings, 0 replies; 12+ messages in thread
From: Eric Schwarzenbach @ 2014-04-14 14:59 UTC (permalink / raw)
To: pgsql-performance
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
>
>
>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Getting query plan alternatives from query planner?
@ 2014-04-14 15:35 Craig James <cjames@emolecules.com>
2014-04-14 18:36 ` Re: Getting query plan alternatives from query planner? Stefan Keller <sfkeller@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Craig James @ 2014-04-14 15:35 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Getting query plan alternatives from query planner?
2014-04-14 15:35 Re: Getting query plan alternatives from query planner? Craig James <cjames@emolecules.com>
@ 2014-04-14 18:36 ` Stefan Keller <sfkeller@gmail.com>
2014-04-14 19:18 ` Re: Getting query plan alternatives from query planner? Heikki Linnakangas <hlinnakangas@vmware.com>
0 siblings, 1 reply; 12+ messages in thread
From: Stefan Keller @ 2014-04-14 18:36 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: pgsql-performance
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
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Getting query plan alternatives from query planner?
2014-04-14 15:35 Re: Getting query plan alternatives from query planner? Craig James <cjames@emolecules.com>
2014-04-14 18:36 ` Re: Getting query plan alternatives from query planner? Stefan Keller <sfkeller@gmail.com>
@ 2014-04-14 19:18 ` Heikki Linnakangas <hlinnakangas@vmware.com>
0 siblings, 0 replies; 12+ messages in thread
From: Heikki Linnakangas @ 2014-04-14 19:18 UTC (permalink / raw)
To: Stefan Keller <sfkeller@gmail.com>; Craig James <cjames@emolecules.com>; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2014-04-14 19:18 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-03-20 16:53 Getting query plan alternatives from query planner? Stefan Keller <sfkeller@gmail.com>
2014-03-20 17:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-03-21 07:37 ` Stefan Keller <sfkeller@gmail.com>
2014-03-21 07:51 ` Atri Sharma <atri.jiit@gmail.com>
2014-03-21 13:34 ` Craig James <cjames@emolecules.com>
2014-03-21 14:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-03-21 19:43 ` Shaun Thomas <sthomas@optionshouse.com>
2014-04-14 14:39 ` Kevin Grittner <kgrittn@ymail.com>
2014-04-14 14:59 ` Eric Schwarzenbach <subscriber@blackbrook.org>
2014-04-14 15:35 Re: Getting query plan alternatives from query planner? Craig James <cjames@emolecules.com>
2014-04-14 18:36 ` Stefan Keller <sfkeller@gmail.com>
2014-04-14 19:18 ` Heikki Linnakangas <hlinnakangas@vmware.com>
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