Runtime join pruning to improve join performance for database tables
The subject technology receives a query, the query including a statement for performing a join operation on a first table and a second table. The subject technology executes, by an execution node using a generated query plan, the query. The subject technology performs, during execution of the query by the execution node, a runtime range pruning process. The subject technology determines a set of range sets for pruning, each range set including a set of columns from the first table, the set of columns being removed from undergoing a read operation as part of executing the query. The subject technology determines, based on a range bloom vector, a set of rows in a particular range set of the first table to avoid scanning in connection with the read operation. The subject technology performs the read operation based a remaining set of rows.
1. A network-based database system comprising:
at least one hardware processor; and
a memory storing instructions that cause the at least one hardware processor to perform operations comprising:
receiving a query, the query including a statement for performing a join operation on a first table and a second table;
generating a query plan based on the query;
executing, by an execution node using the generated query plan, the query, the executing comprising:
performing, during execution of the query by the execution node, a runtime range pruning process, the runtime range pruning process comprising:
determining a set of range sets for pruning, each range set including a set of columns from the first table, the set of columns being removed from undergoing a read operation as part of executing the query;
determining, based on a range bloom vector, a set of rows in a particular range set of the first table to avoid scanning in connection with the read operation;
generating, during a compilation process of the query, a prune data structure, the prune data structure comprising information indicating a first identifier of a hash join build operator and a join key column identifier;
based on the first identifier and the join key column identifier, generating a prune information data structure including a mapping between a column corresponding to the join key column identifier and the range bloom vector; and
performing the read operation based a remaining set of rows from the particular range set of the first table.
2. The system of claim 1 , wherein the range bloom vector comprises vector of key ranges, the vector of key ranges indicating a minimum value and a maximum value of a particular set of values for scanning as part of executing the query.
3. The system of claim 1 , wherein the query plan comprises a first set of nodes and a second set of nodes, the first set of nodes comprises a build side of a hash join, and the second set of nodes comprises a probe side of the hash join.
4. The system of claim 3 , wherein the first set of nodes includes a first node corresponding to a key value table scan operation, a second node corresponding to a filter operation, and a third node corresponding to a hash join build operation.
5. The system of claim 3 , wherein the second set of nodes includes a first node corresponding to a key value table scan operation, and a second node corresponding to a bloom filter operation.
6. The system of claim 5 , wherein the key value table scan operation accesses the range bloom vector provided by a hash join build operation from the first set of nodes.
7. The system of claim 1 , wherein the operations further comprise:
receiving a second query, the second query including a second statement for performing a second join operation on the first table and the second table;
generating a second query plan based on the second query; and
executing, by the execution node using the generated second query plan, the second query, the executing comprising:
performing, during execution of the second query by the execution node, a runtime read version pruning process.
8. The system of claim 5 , wherein the operations further comprise:
generating, during a compaction process of the second table, a set of expression properties for a set of columns of the second table, the set of expression properties include metadata related to the set of columns of the second table; and
performing, after generating the set of expression properties, an insert operation of a set of values into the second table.
9. A method comprising:
receiving a query, the query including a statement for performing a join operation on a first table and a second table;
generating a query plan based on the query;
executing, by an execution node using the generated query plan, the query, the executing comprising:
performing, during execution of the query by the execution node, a runtime range pruning process, the runtime range pruning process comprising:
determining a set of range sets for pruning, each range set including a set of columns from the first table, the set of columns being removed from undergoing a read operation as part of executing the query;
determining, based on a range bloom vector, a set of rows in a particular range set of the first table to avoid scanning in connection with the read operation;
generating, during a compilation process of the query, a prune data structure, the prune data structure comprising information indicating a first identifier of a hash join build operator and a join key column identifier;
based on the first identifier and the join key column identifier, generating a prune information data structure including a mapping between a column corresponding to the join key column identifier and the range bloom vector; and
performing the read operation based a remaining set of rows from the particular range set of the first table.
10. The method of claim 9 , wherein the range bloom vector comprises vector of key ranges, the vector of key ranges indicating a minimum value and a maximum value of a particular set of values for scanning as part of executing the query.
11. The method of claim 9 , further comprising:
generating, during a compilation process of the query, a prune data structure, the prune data structure comprising information indicating a first identifier of a hash join build operator and a join key column identifier.
12. The method of claim 11 , further comprising:
based on the first identifier and the join key column identifier, generating a prune information data structure including a mapping between a column corresponding to the join key column identifier and the range bloom vector.
13. The method of claim 9 , wherein the query plan comprises a first set of nodes and a second set of nodes, the first set of nodes comprises a build side of a hash join, and the second set of nodes comprises a probe side of the hash join.
14. The method of claim 13 , wherein the first set of nodes includes a first node corresponding to a key value table scan operation, a second node corresponding to a filter operation, and a third node corresponding to a hash join build operation.
15. The method of claim 13 , wherein the second set of nodes includes a first node corresponding to a key value table scan operation, and a second node corresponding to a bloom filter operation.
16. The method of claim 15 , wherein the key value table scan operation accesses the range bloom vector provided by a hash join build operation from the first set of nodes.
17. The method of claim 9 , further comprising:
receiving a second query, the second query including a second statement for performing a second join operation on the first table and the second table;
generating a second query plan based on the second query; and
executing, by the execution node using the generated second query plan, the second query, the executing comprising:
performing, during execution of the second query by the execution node, a runtime read version pruning process.
18. The method of claim 15 , further comprising:
generating, during a compaction process of the second table, a set of expression properties for a set of columns of the second table, the set of expression properties include metadata related to the set of columns of the second table; and
performing, after generating the set of expression properties, an insert operation of a set of values into the second table.
19. A non-transitory computer-storage medium comprising instructions that, when executed by one or more processors of a machine, configure the machine to perform operations comprising:
receiving a query, the query including a statement for performing a join operation on a first table and a second table;
generating a query plan based on the query;
executing, by an execution node using the generated query plan, the query, the executing comprising:
performing, during execution of the query by the execution node, a runtime range pruning process, the runtime range pruning process comprising:
determining a set of range sets for pruning, each range set including a set of columns from the first table, the set of columns being removed from undergoing a read operation as part of executing the query;
determining, based on a range bloom vector, a set of rows in a particular range set of the first table to avoid scanning in connection with the read operation;
generating, during a compilation process of the query, a prune data structure, the prune data structure comprising information indicating a first identifier of a hash join build operator and a join key column identifier;
based on the first identifier and the join key column identifier, generating a prune information data structure including a mapping between a column corresponding to the join key column identifier and the range bloom vector; and
performing the read operation based a remaining set of rows from the particular range set of the first table.