enable_joinremoval
in [PgSql]
|
From: Robert Haas on 29 Mar 2010 09:37 On Mon, Mar 29, 2010 at 4:33 AM, Simon Riggs <simon(a)2ndquadrant.com> wrote: > > We have planner method parameters for many important parts of the > planner. We don't have any parameter for join removal, AFAIK. > > The first question I get asked is "can I see the tables it removed?". > This has been asked of me 3 times now this year, always first question. > Same issue applies to constraint exclusion. > > But we *can* do this with constraint_exclusion, simply by turning it off > and checking the two outputs. > > So I think we need a parameter for join removal also. > > I don't want to turn it off, but I think we need a way for people to > check themselves that the removal of the joins is not an error. I > foresee many false bug reports along the lines of "optimizer ate my join > and I want it back". I had this in my original patch but Tom wanted it taken out. In many cases it's not horribly difficult to work around because you can do SELECT * FROM ... instead of your original select list, but there might be some cases with multiple levels of views where it isn't that easy. I think it would be good to add this back. An even worse problem I've been noticing is that there is no easy way to determine whether the planner's new penchant for inserting Materialize notes in all sorts of fun and exciting places is in fact improving performance or not. I'm not sure there's even a difficult way. I really, really think we need to do something out this - the current situation is really quite horrible. ....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 10:36 Robert Haas <robertmhaas(a)gmail.com> writes: > On Mon, Mar 29, 2010 at 4:33 AM, Simon Riggs <simon(a)2ndquadrant.com> wrote: >> So I think we need a parameter for join removal also. > I had this in my original patch but Tom wanted it taken out. And I still don't want it. We are NOT going in the direction of adding an enable_ knob for every single planner activity --- do you have the faintest idea how many there would be? We have such knobs for a small number of cases where it's arguable that the action might be the wrong thing for a particular query. Join removal, if applicable, can't possibly be the wrong choice; it's better than every other join strategy. 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: Alvaro Herrera on 29 Mar 2010 10:42 Tom Lane escribi�: > Robert Haas <robertmhaas(a)gmail.com> writes: > > On Mon, Mar 29, 2010 at 4:33 AM, Simon Riggs <simon(a)2ndquadrant.com> wrote: > >> So I think we need a parameter for join removal also. > > > I had this in my original patch but Tom wanted it taken out. > > And I still don't want it. We are NOT going in the direction of adding > an enable_ knob for every single planner activity --- do you have the > faintest idea how many there would be? We have such knobs for a small > number of cases where it's arguable that the action might be the wrong > thing for a particular query. Join removal, if applicable, can't > possibly be the wrong choice; it's better than every other join strategy. It seems that what's really needed is some debug output to be able to find out what it did. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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 11:01 Alvaro Herrera <alvherre(a)commandprompt.com> writes: > It seems that what's really needed is some debug output to be able to > find out what it did. Isn't EXPLAIN good enough? 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:20 On Mon, Mar 29, 2010 at 10:36 AM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas(a)gmail.com> writes: >> On Mon, Mar 29, 2010 at 4:33 AM, Simon Riggs <simon(a)2ndquadrant.com> wrote: >>> So I think we need a parameter for join removal also. > >> I had this in my original patch but Tom wanted it taken out. > > And I still don't want it. We are NOT going in the direction of adding > an enable_ knob for every single planner activity --- do you have the > faintest idea how many there would be? We have such knobs for a small > number of cases where it's arguable that the action might be the wrong > thing for a particular query. Join removal, if applicable, can't > possibly be the wrong choice; it's better than every other join strategy. As Tom Lane would put it, you're attacking a straw man. No one has proposed adding a planner knob for "every single planner activity". What Simon and I have proposed is adding some planner knobs that are virtually parallel to the existing ones, which are useful, and your argument (or rather, your assertion) against that is apparently that somehow even thought the OTHER ones are useful, these almost identical ones for cases that aren't covered by the existing knobs will be unuseful. The argument that we don't need a knob to control join removal because it has to always be the best strategy presuposes that enable_seqscan exists because a sequential scan might not be the best strategy, or that enable_hashjoin exists because a hash join might not be the best strategy, which is emphatically nonsense. If that were the purpose of those knobs, that would by definition make them planner hints, albeit incredibly poorly designed ones, and we would be having discussions about the best way to turn them into more useful planner hints, as by allowing them to apply to only certain portions of the query tree and/or changing them from booleans to floats so that you could vary the relative level of discouragement given to any particular planner method, rather than only allowing zero and infinity. Of course, we are NOT having those conversations because that ISN'T the purpose of those knobs. Rather, their purpose, at least AIUI, is to allow the user to see what the planner would have done had it not had those strategies as its disposal - which is just as legitimate for join removal or materialization as it is for hash join or merge join. If someone wants to ask a question like "how much does join removal speed up this query?" or "how much does this material node (that didn't exist in 8.4) speed up or slow down this query?", there is going to be no easy way for them to understand that without these knobs. And for the record, I believe I find it rather amusing that you're asking me if I "have the faintest idea how many there would be". I venture to say that after yourself I might be the person who knows this code best. I know how many there will be, if I get my way, and that number is two. 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. Failing to provide a few trivially simple debugging tools for the small number of major new planner features added in 9.0 is essentially pointless cruelty and will result in an endless series of unanswerable questions on -performance, particularly where materialization is concerned, since for join removal there is at least a workaround (maybe not such an easy one to use in all cases, but it exists - just select all the columns). ....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
|
Next
|
Last
Pages: 1 2 3 4 5 6 Prev: Problems with variable cursorname in ecpg Next: Using HStore type in TSearch |