Partition granular selectivity estimation for predicates
A query engine can use partition-granular level statistics to optimize query performance. A query can reference a table with a plurality of partitions and include a predicate. A partition-granular selectivity estimate for the predicate can be generated based on statistics stored regarding the plurality of partitions of the table. A query plan can be generated based on partition-granular selectivity estimate to optimize query processing.
1 . A method comprising:
receiving a query including at least one predicate referencing a first table and a second table stored in a network-based data system, the first table comprising a first set of partitions and the second table comprising a second set of partitions;
retrieving statistics regarding the first set of partitions in the first table and the second set of partitions in the second table;
generating a partition-granular selectivity estimate for the at least one predicate based on the retrieved statistics;
generating a join operation in a query plan, the join operation comprising a selection of the first table as a build table and the second table as a probing table based on the partition-granular selectivity estimate, a first estimate for relevant rows in the build table first table being lower than a second estimate for relevant rows in the second table based on the partition-granular selectivity estimate; and
executing the query plan to generate results of the query comprising creating an index based on the build table for determining matches with the probing table to execute the join operation.
2 . The method of claim 1 , wherein respective partitions of the first set of partitions include a group of rows and at least one partition of the first set of partitions being created in response to new data being written to the first table, the at least one partition replacing an older partition.
3 . The method of claim 1 , wherein generating the partition-granular selectivity estimate is based on the selectivity estimation of at least two partitions of the first set of partitions and based on a number of rows in the at least two partitions.
4 . The method of claim 3 , wherein the at least two partitions are randomly selected.
5 . The method of claim 3 , wherein the at least two partitions are selected based on skews in statistical properties of the at least two partitions.
6 . The method of claim 1 , further comprising:
generating an initial query plan, wherein the query plan is a modified query plan based on the initial query plan and the partition-granular selectivity estimate.
7 . The method of claim 1 , further comprising:
generating a second join operation joining the first table, the second table, and a third table.
8 . A machine-storage medium embodying instructions that, when executed by a machine, cause the machine to perform operations comprising:
receiving a query including at least one predicate referencing a first table and a second table stored in a network-based data system, the first table comprising a first set of partitions and the second table comprising a second set of partitions;
retrieving statistics regarding the first set of partitions in the first table and the second set of partitions in the second table;
generating a partition-granular selectivity estimate for the at least one predicate based on the retrieved statistics;
generating a join operation in a query plan, the join operation comprising a selection of the first table as a build table and the second table as a probing table based on the partition-granular selectivity estimate, a first estimate for relevant rows in the build table first table being lower than a second estimate for relevant rows in the second table based on the partition-granular selectivity estimate; and
executing the query plan to generate results of the query comprising creating an index based on the build table for determining matches with the probing table to execute the join operation.
9 . The machine-storage medium of claim 8 , wherein respective partitions of the first set of partitions include a group of rows and at least one partition of the first set of partitions being created in response to new data being written to the first table, the at least one partition replacing an older partition.
10 . The machine-storage medium of claim 8 , wherein generating the partition-granular selectivity estimate is based on the selectivity estimation of at least two partitions of the first set of partitions and based on a number of rows in the at least two partitions.
11 . The machine-storage medium of claim 10 , wherein the at least two partitions are randomly selected.
12 . The machine-storage medium of claim 10 , wherein the at least two partitions are selected based on skews in statistical properties of the at least two partitions.
13 . The machine-storage medium of claim 8 , further comprising:
generating an initial query plan, wherein the query plan is a modified query plan based on the initial query plan and the partition-granular selectivity estimate.
14 . The machine-storage medium of claim 8 , further comprising:
generating a second join operation joining the first table, the second table, and a third table.
15 . A system comprising:
at least one hardware processor; and
at least one memory storing instructions that, when executed by the at least one hardware processor, cause the at least one hardware processor to perform operations comprising:
receiving a query including at least one predicate referencing a first table and a second table stored in a network-based data system, the first table comprising a first set of partitions and the second table comprising a second set of partitions;
retrieving statistics regarding the first set of partitions in the first table and the second set of partitions in the second table;
generating a partition-granular selectivity estimate for the at least one predicate based on the retrieved statistics;
generating a join operation in a query plan, the join operation comprising a selection of the first table as a build table and the second table as a probing table based on the partition-granular selectivity estimate, a first estimate for relevant rows in the build table first table being lower than a second estimate for relevant rows in the second table based on the partition-granular selectivity estimate; and
executing the query plan to generate results of the query comprising creating an index based on the build table for determining matches with the probing table to execute the join operation.
16 . The system of claim 15 , wherein respective partitions of the first set of partitions include a group of rows and at least one partition of the first set of partitions being created in response to new data being written to the first table, the at least one partition replacing an older partition.
17 . The system of claim 15 , wherein generating the partition-granular selectivity estimate is based on the selectivity estimation of at least two partitions of the first set of partitions and based on a number of rows in the at least two partitions.
18 . The system of claim 17 , wherein the at least two partitions are randomly selected.
19 . The system of claim 17 , wherein the at least two partitions are selected based on skews in statistical properties of the at least two partitions.
20 . The system of claim 15 , the operations further comprising:
generating an initial query plan, wherein the query plan is a modified query plan based on the initial query plan and the partition-granular selectivity estimate.
21 . The system of claim 15 , the operations further comprising:
generating a second join operation joining the first table, the second table, and a third table.