8/25/2023 0 Comments Postgres query planThis is why the query planner decides to do a Hash Join, creating the Hash structure on the result of the previous join, to get fast access to this inner row source. But the SQL executor can create a temporary one for the duration of the query. However, when the result of another join is the inner table, there's no permanent structure to access to specific rows in it. As the inner loop of a Nested loop is executed many times, it is efficient only with a permanent range or point access structure, sorted or hashed, like an index. When table_c was the inner table, the query planner had chosen a Nested Loop because there's a fast access in the inner loop, with the primary key index on table_c. With those two tables, this is as simple as defining (b a) instead of b a and this is put within the Leading() arguments: In order to define the join direction, you need more parentheses to order each join pairs, where the left one is the outer and the right one the inner. This choice is important because the efficiency of a join method, like Nested Loop, depends on the number of rows of the outer table, and the access path to the inner one. For each join, there are two possible directions, defining which one is the outer (or left, on top) or the inner (or right, on second, and last, position). Each one is actually a table or index scan, or the result from a previous join. In any query, the tables will be joined two by two, visible as two child operations on the join node displayed by the explain tree. But the implementation of the join method (Nested Loop, Merge Join, Hash Join) is not. There's no error reported in the verbose log (see below how to enable it) because the hint was used, even if the intention of the user was different.įrom a relational point of view, the join operation is commutative. This Leading syntax just says "start by joining b and a", which is useless here because there are no other solution with two tables only. ![]() The reason is that the Leading hint with a simple list of tables defines in which order the query planner will consider each table, but doesn't define the join direction, which is still left to the query planner estimations. Enter fullscreen mode Exit fullscreen modeĪs you can see, nothing has changed in my plan.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |