Reducing the domain of a subquery by retrieving constraints from the outer query

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.

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.