Reducing the domain of a subquery by retrieving constraints from the outer query
A database engine receives a human-readable database query that includes a subquery, and parses the database query to build an operator tree. The operator tree includes a subtree corresponding to the subquery. The database engine estimates the number of rows that will accessed when the subtree is executed and estimates the fraction of the cardinality of rows that will be filtered out by subsequent operations in the operator tree. In accordance with a determination that the estimated fraction exceeds a first threshold, the database engine inserts a domain constraint into the subtree that restricts rows retrieved by execution of the subtree, thereby forming a modified operator tree. The database engine executes the modified operator tree to form a final result set corresponding to the database query and returns the final result set.
1. A database engine, comprising:
one or more computing devices, each having one or more processors and memory, wherein the memory stores one or more programs configured for execution by the one or more processors, the one or more programs comprising instructions for:
receiving a human-readable database query that includes a subquery;
parsing the database query to build an operator tree, which includes a subtree corresponding to the subquery;
estimating a cardinality of rows in database tables specified in the subtree;
estimating a fraction of the estimated cardinality of rows that do not satisfy a filter condition specified in one or more subsequent operations in the operator tree;
in accordance with a determination that the estimated fraction exceeds a first threshold, inserting, into the subtree, a domain constraint that includes an early-probe operator that specifies comparing rows generated from execution of the subtree to a hash table of a second subtree in the operator tree, the domain constraint corresponding to the filter condition, thereby forming a modified operator tree in which execution of the subtree restricts rows retrieved according to the filter condition;
executing the modified operator tree to form a final result set corresponding to the database query; and
returning the final result set.
2. The database engine of claim 1 , wherein inserting the domain constraint into the subtree is further in accordance with a determination that the estimated cardinality of rows exceeds a second threshold.
3. The database engine of claim 1 , wherein estimating the cardinality of rows in database tables specified in the subtree comprises estimating a number of rows in an intermediate result set that will be created by execution of the subtree.
4. The database engine of claim 3 , wherein estimating the fraction of the estimated cardinality of rows that do not satisfy the filter condition comprises determining a number of rows in the intermediate result set of the subtree that will not be used to form the final result set.
5. The database engine of claim 1 , wherein the second subtree is more than one operator ahead of the subtree in the operator tree.
6. The database engine of claim 1 , wherein estimating the fraction of the estimated cardinality of rows that do not satisfy the filter condition comprises:
selecting a sample of rows from the database tables specified in the subtree;
executing at least a portion of the operator tree, including the subtree and operators in the operator tree that specify the filter condition, using the selected sample of rows; and
in accordance with the execution using the selected sample of rows, determining a number of the sample rows that are filtered out in the execution of the filter condition.
7. The database engine of claim 1 , wherein executing the modified operator tree creates, for the subquery, an intermediate result set whose cardinality is substantially less than the estimated cardinality of rows in database tables specified in the subtree.
8. The database engine of claim 1 , further comprising, in accordance with a determination that the estimated fraction does not exceed the first threshold, forgoing insertion of the domain constraint into the subtree.
9. The database engine of claim 1 , further comprising, in accordance with a determination that the estimated cardinality does not exceed a second threshold, forgoing insertion of the domain constraint into the subtree.
10. The database engine of claim 1 , wherein estimating the cardinality of rows in database tables specified in the subtree comprises:
identifying a plurality of database tables specified in the subquery; and
determining a respective number of rows in each of the plurality of database tables according to statistics stored at the database.
11. A method of retrieving data from a database, comprising:
at a computer system having one or more computing devices, each computing device having one or more processors and memory storing one or more programs configured for execution by the one or more processors:
receiving a human-readable database query that includes a subquery;
parsing the database query to build an operator tree, which includes a subtree corresponding to the subquery;
estimating a cardinality of rows in database tables specified in the subtree;
estimating a fraction of the estimated cardinality of rows that do not satisfy a filter condition specified in one or more subsequent operations in the operator tree;
in accordance with a determination that the estimated fraction exceeds a first threshold, inserting, into the subtree, a domain constraint that includes an early-probe operator that specifies comparing rows generated from execution of the subtree to a hash table of a second subtree in the operator tree, the domain constraint corresponding to the filter condition, thereby forming a modified operator tree in which execution of the subtree restricts rows retrieved according to the filter condition;
executing the modified operator tree to form a final result set corresponding to the database query; and
returning the final result set.
12. The method of claim 11 , wherein inserting the domain constraint into the subtree is further in accordance with a determination that the estimated cardinality of rows exceeds a second threshold.
13. The method of claim 11 , wherein estimating the cardinality of rows in database tables specified in the subtree comprises estimating a number of rows in an intermediate result set that will be created by execution of the subtree.
14. The method of claim 13 , wherein estimating the fraction of the estimated cardinality of rows that do not satisfy the filter condition comprises determining a number of rows in the intermediate result set of the subtree that will not be used to form the final result set.
15. The method of claim 11 , wherein estimating the fraction of the estimated cardinality of rows that do not satisfy the filter condition comprises:
selecting a sample of rows from the database tables specified in the subtree;
executing at least a portion of the operator tree, including the subtree and operators in the operator tree that specify the filter condition, using the selected sample of rows; and
in accordance with the execution using the selected sample of rows, determining a number of the sample rows that are filtered out in the execution of the filter condition.
16. The method of claim 11 , wherein executing the modified operator tree creates, for the subquery, an intermediate result set whose cardinality is substantially less than the estimated cardinality of rows in database tables specified in the subtree.
17. The method of claim 11 , wherein estimating the cardinality of rows in database tables specified in the subtree comprises:
identifying a plurality of database tables specified in the subquery; and
determining a respective number of rows in each of the plurality of database tables according to statistics stored at the database.
18. A non-transitory computer readable storage medium storing one or more programs configured for execution by a computer system having one or more processors and memory, the one or more programs comprising instructions for:
receiving a human-readable database query that includes a subquery;
parsing the database query to build an operator tree, which includes a subtree corresponding to the subquery;
estimating a cardinality of rows in database tables specified in the subtree;
estimating a fraction of the estimated cardinality of rows that do not satisfy a filter condition specified in one or more subsequent operations in the operator tree;
in accordance with a determination that the estimated fraction exceeds a first threshold, inserting, into the subtree, a domain constraint that includes an early-probe operator that specifies comparing rows generated from execution of the subtree to a hash table of a second subtree in the operator tree, the domain constraint corresponding to the filter condition, thereby forming a modified operator tree in which execution of the subtree restricts rows retrieved according to the filter condition;
executing the modified operator tree to form a final result set corresponding to the database query; and
returning the final result set.