Change #4964

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