Re: [PERFORM] Getting query plan alternatives from query planner?
Shaun Thomas stho...@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
Re: [PERFORM] Getting query plan alternatives from query planner?
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 stho...@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
Re: [PERFORM] Getting query plan alternatives from query planner?
Shaun Thomas stho...@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: [PERFORM] Getting query plan alternatives from query planner?
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 cja...@emolecules.com: Shaun Thomas stho...@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: [PERFORM] Getting query plan alternatives from query planner?
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.1395410...@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
Re: [PERFORM] Getting query plan alternatives from query planner?
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 t...@sss.pgh.pa.us: Stefan Keller sfkel...@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
Re: [PERFORM] Getting query plan alternatives from query planner?
On Fri, Mar 21, 2014 at 1:07 PM, Stefan Keller sfkel...@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: [PERFORM] Getting query plan alternatives from query planner?
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.j...@gmail.com wrote: On Fri, Mar 21, 2014 at 1:07 PM, Stefan Keller sfkel...@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: [PERFORM] Getting query plan alternatives from query planner?
Craig James cja...@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
Re: [PERFORM] Getting query plan alternatives from query planner?
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 stho...@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
[PERFORM] Getting query plan alternatives from query planner?
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
Re: [PERFORM] Getting query plan alternatives from query planner?
Stefan Keller sfkel...@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
