Re: [HACKERS] Removing INNER JOINs
David Rowley dgrowle...@gmail.com writes: I'm not worried about the cost of the decision at plan init time. I was just confused about what Tom was recommending. I couldn't quite decide from his email if he meant to keep the alternative plan node around so that the executor must transition through it for each row, or to just choose the proper plan at executor init and return the actual root of the selected plan instead of returning the initialised AlternativePlan node (see nodeAlternativePlan.c) TBH, I don't like anything about this patch and would prefer to reject it altogether. I think it's a disaster from a system structural standpoint, will probably induce nasty bugs, and simply doesn't apply to enough real-world queries to be worth either the pain of making it work or the planning overhead it will add. The structural damage could be reduced if you got rid of the far-too-cute idea that you could cut the AlternativePlan node out of the plan tree at executor startup time. Just have it remember which decision it made and pass down all the calls to the appropriate child node. What you're doing here violates the rule that planstate trees have a one-to-one relationship to plan trees. EXPLAIN used to iterate over those trees in lockstep, and there probably still is code that does similar things (in third-party modules if not core), so I don't think we should abandon that principle. Also, the patch seems rather schizophrenic about whether it's relying on run-time decisions or not; in particular I see no use of the PlannedStmt.suitableFor field, and no reason to have one if there's to be an AlternativePlan node making the decision. I'm just about certain that you can't generate the two alternative plans simply by calling make_one_rel() twice. The planner has far too much tendency to scribble on its input data structures for that to work reliably. It's possible you could dodge bugs of that sort, and save some cycles, if you did base relation planning only once and created the alternatives only while working at the join-relation level. Even then I think you'd need to do pushups like what GEQO does in order to repeat join-level planning. (While I'm looking at that: what used to be a reasonably clear API between query_planner and grouping_planner now seems like a complete mess, and I'm quite certain you've created multiple bugs in grouping_planner, because it would need to track more than one e.g. current_pathkeys value for the subplans created for the different final_rels. You can't just arbitrarily do some of those steps in one loop and the others in a totally different loop.) As far as the fundamental-bugs issue goes, I have no faith that there are no pending AFTER trigger events is a sufficient condition for all known foreign-key constraints hold against whatever-random-snapshot-I'm-using; and it's certainly not a *necessary* condition. (BTW the patch should be doing its best to localize knowledge about that, rather than spreading the assumption far and wide through comments and choices of flag/variable names.) I think the best you can hope to say in that line is that if there were no pending events at the time the snapshot was taken, it might be all right. Maybe. But it's not hard to imagine the trigger queue getting emptied while snapshots still exist that can see inconsistent states that prevailed before the triggers fired. (Remember that a trigger might restore consistency by applying additional changes, not just by throwing an error.) STABLE functions are the pain point here since they could execute queries with snapshots from quite some time back. As for the cost issue, that's an awful lot of code you added to remove_useless_joins(). I'm concerned about how much time it's going to spend while failing to prove anything for most queries. For starters, it looks to be O(N^2) in the number of relations, which the existing join_is_removable code is not; and in general it looks like it will do work in many more common cases than the existing code has to. I'm also pretty unhappy about having get_relation_info() physically visiting the pg_constraint catalog for every relation that's ever scanned by any query. (You could probably teach the relcache to remember data about foreign-key constraints, instead of doing it this way.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
On 16 March 2015 at 09:55, David Rowley dgrowle...@gmail.com wrote: I think it's probably possible to do this, but I think it would require calling make_one_rel() with every combination of each possibly removable relations included and not included in the join list. I'm thinking this could end up a lot of work as the number of calls to make_one_rel() would be N^2, where N is the number of relations that may be removable. My line of thought was more along the lines of that the backup/all purpose plan will only be used in very rare cases. Either when a fk has been deferred or if the query is being executed from within a volatile function which has been called by an UPDATE statement which has just modified the table causing a foreign key trigger to be queued. I'm willing to bet someone does this somewhere in the world, but the query that's run would also have to have a removable join. (One of the regression tests I've added exercises this) For that reason I thought it was best to generate only 2 plans. One with *all* possible removable rels removed, and a backup one with nothing removed which will be executed if there's any FK triggers queued up. Agreed, just 2 plans. The two ways of doing this have a massively different look in the EXPLAIN output. With the method the patch currently implements only 1 of the 2 alternative plans are seen by EXPLAIN, this is because I've coded ExecInitAlternativePlan() to return the root node only 1 of the 2 plans. If I had kept the AlternativePlan node around then the EXPLAIN output would have 2 plans, both sitting under the AlternativePlan node. I guess that is at least compatible with how we currently handle other join elimination, so that is acceptable to me. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, RemoteDBA, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
On 1/13/15 5:02 AM, David Rowley wrote: I can't quite get my head around what you mean here, as the idea sounds quite similar to something that's been discussed already and ruled out. If we're joining relation a to relation b, say the plan chosen is a merge join. If we put some special node as the parent of the merge join then how will we know to skip or not skip any sorts that are there especially for the merge join, or perhaps the planner choose an index scan as a sorted path, where now that the join is removed, could become a faster seqscan. The whole plan switching node discussion came from this exact problem. Nobody seemed to like the non-optimal plan that was not properly optimised for having the relation removed. In my mind this is the same as a root level Alternative Plan, so you'd be free to do whatever you wanted in the alternate: - blah blah - Alternate - Merge join ... - SeqScan I'm guessing this would be easier to code, but that's just a guess. The other advantage is if you can't eliminate the join to table A at runtime you could still eliminate table B, whereas a top-level Alternate node doesn't have that flexibility. This does have a disadvantage of creating more plan variations to consider. With a single top-level Alternate node there's only one other option. I believe multiple Alternates would create more options to consider. Ultimately, unless this is easier to code than a top-level alternate, it's probably not worth pursuing. It also seems that transitioning through needless nodes comes at a cost. This is why I quite liked the Alternative Plan node idea, as it allowed me to skip over the alternative plan node at plan initialisation. For init I would expect this to result in a smaller number of nodes than a top-level Alternate, because you wouldn't be duplicating all the stuff above the joins. That said, I rather doubt it's worth worrying about the cost to init; won't it be completely swamped by extra planning cost, no matter how we go about this? -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
On 12 January 2015 at 15:57, Robert Haas robertmh...@gmail.com wrote: On Thu, Jan 8, 2015 at 6:31 AM, David Rowley dgrowle...@gmail.com wrote: I'd be keen to know what people's thoughts are about the nodeAlternativePlan only surviving until the plan is initialised. I find it scary, although sometimes I am easily frightened. Ok remember I'm not actually modifying the plan like I was in the earlier version of the patch. The Alternative Plan node just simply initialises the correct plan and instead of returning it's own initialised state, it returns the initialised state of the selected plan's root node. I have to admit, it didn't really become clear in my head if the frenzy of discussion above gave any sort of indication that this Alternative plan node would remain and be shown in the EXPLAIN output, or the appropriate plan would be selected during plan initialisation and the new root node would become that of the selected plan. When I was implement what was discussed, I decided that it would be better to choose the correct plan during initialisation rather than transitioning through the Alternative plan node for each row. As proved already on this thread, transitioning through needless nodes adds needless executor time overhead. Also if we kept the alternative plan node, then I think the explain would look rather weird and frighteningly complex, as it would effectively be 2 plans in 1. I'm actually quite happy with how simple the executor changes became. It's far more simple and clean than the node stripping code that I had in an earlier version. The parts of the patch that I'm concerned might raise a few eyebrows are the changes to query_planner() as it now returns a List of RelOptInfo instead of a RelOptInfo. Regards David Rowley
Re: [HACKERS] Removing INNER JOINs
On 12/3/14 1:08 PM, Tom Lane wrote: Heikki Linnakangashlinnakan...@vmware.com writes: Do you need to plan for every combination, where some joins are removed and some are not? I would vote for just having two plans and one switch node. To exploit any finer grain, we'd have to have infrastructure that would let us figure out*which* constraints pending triggers might indicate transient invalidity of, and that doesn't seem likely to be worth the trouble. In the interest of keeping the first pass simple... what if there was simply a switch node in front of every join that could be removable? That means you'd still be stuck with the overall plan you got from not removing anything, but simply eliminating the access to the relation(s) might be a big win in many cases, and presumably this would be a lot easier to code. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
On Thu, Jan 8, 2015 at 6:31 AM, David Rowley dgrowle...@gmail.com wrote: I'd be keen to know what people's thoughts are about the nodeAlternativePlan only surviving until the plan is initialised. I find it scary, although sometimes I am easily frightened. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
On 4 December 2014 at 12:24, Simon Riggs si...@2ndquadrant.com wrote: On 3 December 2014 at 12:18, Atri Sharma atri.j...@gmail.com wrote: So the planner keeps all possibility satisfying plans, or it looks at the possible conditions (like presence of foreign key for this case, for eg) and then lets executor choose between them? I'm suggesting the planner keeps 2 plans: One with removable joins, one without the removable joins. I only just noticed the thread moved on while I was flying. So it looks Tom and I said the same thing, or close enough for me to +1 Tom. Another idea would be to only skip Hash and Merge Joins, since the tests for those are fairly easy to put into the Init call. That sounds slightly easier than the proposal with the Option/Choice/Switch node. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
On 3 December 2014 at 08:13, Robert Haas robertmh...@gmail.com wrote: On Sun, Nov 30, 2014 at 12:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: Bottom line, given all the restrictions on whether the optimization can happen, I have very little enthusiasm for the whole idea. I do not think the benefit will be big enough to justify the amount of mess this will introduce. This optimization applies to a tremendous number of real-world cases, and we really need to have it. This was a huge problem for me in my previous life as a web developer. The previous work that we did to remove LEFT JOINs was an enormous help, but it's not enough; we need a way to remove INNER JOINs as well. I thought that David's original approach of doing this in the planner was a good one. That fell down because of the possibility that apparently-valid referential integrity constraints might not be valid at execution time if the triggers were deferred. But frankly, that seems like an awfully nitpicky thing for this to fall down on. Lots of web applications are going to issue only SELECT statements that run as as single-statement transactions, and so that issue, so troubling in theory, will never occur in practice. That doesn't mean that we don't need to account for it somehow to make the code safe, but any argument that it abridges the use case significantly is, in my opinion, not credible. Anyway, David was undeterred by the rejection of that initial approach and rearranged everything, based on suggestions from Andres and later Simon, into the form it's reached now. Kudos to him for his persistance. But your point that we might have chosen a whole different plan if it had known that this join was cheaper is a good one. However, that takes us right back to square one, which is to do this at plan time. I happen to think that's probably better anyway, but I fear we're just going around in circles here. We can either do it at plan time and find some way of handling the fact that there might be deferred triggers that haven't fired yet; or we can do it at execution time and live with the fact that we might have chosen a plan that is not optimal, though still better than executing a completely-unnecessary join. Just so that I don't end up going around in circles again, let me summarise my understanding of the pros and cons of each of the states that this patch has been in. *** Method 1: Removing Inner Joins at planning time: Pros: 1. Plan generated should be optimal, i.e should generate the same plan for the query as if the removed relations were never included in the query's text. 2. On successful join removal planning likely will be faster as there's less paths to consider having fewer relations and join combinations. Cons: 1. Assumptions must be made during planning about the trigger queue being empty or not. During execution, if there are pending fk triggers which need to be executed then we could produce wrong results. *** Method 2: Marking scans as possibly skippable during planning, and skipping joins at execution (Andres' method) Pros: 1. The plan can be executed as normal if there are any foreign key triggers pending. Cons: 1. Planner may not generate optimal plan. e.g sort nodes may be useless for Merge joins 2. Code needed to be added to all join methods to allow skipping, nested loop joins suffered from a small overhead. 3. Small overhead from visiting extra nodes in the plan which would not be present if those nodes had been removed. 4. Problems writing regression tests due to having to use EXPLAIN ANALYZE to try to work out what's going on, and the output containing variable runtime values. *** Method 3: Marking scans as possibly skippable during planning and removing redundant join nodes at executor startup (Simon's method) Pros: 1. The plan can be executed as normal if there are any foreign key triggers pending. 2. Does not require extra code in all join types (see cons #2 above) 3. Does not suffer from extra node visiting overhead (see cons #3 above) Cons: 1. Executor must modify the plan. 2. Planner may have generated a plan which is not optimal for modification by the executor (e.g. Sort nodes for merge join, or index scans for pre-sorted input won't become seqscans which may be more efficient as ordering may not be required after removing a merge join) With each of the methods listed above, someone has had a problem with, and from the feedback given I've made changes based and ended up with the next revision of the patch. Tom has now pointed out that he does not like the executor modifying the plan, which I agree with to an extent as it I really do hate the extra useless nodes that I'm unable to remove from the plan. I'd like to propose Method 4 which I believe solves quite a few of the problems seen in the other method. Method 4: (Which is I think what Mart had in mind, I've only expanded on it a bit with thoughts about possible implementations methods) 1.
Re: [HACKERS] Removing INNER JOINs
On 3 December 2014 at 09:29, David Rowley dgrowle...@gmail.com wrote: *** Method 3: Marking scans as possibly skippable during planning and removing redundant join nodes at executor startup (Simon's method) Pros: 1. The plan can be executed as normal if there are any foreign key triggers pending. 2. Does not require extra code in all join types (see cons #2 above) 3. Does not suffer from extra node visiting overhead (see cons #3 above) Cons: 1. Executor must modify the plan. 2. Planner may have generated a plan which is not optimal for modification by the executor (e.g. Sort nodes for merge join, or index scans for pre-sorted input won't become seqscans which may be more efficient as ordering may not be required after removing a merge join) With each of the methods listed above, someone has had a problem with, and from the feedback given I've made changes based and ended up with the next revision of the patch. Tom has now pointed out that he does not like the executor modifying the plan, which I agree with to an extent as it I really do hate the extra useless nodes that I'm unable to remove from the plan. I guess we need an Option node. Tom and I discussed that about an aeon ago. The Option node has a plan for each situation. At execution time, we make the test specified in the plan and then select the appropriate subplan. That way we can see what is happening in the plan and the executor doesn't need to edit anything. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
On Wed, Dec 3, 2014 at 5:00 PM, Simon Riggs si...@2ndquadrant.com wrote: On 3 December 2014 at 09:29, David Rowley dgrowle...@gmail.com wrote: *** Method 3: Marking scans as possibly skippable during planning and removing redundant join nodes at executor startup (Simon's method) Pros: 1. The plan can be executed as normal if there are any foreign key triggers pending. 2. Does not require extra code in all join types (see cons #2 above) 3. Does not suffer from extra node visiting overhead (see cons #3 above) Cons: 1. Executor must modify the plan. 2. Planner may have generated a plan which is not optimal for modification by the executor (e.g. Sort nodes for merge join, or index scans for pre-sorted input won't become seqscans which may be more efficient as ordering may not be required after removing a merge join) With each of the methods listed above, someone has had a problem with, and from the feedback given I've made changes based and ended up with the next revision of the patch. Tom has now pointed out that he does not like the executor modifying the plan, which I agree with to an extent as it I really do hate the extra useless nodes that I'm unable to remove from the plan. I guess we need an Option node. Tom and I discussed that about an aeon ago. The Option node has a plan for each situation. At execution time, we make the test specified in the plan and then select the appropriate subplan. That way we can see what is happening in the plan and the executor doesn't need to edit anything. So the planner keeps all possibility satisfying plans, or it looks at the possible conditions (like presence of foreign key for this case, for eg) and then lets executor choose between them? So is the idea essentially making the planner return a set of best plans, one for each condition? Are we assured of their optimality at the local level i.e. at each possibility? IMO this sounds like punting the planner's task to executor. Not to mention some overhead for maintaining various plans that might have been discarded early in the planning and path cost evaluation phase (consider a path with pathkeys specified, like with ORDINALITY. Can there be edge cases where we might end up invalidating the entire path if we let executor modify it, or, maybe just lose the ordinality optimization?) I agree that executor should not modify plans, but letting executor choose the plan to execute (out of a set from planner, of course) rather than planner giving executor a single plan and executor not caring about the semantics, seems a bit counterintuitive to me. It might be just me though. Regards, Atri -- Regards, Atri *l'apprenant*
Re: [HACKERS] Removing INNER JOINs
* Atri Sharma (atri.j...@gmail.com) wrote: So the planner keeps all possibility satisfying plans, or it looks at the possible conditions (like presence of foreign key for this case, for eg) and then lets executor choose between them? Right, this was one of the thoughts that I had. So is the idea essentially making the planner return a set of best plans, one for each condition? Are we assured of their optimality at the local level i.e. at each possibility? We *already* have an idea of there being multiple plans (see plancache.c). IMO this sounds like punting the planner's task to executor. Not to mention some overhead for maintaining various plans that might have been discarded early in the planning and path cost evaluation phase (consider a path with pathkeys specified, like with ORDINALITY. Can there be edge cases where we might end up invalidating the entire path if we let executor modify it, or, maybe just lose the ordinality optimization?) The executor isn't modifying the plan, it's just picking one based on what the current situation is (which is information that only the executor can have, such as if there are pending deferred triggers). I agree that executor should not modify plans, but letting executor choose the plan to execute (out of a set from planner, of course) rather than planner giving executor a single plan and executor not caring about the semantics, seems a bit counterintuitive to me. It might be just me though. I don't think it follows that the executor is now required to care about semantics. The planner says use plan A if X is true; use plan B is X is not true and then the executor does exactly that. There's nothing about the plans provided by the planner which are being changed and there is no re-planning going on (though, as I point out, we actually *do* re-plan in cases where we think the new plan is much much better than the prior plan..). Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] Removing INNER JOINs
* Stephen Frost (sfr...@snowman.net) wrote: * Atri Sharma (atri.j...@gmail.com) wrote: So the planner keeps all possibility satisfying plans, or it looks at the possible conditions (like presence of foreign key for this case, for eg) and then lets executor choose between them? Right, this was one of the thoughts that I had. Erm, I had also. Don't mean to imply that it was all my idea or something silly like that. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Removing INNER JOINs
On 2014-12-03 11:30:32 +, Simon Riggs wrote: I guess we need an Option node. Tom and I discussed that about an aeon ago. The Option node has a plan for each situation. At execution time, we make the test specified in the plan and then select the appropriate subplan. That way we can see what is happening in the plan and the executor doesn't need to edit anything. Given David's result where he noticed a performance impact due to the additional branch in the join code - which I still have a bit of a hard time to believe - it seems likely that a whole separate node that has to pass stuff around will be more expensive. I think the switch would actually have to be done in ExecInitNode() et al. David, if you essentially take your previous solution and move the if into ExecInitNode(), does it work well? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
On Wed, Dec 3, 2014 at 4:29 AM, David Rowley dgrowle...@gmail.com wrote: *** Method 1: Removing Inner Joins at planning time: *** Method 2: Marking scans as possibly skippable during planning, and skipping joins at execution (Andres' method) *** Method 3: Marking scans as possibly skippable during planning and removing redundant join nodes at executor startup (Simon's method) [] a. can we invoke the planner during executor init? I'm pretty sure that we can't safely invoke the planner during executor startup, and that doing surgery on the plan tree (option #3) is unsafe also. I'm pretty clear why the latter is unsafe: it might be a copy of a data structure that's going to be reused. I am less clear on the specifics of why the former is unsafe, but what I think it boils down to is that the plan per se needs to be finalized before we begin execution; any replanning needs to be handled in the plancache code. I am not sure whether it's feasible to do something about this at the plancache layer; we have an is_oneshot flag there, so perhaps one-shot plans could simply test whether there are pending triggers, and non-oneshot plans could forego the optimization until we come up with something better. If that doesn't work for some reason, then I think we basically have to give up on the idea of replanning if the situation becomes unsafe between planning and execution. That leaves us with two alternatives. One is to create a plan incorporating the optimization and another not incorporating the optimization and decide between them at runtime, which sounds expensive. The second is to create a plan that contemplates performing the join and skip the join if it turns out to be possible, living with the fact that the resulting plan might be less than optimal - in other words, option #2. I am not sure that's all that bad. Planning is ALWAYS an exercise in predicting the future: we use statistics gathered at some point in the past, which are furthermore imprecise, to predict what will happen if we try to execute a given plan at some point in the future. Sometimes we are wrong, but that doesn't prevent us from trying to our best to predict the outcome; so here. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
On Wed, Dec 3, 2014 at 8:32 PM, Stephen Frost sfr...@snowman.net wrote: * Atri Sharma (atri.j...@gmail.com) wrote: So the planner keeps all possibility satisfying plans, or it looks at the possible conditions (like presence of foreign key for this case, for eg) and then lets executor choose between them? Right, this was one of the thoughts that I had. So is the idea essentially making the planner return a set of best plans, one for each condition? Are we assured of their optimality at the local level i.e. at each possibility? We *already* have an idea of there being multiple plans (see plancache.c). Thanks for pointing me there. What I am concerned about is that in this case, the option plans are competing plans rather than separate plans. My main concern is that we might be not able to discard plans that we know that are not optimal early in planning. My understanding is that planner is aggressive when discarding potential paths. Maintaining them ahead and storing and returning them might have issues, but that is only my thought. -- Regards, Atri *l'apprenant*
Re: [HACKERS] Removing INNER JOINs
On 2014-12-03 10:51:19 -0500, Robert Haas wrote: On Wed, Dec 3, 2014 at 4:29 AM, David Rowley dgrowle...@gmail.com wrote: *** Method 1: Removing Inner Joins at planning time: *** Method 2: Marking scans as possibly skippable during planning, and skipping joins at execution (Andres' method) *** Method 3: Marking scans as possibly skippable during planning and removing redundant join nodes at executor startup (Simon's method) [] a. can we invoke the planner during executor init? I'm pretty sure that we can't safely invoke the planner during executor startup, and that doing surgery on the plan tree (option #3) is unsafe also. I'm pretty clear why the latter is unsafe: it might be a copy of a data structure that's going to be reused. We already have a transformation between the plan and execution tree. I'm right now not seing why transforming the trees in ExecInitNode() et. al. would be unsafe - it looks fairly simple to switch between different execution plans there. Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
On Wed, Dec 3, 2014 at 10:56 AM, Andres Freund and...@2ndquadrant.com wrote: On 2014-12-03 10:51:19 -0500, Robert Haas wrote: On Wed, Dec 3, 2014 at 4:29 AM, David Rowley dgrowle...@gmail.com wrote: *** Method 1: Removing Inner Joins at planning time: *** Method 2: Marking scans as possibly skippable during planning, and skipping joins at execution (Andres' method) *** Method 3: Marking scans as possibly skippable during planning and removing redundant join nodes at executor startup (Simon's method) [] a. can we invoke the planner during executor init? I'm pretty sure that we can't safely invoke the planner during executor startup, and that doing surgery on the plan tree (option #3) is unsafe also. I'm pretty clear why the latter is unsafe: it might be a copy of a data structure that's going to be reused. We already have a transformation between the plan and execution tree. We do? I think what we have is a plan tree, which is potentially stored in a plan cache someplace and thus must be read-only, and a planstate tree, which contains the stuff that is for this specific execution. There's probably some freedom to do exciting things in the planstate nodes, but I don't think you can tinker with the plan itself. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
On 2014-12-03 11:11:49 -0500, Robert Haas wrote:
On Wed, Dec 3, 2014 at 10:56 AM, Andres Freund and...@2ndquadrant.com wrote:
On 2014-12-03 10:51:19 -0500, Robert Haas wrote:
On Wed, Dec 3, 2014 at 4:29 AM, David Rowley dgrowle...@gmail.com wrote:
*** Method 1: Removing Inner Joins at planning time:
*** Method 2: Marking scans as possibly skippable during planning, and
skipping joins at execution (Andres' method)
*** Method 3: Marking scans as possibly skippable during planning and
removing redundant join nodes at executor startup (Simon's method)
[]
a. can we invoke the planner during executor init?
I'm pretty sure that we can't safely invoke the planner during
executor startup, and that doing surgery on the plan tree (option #3)
is unsafe also. I'm pretty clear why the latter is unsafe: it might
be a copy of a data structure that's going to be reused.
We already have a transformation between the plan and execution
tree.
We do?
I think what we have is a plan tree, which is potentially stored in a
plan cache someplace and thus must be read-only, and a planstate tree,
which contains the stuff that is for this specific execution. There's
probably some freedom to do exciting things in the planstate nodes,
but I don't think you can tinker with the plan itself.
Well, the planstate tree is what determines the execution, right? I
don't see what would stop us from doing something like replacing:
PlanState *
ExecInitNode(Plan *node, EState *estate, int eflags)
{
...
case T_NestLoop:
result = (PlanState *) ExecInitNestLoop((NestLoop *) node,
estate, eflags);
by
case T_NestLoop:
if (JoinCanBeSkipped(node))
result = NonSkippedJoinNode(node);
else
result = (PlanState *) ExecInitNestLoop((NestLoop
*) node,
estate, eflags);
Where JoinCanBeSkipped() and NonSkippedJoinNode() contain the logic
from David's early patch where he put the logic entirely into the actual
execution phase.
We'd probably want to move the join nodes into a separate ExecInitJoin()
function and do the JoinCanBeSkipped() and NonSkippedJoin() node in the
generic code.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
* Atri Sharma (atri.j...@gmail.com) wrote: What I am concerned about is that in this case, the option plans are competing plans rather than separate plans. Not sure I follow this thought entirely.. The plans in the plancache are competeing, but separate, plans. My main concern is that we might be not able to discard plans that we know that are not optimal early in planning. My understanding is that planner is aggressive when discarding potential paths. Maintaining them ahead and storing and returning them might have issues, but that is only my thought. The planner is aggressive at discarding potential paths, but this is all a consideration for how expensive this particular optimization is, not an issue with the approach itself. We certainly don't want an optimization that doubles the time for 100% of queries planned but only saves time in 5% of the cases, but if we can spend an extra 5% of the time required for planning in the 1% of cases where the optimization could possibly happen to save a huge amount of time for those queries, then it's something to consider. We would definitely want to spend as little time as possible checking for this optimization in cases where it isn't possible to use the optimization. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Removing INNER JOINs
On Wed, Dec 3, 2014 at 11:23 AM, Andres Freund and...@2ndquadrant.com wrote:
Well, the planstate tree is what determines the execution, right? I
don't see what would stop us from doing something like replacing:
PlanState *
ExecInitNode(Plan *node, EState *estate, int eflags)
{
...
case T_NestLoop:
result = (PlanState *) ExecInitNestLoop((NestLoop *) node,
estate, eflags);
by
case T_NestLoop:
if (JoinCanBeSkipped(node))
result = NonSkippedJoinNode(node);
else
result = (PlanState *) ExecInitNestLoop((NestLoop
*) node,
estate, eflags);
Where JoinCanBeSkipped() and NonSkippedJoinNode() contain the logic
from David's early patch where he put the logic entirely into the actual
execution phase.
Yeah, maybe. I think there's sort of a coding principle that the plan
and planstate trees should match up one-to-one, but it's possible that
nothing breaks if they don't, or that I've misunderstood the coding
rule in the first instance.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
Robert Haas robertmh...@gmail.com writes:
On Wed, Dec 3, 2014 at 11:23 AM, Andres Freund and...@2ndquadrant.com wrote:
Well, the planstate tree is what determines the execution, right? I
don't see what would stop us from doing something like replacing:
PlanState *
ExecInitNode(Plan *node, EState *estate, int eflags)
{
...
case T_NestLoop:
result = (PlanState *) ExecInitNestLoop((NestLoop *) node,
estate, eflags);
by
case T_NestLoop:
if (JoinCanBeSkipped(node))
result = NonSkippedJoinNode(node);
else
result = (PlanState *) ExecInitNestLoop((NestLoop *) node,
estate, eflags);
Where JoinCanBeSkipped() and NonSkippedJoinNode() contain the logic
from David's early patch where he put the logic entirely into the actual
execution phase.
Yeah, maybe. I think there's sort of a coding principle that the plan
and planstate trees should match up one-to-one, but it's possible that
nothing breaks if they don't, or that I've misunderstood the coding
rule in the first instance.
Far better would be what I mentioned upthread: an explicit switch node
in the plan tree, analogous to the existing AlternativeSubPlan structure.
ChooseJoinSubPlan
- plan tree requiring all tables to be joined
- plan tree not requiring all tables to be joined
This allows sensible display by EXPLAIN and avoids the need for the core
executor code to be dirtied with implementation of the precise switch
rule: all that logic goes into the ChooseJoinSubPlan plan node code.
I would envision the planner starting out generating the first subplan
(without the optimization), but as it goes along, noting whether there
are any opportunities for join removal. At the end, if it found that
there were such opportunities, re-plan assuming that removal is possible.
Then stick a switch node on top.
This would give optimal plans for both cases, and it would avoid the need
for lots of extra planner cycles when the optimization can't be applied
... except for one small detail, which is that the planner has a bad habit
of scribbling on its own input. I'm not sure how much cleanup work would
be needed before that re-plan operation could happen as easily as is
suggested above. But in principle this could be made to work.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
On Wed, Dec 3, 2014 at 12:08 PM, Tom Lane t...@sss.pgh.pa.us wrote: I would envision the planner starting out generating the first subplan (without the optimization), but as it goes along, noting whether there are any opportunities for join removal. At the end, if it found that there were such opportunities, re-plan assuming that removal is possible. Then stick a switch node on top. This would give optimal plans for both cases, and it would avoid the need for lots of extra planner cycles when the optimization can't be applied ... except for one small detail, which is that the planner has a bad habit of scribbling on its own input. I'm not sure how much cleanup work would be needed before that re-plan operation could happen as easily as is suggested above. But in principle this could be made to work. Doesn't this double the planning overhead, in most cases for no benefit? The alternative plan used only when there are deferred triggers is rarely going to get used. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
Robert Haas robertmh...@gmail.com writes: On Wed, Dec 3, 2014 at 12:08 PM, Tom Lane t...@sss.pgh.pa.us wrote: I would envision the planner starting out generating the first subplan (without the optimization), but as it goes along, noting whether there are any opportunities for join removal. At the end, if it found that there were such opportunities, re-plan assuming that removal is possible. Then stick a switch node on top. This would give optimal plans for both cases, and it would avoid the need for lots of extra planner cycles when the optimization can't be applied ... except for one small detail, which is that the planner has a bad habit of scribbling on its own input. I'm not sure how much cleanup work would be needed before that re-plan operation could happen as easily as is suggested above. But in principle this could be made to work. Doesn't this double the planning overhead, in most cases for no benefit? The alternative plan used only when there are deferred triggers is rarely going to get used. Personally, I remain of the opinion that this optimization will apply in only a tiny fraction of real-world cases, so I'm mostly concerned about not blowing out planning time when the optimization doesn't apply. However, even granting that that is a concern, so what? You *have* to do the planning twice, or you're going to be generating a crap plan for one case or the other. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
* Tom Lane (t...@sss.pgh.pa.us) wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Dec 3, 2014 at 12:08 PM, Tom Lane t...@sss.pgh.pa.us wrote: I would envision the planner starting out generating the first subplan (without the optimization), but as it goes along, noting whether there are any opportunities for join removal. At the end, if it found that there were such opportunities, re-plan assuming that removal is possible. Then stick a switch node on top. This would give optimal plans for both cases, and it would avoid the need for lots of extra planner cycles when the optimization can't be applied ... except for one small detail, which is that the planner has a bad habit of scribbling on its own input. I'm not sure how much cleanup work would be needed before that re-plan operation could happen as easily as is suggested above. But in principle this could be made to work. Doesn't this double the planning overhead, in most cases for no benefit? The alternative plan used only when there are deferred triggers is rarely going to get used. Personally, I remain of the opinion that this optimization will apply in only a tiny fraction of real-world cases, so I'm mostly concerned about not blowing out planning time when the optimization doesn't apply. This was my thought also- most of the time we won't be able to apply the optimization and we'll know that pretty early on and can skip the double planning. What makes this worthwhile is that there are cases where it'll be applied regularly due to certain tools/technologies being used and the extra planning will be more than made up for by the reduction in execution time. However, even granting that that is a concern, so what? You *have* to do the planning twice, or you're going to be generating a crap plan for one case or the other. Yeah, I don't see a way around that.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Removing INNER JOINs
Stephen Frost sfr...@snowman.net writes: * Tom Lane (t...@sss.pgh.pa.us) wrote: However, even granting that that is a concern, so what? You *have* to do the planning twice, or you're going to be generating a crap plan for one case or the other. Yeah, I don't see a way around that.. Also, it occurs to me that it's only necessary to repeat the join search part of the process, which means that in principle the mechanisms already exist for that; see GEQO. This means that for small join problems, the total planning time would much less than double anyway. For large problems, where the join search is the bulk of the time, we could hope that removal of unnecessary joins would reduce the join search runtime enough that the second search would be pretty negligible next to the first (which is not optional). So I think it'll double the runtime is an unfounded objection, or at least there's good reason to hope it's unfounded. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
On Wed, Dec 3, 2014 at 11:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: Stephen Frost sfr...@snowman.net writes: * Tom Lane (t...@sss.pgh.pa.us) wrote: However, even granting that that is a concern, so what? You *have* to do the planning twice, or you're going to be generating a crap plan for one case or the other. Yeah, I don't see a way around that.. Also, it occurs to me that it's only necessary to repeat the join search part of the process, which means that in principle the mechanisms already exist for that; see GEQO. This means that for small join problems, the total planning time would much less than double anyway. For large problems, where the join search is the bulk of the time, we could hope that removal of unnecessary joins would reduce the join search runtime enough that the second search would be pretty negligible next to the first (which is not optional). So I think it'll double the runtime is an unfounded objection, or at least there's good reason to hope it's unfounded. Is it possible to only replan part of the plan in case of this optimization? I think that we might need to only replan parts of the original plan (as you mentioned, join search and above). So we could reuse the original plan in part and not do a lot of replanning (an obvious case is scan strategy, which we can assume will not change for the two plans). I wonder if we could have a rule based system for replacement of some plan nodes with other type of nodes. As we discover more cases like this, we could add more rules. Wild thought though. -- Regards, Atri *l'apprenant*
Re: [HACKERS] Removing INNER JOINs
On Wed, Dec 3, 2014 at 12:33 PM, Tom Lane t...@sss.pgh.pa.us wrote: Stephen Frost sfr...@snowman.net writes: * Tom Lane (t...@sss.pgh.pa.us) wrote: However, even granting that that is a concern, so what? You *have* to do the planning twice, or you're going to be generating a crap plan for one case or the other. Yeah, I don't see a way around that.. Also, it occurs to me that it's only necessary to repeat the join search part of the process, which means that in principle the mechanisms already exist for that; see GEQO. This means that for small join problems, the total planning time would much less than double anyway. For large problems, where the join search is the bulk of the time, we could hope that removal of unnecessary joins would reduce the join search runtime enough that the second search would be pretty negligible next to the first (which is not optional). So I think it'll double the runtime is an unfounded objection, or at least there's good reason to hope it's unfounded. OK. One other point of hope is that, in my experience, the queries where you need join removal are the ones where there are lots of tables being joined and there are often quite a few of those joins that can be removed, not just one. So the extra planner overhead might pay off anyway. (It still seems a shame to have to plan for the not-removing-the-joins case since it will so rarely happen. But maybe I should take what I can get.) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
Atri Sharma atri.j...@gmail.com writes: Is it possible to only replan part of the plan in case of this optimization? I think that we might need to only replan parts of the original plan (as you mentioned, join search and above). So we could reuse the original plan in part and not do a lot of replanning (an obvious case is scan strategy, which we can assume will not change for the two plans). I think you assume wrong; or at least, I certainly would not wish to hard-wire any such assumption. Skipping some joins could change the shape of the join tree *completely*, because the cost estimates will change so much. And that could in turn lead to making different choices of scan methods, eg, we might or might not care about sort order of a scan result if we change join methods. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
On Wed, Dec 3, 2014 at 11:19 PM, Tom Lane t...@sss.pgh.pa.us wrote: Atri Sharma atri.j...@gmail.com writes: Is it possible to only replan part of the plan in case of this optimization? I think that we might need to only replan parts of the original plan (as you mentioned, join search and above). So we could reuse the original plan in part and not do a lot of replanning (an obvious case is scan strategy, which we can assume will not change for the two plans). I think you assume wrong; or at least, I certainly would not wish to hard-wire any such assumption. Skipping some joins could change the shape of the join tree *completely*, because the cost estimates will change so much. And that could in turn lead to making different choices of scan methods, eg, we might or might not care about sort order of a scan result if we change join methods. regards, tom lane Agreed, but in some cases, we could possibly make some assumptions (if there is no index, if a large fraction of table will be returned in scan, FunctionScan).
Re: [HACKERS] Removing INNER JOINs
* Atri Sharma (atri.j...@gmail.com) wrote: Agreed, but in some cases, we could possibly make some assumptions (if there is no index, if a large fraction of table will be returned in scan, FunctionScan). All neat ideas but how about we get something which works in the way being asked for before we start trying to optimize it..? Maybe I'm missing something, but getting all of this infrastructure into place and making sure things aren't done to the plan tree which shouldn't be (or done to all of them if necessary..) is enough that we should get that bit done first and then worry if there are ways we can further improve things.. THanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Removing INNER JOINs
On Wed, Dec 3, 2014 at 11:27 PM, Stephen Frost sfr...@snowman.net wrote: * Atri Sharma (atri.j...@gmail.com) wrote: Agreed, but in some cases, we could possibly make some assumptions (if there is no index, if a large fraction of table will be returned in scan, FunctionScan). All neat ideas but how about we get something which works in the way being asked for before we start trying to optimize it..? Maybe I'm missing something, but getting all of this infrastructure into place and making sure things aren't done to the plan tree which shouldn't be (or done to all of them if necessary..) is enough that we should get that bit done first and then worry if there are ways we can further improve things.. Right,sorry for digressing. I think we are in agreement as to what needs to be done (start with a plan, note ideas and replan if necessary). The idea of executor modifying the plan (or personally, even choosing the plan) seems counterintuitive. Does it also make sense to recalculate the costs from scratch for the replan? It might be, I am just asking. Regards, Atri
Re: [HACKERS] Removing INNER JOINs
Stephen Frost sfr...@snowman.net writes: * Atri Sharma (atri.j...@gmail.com) wrote: Agreed, but in some cases, we could possibly make some assumptions (if there is no index, if a large fraction of table will be returned in scan, FunctionScan). All neat ideas but how about we get something which works in the way being asked for before we start trying to optimize it..? Maybe I'm missing something, but getting all of this infrastructure into place and making sure things aren't done to the plan tree which shouldn't be (or done to all of them if necessary..) is enough that we should get that bit done first and then worry if there are ways we can further improve things.. Yeah; moreover, there's no evidence that hard-wiring such assumptions would save anything. In the example of a FunctionScan, guess what: there's only one Path for that relation anyway. I think the right approach for now is to emulate the GEQO precedent as closely as possible. Build all the single-relation Paths the same as now, then do a join search over all the relations, then (if we've noticed that some joins are potentially removable) do another join search over just the nonremovable relations. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
Atri Sharma atri.j...@gmail.com writes: Does it also make sense to recalculate the costs from scratch for the replan? It might be, I am just asking. The join costs would be recalculated from scratch, yes. The single-relation Paths would already exist and their costs would not change. Again, if you've not studied how GEQO works, you probably should go do that before thinking more about how this would work. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
On Wed, Dec 3, 2014 at 11:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Stephen Frost sfr...@snowman.net writes: * Atri Sharma (atri.j...@gmail.com) wrote: Agreed, but in some cases, we could possibly make some assumptions (if there is no index, if a large fraction of table will be returned in scan, FunctionScan). All neat ideas but how about we get something which works in the way being asked for before we start trying to optimize it..? Maybe I'm missing something, but getting all of this infrastructure into place and making sure things aren't done to the plan tree which shouldn't be (or done to all of them if necessary..) is enough that we should get that bit done first and then worry if there are ways we can further improve things.. Yeah; moreover, there's no evidence that hard-wiring such assumptions would save anything. In the example of a FunctionScan, guess what: there's only one Path for that relation anyway. That is precisely what I meant :) I guess I was being too over cautious and even trying to save the time spent in evaluating whatever paths we have and building new FunctionScan paths... I think the right approach for now is to emulate the GEQO precedent as closely as possible. Build all the single-relation Paths the same as now, then do a join search over all the relations, then (if we've noticed that some joins are potentially removable) do another join search over just the nonremovable relations. How about using geqo more liberally when replanning (decrease the number of relations in join before geqo is hit?) -- Regards, Atri *l'apprenant*
Re: [HACKERS] Removing INNER JOINs
Atri Sharma atri.j...@gmail.com writes: On Wed, Dec 3, 2014 at 11:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think the right approach for now is to emulate the GEQO precedent as closely as possible. Build all the single-relation Paths the same as now, then do a join search over all the relations, then (if we've noticed that some joins are potentially removable) do another join search over just the nonremovable relations. How about using geqo more liberally when replanning (decrease the number of relations in join before geqo is hit?) This is going to be quite difficult enough without overcomplicating it. Or as a wise man once said, premature optimization is the root of all evil. Get it working in the basic way and then see if improvement is necessary at all. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
On Wed, Dec 3, 2014 at 11:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Atri Sharma atri.j...@gmail.com writes: On Wed, Dec 3, 2014 at 11:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think the right approach for now is to emulate the GEQO precedent as closely as possible. Build all the single-relation Paths the same as now, then do a join search over all the relations, then (if we've noticed that some joins are potentially removable) do another join search over just the nonremovable relations. How about using geqo more liberally when replanning (decrease the number of relations in join before geqo is hit?) This is going to be quite difficult enough without overcomplicating it. Or as a wise man once said, premature optimization is the root of all evil. Get it working in the basic way and then see if improvement is necessary at all. Sure, I can take a crack at it since I am working on a patch that does require this alternative path approach. Let me try something and report my experimental results.
Re: [HACKERS] Removing INNER JOINs
On 12/03/2014 07:41 PM, Robert Haas wrote: On Wed, Dec 3, 2014 at 12:33 PM, Tom Lane t...@sss.pgh.pa.us wrote: Stephen Frost sfr...@snowman.net writes: * Tom Lane (t...@sss.pgh.pa.us) wrote: However, even granting that that is a concern, so what? You *have* to do the planning twice, or you're going to be generating a crap plan for one case or the other. Yeah, I don't see a way around that.. Also, it occurs to me that it's only necessary to repeat the join search part of the process, which means that in principle the mechanisms already exist for that; see GEQO. This means that for small join problems, the total planning time would much less than double anyway. For large problems, where the join search is the bulk of the time, we could hope that removal of unnecessary joins would reduce the join search runtime enough that the second search would be pretty negligible next to the first (which is not optional). So I think it'll double the runtime is an unfounded objection, or at least there's good reason to hope it's unfounded. OK. One other point of hope is that, in my experience, the queries where you need join removal are the ones where there are lots of tables being joined and there are often quite a few of those joins that can be removed, not just one. So the extra planner overhead might pay off anyway. Do you need to plan for every combination, where some joins are removed and some are not? I hope the same mechanism could be used to prepare a plan for a query with parameters, where the parameters might or might not allow a partial index to be used. We have some smarts nowadays to use custom plans, but this could be better. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
Heikki Linnakangas hlinnakan...@vmware.com writes: Do you need to plan for every combination, where some joins are removed and some are not? I would vote for just having two plans and one switch node. To exploit any finer grain, we'd have to have infrastructure that would let us figure out *which* constraints pending triggers might indicate transient invalidity of, and that doesn't seem likely to be worth the trouble. I hope the same mechanism could be used to prepare a plan for a query with parameters, where the parameters might or might not allow a partial index to be used. We have some smarts nowadays to use custom plans, but this could be better. Interesting thought, but that would be a totally different switch condition ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
On Wed, Dec 03, 2014 at 02:08:27PM -0500, Tom Lane wrote: Heikki Linnakangas hlinnakan...@vmware.com writes: Do you need to plan for every combination, where some joins are removed and some are not? I would vote for just having two plans and one switch node. To exploit any finer grain, we'd have to have infrastructure that would let us figure out *which* constraints pending triggers might indicate transient invalidity of, and that doesn't seem likely to be worth the trouble. I hope the same mechanism could be used to prepare a plan for a query with parameters, where the parameters might or might not allow a partial index to be used. We have some smarts nowadays to use custom plans, but this could be better. Interesting thought, but that would be a totally different switch condition ... regards, tom lane Or between a node with a low rows count and a high rows count for those pesky mis-estimation queries. Regards, Ken -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
On Wed, Dec 3, 2014 at 2:09 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Dec 3, 2014 at 12:08 PM, Tom Lane t...@sss.pgh.pa.us wrote: I would envision the planner starting out generating the first subplan (without the optimization), but as it goes along, noting whether there are any opportunities for join removal. At the end, if it found that there were such opportunities, re-plan assuming that removal is possible. Then stick a switch node on top. This would give optimal plans for both cases, and it would avoid the need for lots of extra planner cycles when the optimization can't be applied ... except for one small detail, which is that the planner has a bad habit of scribbling on its own input. I'm not sure how much cleanup work would be needed before that re-plan operation could happen as easily as is suggested above. But in principle this could be made to work. Doesn't this double the planning overhead, in most cases for no benefit? The alternative plan used only when there are deferred triggers is rarely going to get used. It shouldn't. It will only double (at worst) planning overhead for the queries that do have removable joins, which would be the ones benefiting from the extra work. Whether that extra work pays off is the question to ask here. Perhaps whether or not to remove the joins could be a decision made accounting for overall plan cost and fraction of joins removed, as to avoid the extra planning if execution will be fast anyway. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
On Sun, Nov 30, 2014 at 12:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: Bottom line, given all the restrictions on whether the optimization can happen, I have very little enthusiasm for the whole idea. I do not think the benefit will be big enough to justify the amount of mess this will introduce. This optimization applies to a tremendous number of real-world cases, and we really need to have it. This was a huge problem for me in my previous life as a web developer. The previous work that we did to remove LEFT JOINs was an enormous help, but it's not enough; we need a way to remove INNER JOINs as well. I thought that David's original approach of doing this in the planner was a good one. That fell down because of the possibility that apparently-valid referential integrity constraints might not be valid at execution time if the triggers were deferred. But frankly, that seems like an awfully nitpicky thing for this to fall down on. Lots of web applications are going to issue only SELECT statements that run as as single-statement transactions, and so that issue, so troubling in theory, will never occur in practice. That doesn't mean that we don't need to account for it somehow to make the code safe, but any argument that it abridges the use case significantly is, in my opinion, not credible. Anyway, David was undeterred by the rejection of that initial approach and rearranged everything, based on suggestions from Andres and later Simon, into the form it's reached now. Kudos to him for his persistance. But your point that we might have chosen a whole different plan if it had known that this join was cheaper is a good one. However, that takes us right back to square one, which is to do this at plan time. I happen to think that's probably better anyway, but I fear we're just going around in circles here. We can either do it at plan time and find some way of handling the fact that there might be deferred triggers that haven't fired yet; or we can do it at execution time and live with the fact that we might have chosen a plan that is not optimal, though still better than executing a completely-unnecessary join. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
* Robert Haas (robertmh...@gmail.com) wrote: On Sun, Nov 30, 2014 at 12:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: Bottom line, given all the restrictions on whether the optimization can happen, I have very little enthusiasm for the whole idea. I do not think the benefit will be big enough to justify the amount of mess this will introduce. This optimization applies to a tremendous number of real-world cases, and we really need to have it. This was a huge problem for me in my previous life as a web developer. The previous work that we did to remove LEFT JOINs was an enormous help, but it's not enough; we need a way to remove INNER JOINs as well. For my 2c, I'm completely with Robert on this one. There are a lot of cases this could help with, particularly things coming out of ORMs (which, yes, might possibly be better written, but that's a different issue). I thought that David's original approach of doing this in the planner was a good one. That fell down because of the possibility that apparently-valid referential integrity constraints might not be valid at execution time if the triggers were deferred. But frankly, that seems like an awfully nitpicky thing for this to fall down on. Lots of web applications are going to issue only SELECT statements that run as as single-statement transactions, and so that issue, so troubling in theory, will never occur in practice. That doesn't mean that we don't need to account for it somehow to make the code safe, but any argument that it abridges the use case significantly is, in my opinion, not credible. Agreed with this also, deferred triggers are not common-place in my experience and when it *does* happen, ime at least, it's because you have a long-running data load or similar where you're not going to care one bit that large, complicated JOINs aren't as fast as they might have been otherwise. Anyway, David was undeterred by the rejection of that initial approach and rearranged everything, based on suggestions from Andres and later Simon, into the form it's reached now. Kudos to him for his persistance. But your point that we might have chosen a whole different plan if it had known that this join was cheaper is a good one. However, that takes us right back to square one, which is to do this at plan time. I happen to think that's probably better anyway, but I fear we're just going around in circles here. We can either do it at plan time and find some way of handling the fact that there might be deferred triggers that haven't fired yet; or we can do it at execution time and live with the fact that we might have chosen a plan that is not optimal, though still better than executing a completely-unnecessary join. Right, we can't get it wrong in the face of deferred triggers either. Have we considered only doing the optimization for read-only transactions? I'm not thrilled with that, but at least we'd get out from under this deferred triggers concern. Another way might be an option to say use the optimization, but throw an error if you run into a deferred trigger, or perhaps save both plans and use whichever one we can when we get to execution time? That could make planning time go up too much to work, but perhaps it's worth testing.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Removing INNER JOINs
Hi David (and others), David Rowley wrote: Hi, Starting a new thread which continues on from http://www.postgresql.org/message-id/caaphdvoec8ygwoahvsri-84en2k0tnh6gpxp1k59y9juc1w...@mail.gmail.com To give a brief summary for any new readers: The attached patch allows for INNER JOINed relations to be removed from the plan, providing none of the columns are used for anything, and a foreign key exists which proves that a record must exist in the table being removed which matches the join condition: I'm looking for a bit of feedback around the method I'm using to prune the redundant plan nodes out of the plan tree at executor startup. Particularly around not stripping the Sort nodes out from below a merge join, even if the sort order is no longer required due to the merge join node being removed. This potentially could leave the plan suboptimal when compared to a plan that the planner could generate when the removed relation was never asked for in the first place. I did read this patch (and the previous patch about removing SEMI-joins) with great interest. I don't know the code well enough to say much about the patch itself, but I hope to have some usefull ideas about the the global process. I think performance can be greatly improved if the planner is able to use information based on the current data. I think these patches are just two examples of where assumptions during planning are usefull. I think there are more possibilities for this kind of assumpions (for example unique constraints, empty tables). There are some more details around the reasons behind doing this weird executor startup plan pruning around here: http://www.postgresql.org/message-id/20141006145957.ga20...@awork2.anarazel.de The problem here is that assumpions done during planning might not hold during execution. That is why you placed the final decision about removing a join in the executor. If a plan is made, you know under which assumptions are made in the final plan. In this case, the assumption is that a foreign key is still valid. In general, there are a lot more assumptions, such as the still existing of an index or the still existing of columns. There also are soft assumptions, assuming that the used statistics are still reasonable. My suggestion is to check the assumptions at the start of executor. If they still hold, you can just execute the plan as it is. If one or more assumptions doesn't hold, there are a couple of things you might do: * Make a new plan. The plan is certain to match all conditions because at that time, a snapshot is already taken. * Check the assumption. This can be a costly operation with no guarantee of success. * Change the existing plan to not rely on the failed assumption. * Use an already stored alternate plan (generate during the initial plan). You currently change the plan in executer code. I suggest to go back to the planner if the assumpion doesn't hold. The planner can then decide to change the plan. The planner can also conclude to fully replan if there are reasons for it. If the planner knows that it needs to replan if the assumption will not hold during execution, the cost of replanning multiplied by the chance of the assumption not holding during exeuction should be part of the decision to deliver a plan with an assumpion in the first place. There are also other cases such as MergeJoins performing btree index scans in order to obtain ordered results for a MergeJoin that would be better executed as a SeqScan when the MergeJoin can be removed. Perhaps some costs could be adjusted at planning time when there's a possibility that joins could be removed at execution time, although I'm not quite sure about this as it risks generating a poor plan in the case when the joins cannot be removed. Maybe this is a case where you are better off replanning if the assumption doesn't hold instead of changing the generated exeuction plan. In that case you can remove the join before the path is made. Comments are most welcome Regards David Rowley Regards, Mart -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
On 30 November 2014 at 23:19, Mart Kelder m...@kelder31.nl wrote: I think performance can be greatly improved if the planner is able to use information based on the current data. I think these patches are just two examples of where assumptions during planning are usefull. I think there are more possibilities for this kind of assumpions (for example unique constraints, empty tables). The problem here is that assumpions done during planning might not hold during execution. That is why you placed the final decision about removing a join in the executor. If a plan is made, you know under which assumptions are made in the final plan. In this case, the assumption is that a foreign key is still valid. In general, there are a lot more assumptions, such as the still existing of an index or the still existing of columns. There also are soft assumptions, assuming that the used statistics are still reasonable. Hi Mart, That's an interesting idea. Though I think it would be much harder to decide if it's a good idea to go off and replan for things like empty tables as that's not known at executor startup, and may only be discovered 99% of the way through the plan execution, in that case going off and replanning and starting execution all over again might throw away too much hard work. It does seem like a good idea for things that could be known at executor start-up, I guess this would likely include LEFT JOIN removals using deferrable unique indexes... Currently these indexes are ignored by the current join removal code as they mightn't be unique until the transaction finishes. I'm imagining this being implemented by passing the planner a set of flags which are assumptions that the planner is allowed to make... During the planner's work, if it generated a plan which required this assumption to be met, then it could set this flag in the plan somewhere which would force the executor to check this at executor init. If the executor found any required flag's conditions to be not met, then the executor would request a new plan passing all the original flags, minus the ones that the conditions have been broken on. I see this is quite a fundamental change to how things currently work and it could cause planning to take place during the execution of PREPAREd statements, which might not impress people too much, but it would certainly fix the weird anomalies that I'm currently facing by trimming the plan at executor startup. e.g left over Sort nodes after a MergeJoin was removed. It would be interesting to hear Tom's opinion on this. Regards David Rowley
Re: [HACKERS] Removing INNER JOINs
David Rowley dgrowle...@gmail.com writes: I see this is quite a fundamental change to how things currently work and it could cause planning to take place during the execution of PREPAREd statements, which might not impress people too much, but it would certainly fix the weird anomalies that I'm currently facing by trimming the plan at executor startup. e.g left over Sort nodes after a MergeJoin was removed. It would be interesting to hear Tom's opinion on this. TBH I don't like this patch at all even in its current form, let alone a form that's several times more invasive. I do not think there is a big enough use-case to justify such an ad-hoc and fundamentally different way of doing things. I think it's probably buggy as can be --- one thing that definitely is a huge bug is that it modifies the plan tree in-place, ignoring the rule that the plan tree is read-only to the executor. Another question is what effect this has on EXPLAIN; there's basically no way you can avoid lying to the user about what's going to happen at runtime. One idea you might think about to ameliorate those two objections is two separate plan trees underneath an AlternativeSubPlan or similar kind of node. At a more macro level, there's the issue of how can the planner possibly make intelligent decisions at other levels of the join tree when it doesn't know the cost of this join. For that matter there's nothing particularly driving the planner to arrange the tree so that the optimization is possible at all. Bottom line, given all the restrictions on whether the optimization can happen, I have very little enthusiasm for the whole idea. I do not think the benefit will be big enough to justify the amount of mess this will introduce. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing INNER JOINs
On 1 December 2014 at 06:51, Tom Lane t...@sss.pgh.pa.us wrote: David Rowley dgrowle...@gmail.com writes: I see this is quite a fundamental change to how things currently work and it could cause planning to take place during the execution of PREPAREd statements, which might not impress people too much, but it would certainly fix the weird anomalies that I'm currently facing by trimming the plan at executor startup. e.g left over Sort nodes after a MergeJoin was removed. It would be interesting to hear Tom's opinion on this. Another question is what effect this has on EXPLAIN; there's basically no way you can avoid lying to the user about what's going to happen at runtime. One of us must be missing something here. As far as I see it, there are no lies told, the EXPLAIN shows exactly the plan that will be executed. All of the regression tests I've added rely on this. Regards David Rowley
[HACKERS] Removing INNER JOINs
Hi, Starting a new thread which continues on from http://www.postgresql.org/message-id/caaphdvoec8ygwoahvsri-84en2k0tnh6gpxp1k59y9juc1w...@mail.gmail.com To give a brief summary for any new readers: The attached patch allows for INNER JOINed relations to be removed from the plan, providing none of the columns are used for anything, and a foreign key exists which proves that a record must exist in the table being removed which matches the join condition: Example: test=# create table b (id int primary key); CREATE TABLE test=# create table a (id int primary key, b_id int not null references b(id)); CREATE TABLE test=# explain (costs off) select a.* from a inner join b on a.b_id = b.id; QUERY PLAN --- Seq Scan on a This has worked for a few years now for LEFT JOINs, so this patch is just extending the joins types that can be removed. This optimisation should prove to be quite useful for views in which a subset of its columns are queried. The attached is an updated patch which fixes a conflict from a recent commit and also fixes a bug where join removals did not properly work for PREPAREd statements. I'm looking for a bit of feedback around the method I'm using to prune the redundant plan nodes out of the plan tree at executor startup. Particularly around not stripping the Sort nodes out from below a merge join, even if the sort order is no longer required due to the merge join node being removed. This potentially could leave the plan suboptimal when compared to a plan that the planner could generate when the removed relation was never asked for in the first place. There are also other cases such as MergeJoins performing btree index scans in order to obtain ordered results for a MergeJoin that would be better executed as a SeqScan when the MergeJoin can be removed. Perhaps some costs could be adjusted at planning time when there's a possibility that joins could be removed at execution time, although I'm not quite sure about this as it risks generating a poor plan in the case when the joins cannot be removed. I currently can't see much of a way around these cases, but in both cases removing the join should prove to be a win, though just perhaps not with the most optimal of plans. There are some more details around the reasons behind doing this weird executor startup plan pruning around here: http://www.postgresql.org/message-id/20141006145957.ga20...@awork2.anarazel.de Comments are most welcome Regards David Rowley inner_join_removals_2014-11-29_be69869.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing Inner Joins
Sent from my iPad On 10-Jul-2013, at 13:11, Hannu Krosing ha...@2ndquadrant.com wrote: On 07/10/2013 09:18 AM, Atri Sharma wrote: Can you please post an example of such a join removal? I mean a query before and after the removal. Thanks, Courtesy Robert Haas: SELECT foo.x, foo.y, foo.z FROM foo WHERE foo.x = bar.x Conditions: 1) foo.x is not null. I guess that this is also not needed. you can just remove rows where foo.x is null That is, replace the join with foo.x is not null 2) foo (x) is a foreign key referencing bar (x). We can ignore bar completely in this case i.e. avoid scanning bar. Regards, Atri -- Regards, Atri l'apprenant I discussed with RhodiumToad and was exploring potential design methods with which we can solve the above problem. My focus is to add support for foreign key detection in planner and allow planner to make decisions based on it. It wouldn't be too much of a cost to maintain the foreign key column and the referenced table. The main issue, as pointed out by RHodiumToad is primarily that, between the generation of the plan, which is made with accordance to the foreign key presence, and the execution of the plan, we may get into an inconsistent state when the corresponding row is deleted or constraints are changed and fk trigger has not yet run and detected those changes. I was thinking of row level locks,which are done by the fk trigger.Is there any way we can modify the fk trigger to forcibly run? Or add an 'looked at' bit, which is reset when a table/row is changed, and set by the fk trigger when it runs on that table? I am just thinking wild here, please let me know your thoughts, feedback and ideas. Regards, Atri -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing Inner Joins
On 07/14/2013 06:10 PM, Atri Sharma wrote: Sent from my iPad On 10-Jul-2013, at 13:11, Hannu Krosing ha...@2ndquadrant.com wrote: On 07/10/2013 09:18 AM, Atri Sharma wrote: Can you please post an example of such a join removal? I mean a query before and after the removal. Thanks, Courtesy Robert Haas: SELECT foo.x, foo.y, foo.z FROM foo WHERE foo.x = bar.x Conditions: 1) foo.x is not null. I guess that this is also not needed. you can just remove rows where foo.x is null That is, replace the join with foo.x is not null 2) foo (x) is a foreign key referencing bar (x). We can ignore bar completely in this case i.e. avoid scanning bar. Regards, Atri -- Regards, Atri l'apprenant I discussed with RhodiumToad and was exploring potential design methods with which we can solve the above problem. My focus is to add support for foreign key detection in planner and allow planner to make decisions based on it. It wouldn't be too much of a cost to maintain the foreign key column and the referenced table. The main issue, as pointed out by RHodiumToad is primarily that, between the generation of the plan, which is made with accordance to the foreign key presence, and the execution of the plan, we may get into an inconsistent state when the corresponding row is deleted or constraints are changed and fk trigger has not yet run and detected those changes. Is this not all transactional and taken care of by MVCC ? That is, the problem can only happen for prepared plans, which need to have invalidation in case of underlaying DDL / schema changes anyway ? Or are you worried about the case where the FK constraint is delayed and thus the plan can be invalid between the change and running of FK trigger in the same transaction ? I was thinking of row level locks,which are done by the fk trigger.Is there any way we can modify the fk trigger to forcibly run? Or add an 'looked at' bit, which is reset when a table/row is changed, and set by the fk trigger when it runs on that table? I am just thinking wild here, please let me know your thoughts, feedback and ideas. Regards, Atri -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing Inner Joins
Sent from my iPad On 14-Jul-2013, at 22:12, Hannu Krosing ha...@2ndquadrant.com wrote: On 07/14/2013 06:10 PM, Atri Sharma wrote: Sent from my iPad On 10-Jul-2013, at 13:11, Hannu Krosing ha...@2ndquadrant.com wrote: On 07/10/2013 09:18 AM, Atri Sharma wrote: Can you please post an example of such a join removal? I mean a query before and after the removal. Thanks, Courtesy Robert Haas: SELECT foo.x, foo.y, foo.z FROM foo WHERE foo.x = bar.x Conditions: 1) foo.x is not null. I guess that this is also not needed. you can just remove rows where foo.x is null That is, replace the join with foo.x is not null 2) foo (x) is a foreign key referencing bar (x). We can ignore bar completely in this case i.e. avoid scanning bar. Regards, Atri -- Regards, Atri l'apprenant I discussed with RhodiumToad and was exploring potential design methods with which we can solve the above problem. My focus is to add support for foreign key detection in planner and allow planner to make decisions based on it. It wouldn't be too much of a cost to maintain the foreign key column and the referenced table. The main issue, as pointed out by RHodiumToad is primarily that, between the generation of the plan, which is made with accordance to the foreign key presence, and the execution of the plan, we may get into an inconsistent state when the corresponding row is deleted or constraints are changed and fk trigger has not yet run and detected those changes. Is this not all transactional and taken care of by MVCC ? That is, the problem can only happen for prepared plans, which need to have invalidation in case of underlaying DDL / schema changes anyway ? Or are you worried about the case where the FK constraint is delayed and thus the plan can be invalid between the change and running of FK trigger in the same transaction ? Yes, that is precisely what I am concerned about.Thanks for wording it so nicely! Regards, Atri -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing Inner Joins
On 07/10/2013 07:28 AM, Atri Sharma wrote: I am not sure if the part you mentioned is inline with the case I am talking about. Can you please post an example of such a join removal? I mean a query before and after the removal. Thanks, //Tony -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing Inner Joins
Can you please post an example of such a join removal? I mean a query before and after the removal. Thanks, Courtesy Robert Haas: SELECT foo.x, foo.y, foo.z FROM foo WHERE foo.x = bar.x Conditions: 1) foo.x is not null. 2) foo (x) is a foreign key referencing bar (x). We can ignore bar completely in this case i.e. avoid scanning bar. Regards, Atri -- Regards, Atri l'apprenant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing Inner Joins
On 07/10/2013 09:18 AM, Atri Sharma wrote: Can you please post an example of such a join removal? I mean a query before and after the removal. Thanks, Courtesy Robert Haas: SELECT foo.x, foo.y, foo.z FROM foo WHERE foo.x = bar.x Conditions: 1) foo.x is not null. I guess that this is also not needed. you can just remove rows where foo.x is null That is, replace the join with foo.x is not null 2) foo (x) is a foreign key referencing bar (x). We can ignore bar completely in this case i.e. avoid scanning bar. Regards, Atri -- Regards, Atri l'apprenant -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing Inner Joins
On Wed, Jul 10, 2013 at 1:11 PM, Hannu Krosing ha...@2ndquadrant.com wrote: On 07/10/2013 09:18 AM, Atri Sharma wrote: Can you please post an example of such a join removal? I mean a query before and after the removal. Thanks, Courtesy Robert Haas: SELECT foo.x, foo.y, foo.z FROM foo WHERE foo.x = bar.x Conditions: 1) foo.x is not null. I guess that this is also not needed. you can just remove rows where foo.x is null That is, replace the join with foo.x is not null Yeah, sounds good. We should explore it further. Regards, Atri -- Regards, Atri l'apprenant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Removing Inner Joins
Hi all, I was reading about the plans to add functionality to the planner to remove redundant inner joins where there is no member of the inner relation present in the target list and the inner relation is only used for the join clause. Also, we have a foreign key in the outer relation to the inner relation. Where are we with that functionality atm? Do we have plans to move forward? Also, how difficult is it to go ahead with it? Regards, Atri -- Regards, Atri l'apprenant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing Inner Joins
On Mon, Jul 8, 2013 at 11:20 PM, Atri Sharma atri.j...@gmail.com wrote: Where are we with that functionality atm? Do we have plans to move forward? PostgreSQL has had this capability for some time. See: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=488d70ab46311386801c10691196ec8d755f2283 -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing Inner Joins
On 2013-07-08 23:31:15 -0700, Peter Geoghegan wrote: On Mon, Jul 8, 2013 at 11:20 PM, Atri Sharma atri.j...@gmail.com wrote: Where are we with that functionality atm? Do we have plans to move forward? PostgreSQL has had this capability for some time. See: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=488d70ab46311386801c10691196ec8d755f2283 That's for outer joins tho. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing Inner Joins
On Tue, Jul 9, 2013 at 12:01 PM, Peter Geoghegan p...@heroku.com wrote: On Mon, Jul 8, 2013 at 11:20 PM, Atri Sharma atri.j...@gmail.com wrote: Where are we with that functionality atm? Do we have plans to move forward? PostgreSQL has had this capability for some time. See: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=488d70ab46311386801c10691196ec8d755f2283 Thanks, but doesn't that commit refer to left join removal? I was referring to inner join removals. Could you point me to a resource for that please? I was looking at the mail threads for that earlier. Regards, Atri -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing Inner Joins
On Mon, Jul 8, 2013 at 11:33 PM, Andres Freund and...@2ndquadrant.com wrote: That's for outer joins tho. Oh, right - I spoke too soon. Looks like I missed the whole discussion on inner join removal. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing Inner Joins
My main point here is researching how difficult it is to add functionality in the planner to allow it to to detect and make decisions on foreign keys present in the outer relation. I think that if this is added, rest of the work would be much easier. I amy be completely wrong,though. Thoughts/Comments? Regards, Atri Regards, Atri l'apprenant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing Inner Joins
AFAIK, There is code to remove the redundant relations (joins too are relations). But I don't remember exactly where it is. Start looking at query_planner(). The removal of relations should happen before actually planning the joins. On Tue, Jul 9, 2013 at 12:21 PM, Atri Sharma atri.j...@gmail.com wrote: My main point here is researching how difficult it is to add functionality in the planner to allow it to to detect and make decisions on foreign keys present in the outer relation. I think that if this is added, rest of the work would be much easier. I amy be completely wrong,though. Thoughts/Comments? Regards, Atri Regards, Atri l'apprenant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Best Wishes, Ashutosh Bapat EntepriseDB Corporation The Postgres Database Company
Re: [HACKERS] Removing Inner Joins
On Wed, Jul 10, 2013 at 8:29 AM, Ashutosh Bapat ashutosh.ba...@enterprisedb.com wrote: AFAIK, There is code to remove the redundant relations (joins too are relations). But I don't remember exactly where it is. Start looking at query_planner(). The removal of relations should happen before actually planning the joins. Thanks for your reply. I am not sure if the part you mentioned is inline with the case I am talking about. The specific case I mentioned does not seem to have been implemented yet, and I was researching the roadblocks to it, hence asked on the list. Thanks and Regards, Atri -- Regards, Atri l'apprenant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
