Change #4964
Category | None |
Changed by | Mike Rylander <mrylander | @gmail.com>
Changed at | Wed 08 Nov 2017 15:54:31 |
Repository | git://git.evergreen-ils.org/Evergreen.git |
Project | Evergreen |
Branch | master |
Revision | edd28a3cb95bb2d8464bb1248abde7763ac753b9 |
Comments
LP#1527731: Allow specified join order With this commit we now support user-defined join order in cstore and friends. Previously, because the join structure of oils_sql beyond the specification of a single table was only allowed to be represented as a JSON object, it was subject to potential hash key reordering -- thanks, Perl. By supporting an intervening array layer, one can now specify the exact join order of the tables in a join tree. For example, given the following JSON object passing through a modern Perl 5 interpreter as a nested hash: {select : {acp:['id'], acn:['record'], acpl:['name'] }, from : {acp: {acn:{filter:{record:12345}}, acpl:null } } } the FROM clause of the query may end up as: FROM acp JOIN acn ON (acp.call_number = acn.id AND acn.record = 12345) JOIN acpl ON (acp.location = acpl.id) Or as: FROM acp JOIN acpl ON (acp.location = acpl.id) JOIN acn ON (acp.call_number = acn.id AND acn.record = 12345) In some situations, the join order will matter either to the semantics of the query plan, or to its performance. The following example of the newly supported syntax illustrates how to specify join order: {select : {acp:['id'], acn:['record'], acpl:['name'] }, from : {acp:[ {acn:{filter:{record:12345}}}, 'acpl' ]} } And the only FROM clause the can be generated is: FROM acp JOIN acn ON (acp.call_number = acn.id AND acn.record = 12345) JOIN acpl ON (acp.location = acpl.id) Why is this important --------------------- While Postgres' planner is very smart, a join tree with many tables may create a plan search space that is simply too large to be tested effeciently. In such cases, Postgres will do its best to find a good plan for the query using its GEQO algorithm. Often, a DBA or developer has enough understanding of the expected relative data sizes involved to give Postgres a leg up by specifying a join order that improves the planner's chances of generating an optimal plan. Signed-off-by: Mike Rylander <mrylander@gmail.com> Signed-off-by: Jason Stephenson <jason@sigio.com> Signed-off-by: Galen Charlton <gmc@equinoxinitiative.org>
Changed files
- Open-ILS/src/c-apps/oils_sql.c
- Open-ILS/src/perlmods/lib/OpenILS/Application/AppUtils.pm
- Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Record.pm