Modular query optimizer
View Patent ↗Computer programs encoded on a computer storage medium provide for a modular query optimizer. In one aspect, a computer program product includes selecting one or more projections from a set of projections for each table in a database query wherein each of the selected projections for the table has leads to an estimated lower execution cost for the query as compared to non-selected projections; generating join orders for the query based on data distribution of one or more of the selected projections among sites in a computer network wherein the join orders reflect different combinations of data distribution operations applied to the output of one or more of the query's joins; and selecting a join order from the join orders based on evaluation of the join orders using a cost model.
1. A computer-implemented method comprising:
for each table in a database query, selecting one or more projections that reduce an estimated cost for executing the query for the table, based on a segmentation or sort order of the selected projections;
based on a data distribution of one or more of the selected projections among sites in a computer network, generating, for the query, possible join orders that represent different combinations of data distribution operations applied to the outputs of one or more of the query's joins; and
evaluating the join orders based on a cost model.
2. The method of claim 1 wherein the estimated execution cost for the query is based on whether the selected projections allow for one or more local joins in an execution plan for the query.
3. The method of claim 1 wherein, in a join order, many-to-one joins occur before many-to-many joins.
4. The method of claim 1 wherein, in a join order, more selective joins occur before less selective joins.
5. The method of claim 1 wherein, a local join in a join order that occurs before a subsequent join that would destroy the locality of the local join is given the same order as the subsequent join.
6. The method of claim 1 wherein a data distribution operation is at least one of: re-segmentation according to a join key, broadcast, and filtering on a join key.
7. The method of claim 1 also comprising selecting a join order based on the evaluation.
8. The method of claim 7 in which selecting a join order further comprises selecting the join order with the lowest cost.
9. A computer program product, encoded on a computer-readable storage medium, including instructions operable to cause data processing apparatus to perform operations comprising:
for each table in a database query, selecting one or more projections that reduce an estimated cost for executing the query for the table, based on a segmentation or sort order of the selected projections;
based on a data distribution of one or more of the selected projections among sites in a computer network, generating, for the query, possible join orders that represent different combinations of data distribution operations applied to the outputs of one or more of the query's joins; and
evaluating the join orders based on a cost model.
10. The program product of claim 9 wherein the estimated execution cost for the query is based on whether the selected projections allow for one or more local joins in an execution plan for the query.
11. The program product of claim 9 wherein, in a join order, many-to-one joins occur before many-to-many joins.
12. The program product of claim 9 wherein, in a join order, more selective joins occur before less selective joins.
13. The program product of claim 9 wherein, a local join in a join order that occurs before a subsequent join that would destroy the locality of the local join is given the same order as the subsequent join.
14. The program product of claim 9 wherein a data distribution operation is at least one of: re-segmentation according to a join key, broadcast, and filtering on a join key.
15. The program product of claim 9 wherein the operations also comprise selecting a join order based on the evaluation.
16. The program product of claim 15 in which selecting a join order further comprises selecting the join order with the lowest cost.