Reordering predicates for disjunction of conjunctions database queries
The present disclosure involves systems, software, and computer implemented methods for reordering predicates for disjunction of conjunction database queries. One example method includes receiving a disjunction of conjunctions query including a set of parameter values. An estimated selectivity of each inner predicate of the disjunction of conjunctions query can be determined, based on the parameter values. Estimated selectivities of inner predicates of the disjunction of conjunctions query that reference a database column can be determined, to generate an aggregated estimated selectivity for the database column. An evaluation order for evaluating inner predicates for the disjunction of conjunctions query can be determined, based on aggregated estimated selectivities of respective database columns. The inner predicates are evaluated, during execution of the query, based on the evaluation order and the set of parameter values.
1 . A computer-implemented method comprising:
receiving, at a database system, a query for a database table, wherein the query includes a set of parameter values;
determining that the query corresponds to a disjunction of conjunctions query;
identifying, by a runtime execution engine of the database system, from a pre-compiled query plan, a matrix of the runtime execution engine that represents the disjunction of conjunctions query that includes a matrix row for each conjunction included in the query and a matrix column for each database column referenced in an inner predicate in a conjunction;
estimating, for each database column referenced in an inner predicate in a conjunction and based on the set of parameter values, an estimated selectivity of each inner predicate of the disjunction of conjunctions query that references the database column;
aggregating, for each database column referenced in an inner predicate in a conjunction, estimated selectivities of inner predicates of the disjunction of conjunctions query that reference the database column, to generate an aggregated estimated selectivity for the database column;
determining, based on aggregated estimated selectivities of database columns referenced in an inner predicate in a conjunction, a matrix column evaluation order for evaluating inner predicates in the matrix for the disjunction of conjunctions query; and
evaluating the disjunction of conjunctions query by evaluating the inner predicates in matrix columns of the matrix based on the matrix column evaluation order and the set of parameter values.
2 . The computer-implemented method of claim 1 , wherein the disjunction of conjunctions query corresponds to a for-all-entries query.
3 . The computer-implemented method of claim 1 , wherein the disjunction of conjunctions query comprises a combination of conjunction query expressions joined by multiple disjunction operators.
4 . The computer-implemented method of claim 1 , wherein the estimated selectivity of a first inner predicate corresponds to a percentage of table rows of the database table that are estimated to match the inner predicate.
5 . The computer-implemented method of claim 1 , wherein the estimated selectivity of a first inner predicate is determined based on locating a first parameter value in frequency statistic metadata for the database table.
6 . The computer-implemented method of claim 1 , wherein the estimated selectivity of a first inner predicate is determined based on sampling the database table and determining how many sampled rows of the database table match the inner predicate with respect to a first parameter value of the query.
7 . The computer-implemented method of claim 1 , wherein the estimated selectivity of a first inner predicate is determined by:
determining that a column referenced in the inner predicate stores unique values; and
determining the estimated selectivity of the inner predicate by dividing a value of one by a row count of the database table.
8 . The computer-implemented method of claim 1 , wherein the aggregated estimated selectivity for the database column is a sum or a mean average of estimated selectivities of inner predicates of the disjunction of conjunctions query that reference the database column.
9 . The computer-implemented method of claim 1 , wherein determining the matrix column evaluation order comprises placing matrix columns that correspond to database columns with lower-valued aggregated estimated selectivities earlier in the matrix column evaluation order than matrix columns that correspond to database columns that have higher-valued aggregated estimated selectivities.
10 . The computer-implemented method of claim 1 , further comprising:
identifying index information for the database table; and
wherein determining the matrix column evaluation order comprises placing a first matrix column that corresponds to an indexed database column earlier in the matrix column evaluation order than a second matrix column that corresponds to a non-indexed database column, wherein the indexed database column has a higher-valued aggregated estimated selectivity than the non-indexed database column.
11 . A system comprising:
one or more computers; and
a computer-readable medium coupled to the one or more computers having instructions stored thereon which, when executed by the one or more computers, cause the one or more computers to perform operations comprising:
receiving, at a database system, a query for a database table, wherein the query includes a set of parameter values;
determining that the query corresponds to a disjunction of conjunctions query;
identifying, by a runtime execution engine of the database system, from a pre-compiled query plan, a matrix of the runtime execution engine that represents the disjunction of conjunctions query that includes a matrix row for each conjunction included in the query and a matrix column for each database column referenced in an inner predicate in a conjunction;
estimating, for each database column referenced in an inner predicate in a conjunction and based on the set of parameter values, an estimated selectivity of each inner predicate of the disjunction of conjunctions query that references the database column;
aggregating, for each database column referenced in an inner predicate in a conjunction, estimated selectivities of inner predicates of the disjunction of conjunctions query that reference the database column, to generate an aggregated estimated selectivity for the database column;
determining, based on aggregated estimated selectivities of database columns referenced in an inner predicate in a conjunction, a matrix column evaluation order for evaluating inner predicates in the matrix for the disjunction of conjunctions query; and
evaluating the disjunction of conjunctions query by evaluating the inner predicates in matrix columns of the matrix based on the matrix column evaluation order and the set of parameter values.
12 . The system of claim 11 , wherein the disjunction of conjunctions query corresponds to a for-all-entries query.
13 . The system of claim 11 , wherein the disjunction of conjunctions query comprises a combination of conjunction query expressions joined by multiple disjunction operators.
14 . The system of claim 11 , wherein the estimated selectivity of a first inner predicate corresponds to a percentage of table rows of the database table that are estimated to match the inner predicate.
15 . The system of claim 11 , wherein the estimated selectivity of a first inner predicate is determined based on locating a first parameter value in frequency statistic metadata for the database table.
16 . A computer program product encoded on a non-transitory storage medium, the product comprising non-transitory, computer readable instructions for causing one or more processors to perform operations comprising:
receiving, at a database system, a query for a database table, wherein the query includes a set of parameter values;
determining that the query corresponds to a disjunction of conjunctions query;
identifying, by a runtime execution engine of the database system, from a pre-compiled query plan, a matrix of the runtime execution engine that represents the disjunction of conjunctions query that includes a matrix row for each conjunction included in the query and a matrix column for each database column referenced in an inner predicate in a conjunction;
estimating, for each database column referenced in an inner predicate in a conjunction and based on the set of parameter values, an estimated selectivity of each inner predicate of the disjunction of conjunctions query that references the database column;
aggregating, for each database column referenced in an inner predicate in a conjunction, estimated selectivities of inner predicates of the disjunction of conjunctions query that reference the database column, to generate an aggregated estimated selectivity for the database column;
determining, based on aggregated estimated selectivities of database columns referenced in an inner predicate in a conjunction, a matrix column evaluation order for evaluating inner predicates in the matrix for the disjunction of conjunctions query; and
evaluating the disjunction of conjunctions query by evaluating the inner predicates in matrix columns of the matrix based on the matrix column evaluation order and the set of parameter values.
17 . The computer program product of claim 16 , wherein the disjunction of conjunctions query corresponds to a for-all-entries query.
18 . The computer program product of claim 16 , wherein the disjunction of conjunctions query comprises a combination of conjunction query expressions joined by multiple disjunction operators.
19 . The computer program product of claim 16 , wherein the estimated selectivity of a first inner predicate corresponds to a percentage of table rows of the database table that are estimated to match the inner predicate.
20 . The computer program product of claim 16 , wherein the estimated selectivity of a first inner predicate is determined based on locating a first parameter value in frequency statistic metadata for the database table.