enable_joinremoval
in [PgSql]
|
From: Tom Lane on 29 Mar 2010 11:46 Robert Haas <robertmhaas(a)gmail.com> writes: > OK, I'll write a patch for that; and a consensus emerges that we > should also have enable_joinremoval, then I will add that as well. I > think the only argument for NOT having enable_joinremoval is that you > can always modify the query to say SELECT * rather than some more > specific SELECT list, Uh, no, the argument for not having enable_joinremoval is that it's useless. In particular, I categorically deny the argument that putting it in will reduce user confusion. If anyone is confused because EXPLAIN shows that some table isn't getting joined to, you think that the fact that somewhere in the manual is a mention of enable_joinremoval will un-confuse them? If they knew that switch was there or what it did, they wouldn't be confused to begin with. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 29 Mar 2010 11:55 On Mon, Mar 29, 2010 at 11:46 AM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas(a)gmail.com> writes: >> OK, I'll write a patch for that; and a consensus emerges that we >> should also have enable_joinremoval, then I will add that as well. I >> think the only argument for NOT having enable_joinremoval is that you >> can always modify the query to say SELECT * rather than some more >> specific SELECT list, > > Uh, no, the argument for not having enable_joinremoval is that it's > useless. > > In particular, I categorically deny the argument that putting it in will > reduce user confusion. If anyone is confused because EXPLAIN shows that > some table isn't getting joined to, you think that the fact that > somewhere in the manual is a mention of enable_joinremoval will > un-confuse them? If they knew that switch was there or what it did, > they wouldn't be confused to begin with. Uh, wow, no, it doesn't make any sense from that point of view. What I think the use case is is seeing how join removal changed the plan. It could work out that the plan WITH join removal is significantly different from the plan WITHOUT join removal. Most of the time that won't be the case - the join will just get snipped out. But suppose we're joining A to B and then to C and then to D, and that's the actual join order. Suppose further that C is most cheaply done as a hash join. It seems just barely possible to think that if the join to C actually doesn't need to be done at all, then the join to D might be done via some other method, because with the disappearance of the join to C the join to D will receive its left input in some kind of sorted order. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Greg Smith on 29 Mar 2010 12:03 Robert Haas wrote: > The query planner is a great piece of code but it > is not so transparently simple that it doesn't need debugging or > instrumentation, and "why did the planner do X" has got to be one of > our top ten most-frequently asked questions. Debugging and instrumentation are two slightly different issues. There is a lot more instrumentation needed in the query optimizer before people have better odds of understanding what's going on in this part of the database. Recent features like pg_stat_statements and auto_explain are just the first round of what people really want here. Now that we can get the explain data out in usable formats (XML, JSON, YAML) for a tool to manage them, the thing at the top of my list in this area for 9.1 is to track down the rumored patch that exports information about the rejected plans considered and get that comitted. That always seems what I want to look at for answering the question "why this plan instead of what I was expecting?" Stepping away from that, from the debugging perspective it seems one way to answer the question "is this unexpected behavior being caused by the new join removal code or not?" is to provide a way to toggle it off and see what changes. Much like enable_seqscan, just because we don't ever want people to use it in production doesn't necessarily mean it's a bad idea to expose it. Also, given that this is a complicated feature, I think it's reasonable to ask whether allowing it to be turned off is the right thing just from the pragmatic basis that it provides a, ahem, backup plan in case there's unexpected difficulty with it in the field. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg(a)2ndQuadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 29 Mar 2010 12:10 On Mon, Mar 29, 2010 at 12:03 PM, Greg Smith <greg(a)2ndquadrant.com> wrote: > Robert Haas wrote: >> >> The query planner is a great piece of code but it >> is not so transparently simple that it doesn't need debugging or >> instrumentation, and "why did the planner do X" has got to be one of >> our top ten most-frequently asked questions. > > Debugging and instrumentation are two slightly different issues. Yeah, you're right. This is debugging, not instrumentation. > There is a > lot more instrumentation needed in the query optimizer before people have > better odds of understanding what's going on in this part of the database. > Recent features like pg_stat_statements and auto_explain are just the first > round of what people really want here. Now that we can get the explain data > out in usable formats (XML, JSON, YAML) for a tool to manage them, the thing > at the top of my list in this area for 9.1 is to track down the rumored > patch that exports information about the rejected plans considered and get > that comitted. That always seems what I want to look at for answering the > question "why this plan instead of what I was expecting?" Having looked at that patch, I am skeptical of it, but we can certainly take a fresh look. > Stepping away from that, from the debugging perspective it seems one way to > answer the question "is this unexpected behavior being caused by the new > join removal code or not?" is to provide a way to toggle it off and see what > changes. Much like enable_seqscan, just because we don't ever want people > to use it in production doesn't necessarily mean it's a bad idea to expose > it. > > Also, given that this is a complicated feature, I think it's reasonable to > ask whether allowing it to be turned off is the right thing just from the > pragmatic basis that it provides a, ahem, backup plan in case there's > unexpected difficulty with it in the field. Yep. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 29 Mar 2010 12:17
Robert Haas <robertmhaas(a)gmail.com> writes: > On Mon, Mar 29, 2010 at 12:03 PM, Greg Smith <greg(a)2ndquadrant.com> wrote: >> at the top of my list in this area for 9.1 is to track down the rumored >> patch that exports information about the rejected plans considered and get >> that comitted. �That always seems what I want to look at for answering the >> question "why this plan instead of what I was expecting?" > Having looked at that patch, I am skeptical of it, but we can > certainly take a fresh look. The problem with this line of thought is that it imagines you can look at worked-out alternative plans. You can't, because the planner doesn't pursue rejected alternatives that far (and you'd not want to wait long enough for it to do so...) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |