Optimizing database queries using reusable subqueries

A non-transitory computer-readable storage medium storing computer-executable code for optimizing queries of a database system, the code, when executed by a processor, causing the processor to:

• receive a database query for optimization, the database query comprising a first subquery and a second subquery, the first subquery specifying a first where clause comprising a first condition and the second subquery specifying a second where clause comprising a second condition, wherein the first where clause is distinct from the second where clause and wherein the first condition evaluates to true for a first set of input rows and the second condition evaluates to true for a second set of input rows;
• compare the first subquery and the second subquery based on input tables processed by each of the first and second subqueries;
• responsive to determining that the first subquery and the second subquery match based on the comparison:
• generate a first database query specifying a where clause comprising a condition that evaluates to true for a superset of the first set of input rows and the second set of input rows;
• generate a statement comprising the first database query, the statement storing result of execution of the first database query in a result table;
• generate a first expression equivalent to the first subquery and a second expression equivalent to the second subquery, the first and the second expressions based on the result table;
• modify the database query to use the result table, the modifying comprising, replacing the first subquery with the first expression and the second subquery with the second expression;
• and
• replace an execution of the database query with an execution of the statement followed by an execution of the modified database query.

A computer-implemented system for optimizing queries, the system comprising:

• a computer processor;
• and
• a non-transitory computer-readable storage medium storing computer program modules configured to execute on the computer processor, the computer program modules comprising:
• an optimizer module configured to:
• receive a database query for optimization, the database query comprising a first subquery and a second subquery, the first subquery specifying a first where clause comprising a first condition and the second subquery specifying a second where clause comprising a second condition, wherein the first where clause is distinct from the second where clause and wherein the first condition evaluates to true for a first set of input rows and the second condition evaluates to true for a second set of input rows;
• compare the first subquery and the second subquery based on input tables processed by each of the first and second subqueries;
• responsive to determining that the first subquery and the second subquery match based on the comparison:
• generate a first database query specifying a where clause comprising a condition that evaluates to true for a superset of the first set of input rows and the second set of input rows;
• generate a statement comprising the first database query, the statement storing result of execution of the first database query in a result table;
• generate a first expression equivalent to the first subquery and a second expression equivalent to the second subquery, the first and the second expressions based on the result table;
• modify the database query to use the result table, the modifying comprising, replacing the first subquery with the first expression and the second subquery with the second expression;
• and
• replace an execution of the database query with an execution of the statement followed by an execution of the modified database query.