Synthesized predicate driven index selection for partitioned table
A system and method for receiving a query of a partitioned table, the query including a first index predicate associated with a first partition key column; determining that one or more of the query is missing a second index predicate and the first index predicate is unusable for index probing; responsive to determining that one or more of the query is missing the second index predicate and the first index predicate is unusable for index probing, generating one or more synthesized predicates used to process the query using an index scan.
1. A method comprising:
receiving, using one or more processors, a query querying a partitioned table, the partitioned table including a first partition key column and a second partition key column associated with a local index;
determining, using the one or more processors, that the query is missing a first index predicate associated with the first partition key column and includes a second index predicate associated with the second partition key column; and
responsive to determining that the query is missing the first index predicate associated with the first partition key column and includes the second index predicate associated with the second partition key column, generating, using the one or more processors, a synthesized predicate associated with the first partition key column for the query, the synthesized predicate being used to process the query using an index scan.
2. The method of claim 1 , wherein:
the first partition key column is a leading key to the second partition key column among partition key columns associated with the local index.
3. The method of claim 1 , wherein:
the first partition key column is a partition key column of a list type that contains a set of defined values in a partition of the partitioned table.
4. The method of claim 1 , wherein generating the synthesized predicate associated with the first partition key column includes:
iterating over partition definitions of the partitioned table;
obtaining a set of defined values of the first partition key column; and
generating the synthesized predicate associated with the first partition key column using the set of defined values.
5. The method of claim 1 , wherein:
the synthesized predicate associated with the first partition key column includes an operator usable for index probing.
6. The method of claim 1 , wherein:
the synthesized predicate associated with the first partition key column includes an IN operator and a set of defined values of the first partition key column.
7. The method of claim 1 , further comprising:
generating a query plan based on the synthesized predicate associated with the first partition key column and the second index predicate associated with the second partition key column; and
executing the query plan to process the query using the index scan.
8. The method of claim 7 , wherein:
the query includes a join condition associated with the partitioned table and another table; and
executing the query plan includes performing the index scan on the partitioned table to evaluate the join condition.
9. The method of claim 1 , further comprising:
determining that the query is missing a third index predicate associated with a third partition key column; and
responsive to determining that the query is missing the third index predicate associated with the third partition key column, generating another synthesized predicate associated with the third partition key column for the query, the another synthesized predicate being used to process the query using the index scan.
10. The method of claim 1 , wherein determining that the query is missing the first index predicate includes:
determining one or more index predicates in the query;
receiving index metadata of the partitioned table, the index metadata specifying one or more partition key columns associated with the local index; and
determining that the query is missing the first index predicate associated with the first partition key column based on the one or more index predicates in the query and the index metadata of the partitioned table.
11. A system comprising:
a processor; and
a memory storing instructions that, when executed, cause the system to:
receive a query querying a partitioned table, the partitioned table including a first partition key column and a second partition key column associated with a local index;
determine that the query is missing a first index predicate associated with the first partition key column and includes a second index predicate associated with the second partition key column; and
responsive to determining that the query is missing the first index predicate associated with the first partition key column and includes the second index predicate associated with the second partition key column, generate a synthesized predicate associated with the first partition key column for the query, the synthesized predicate being used to process the query using an index scan.
12. The system of claim 11 , wherein:
the first partition key column is a leading key to the second partition key column among partition key columns associated with the local index.
13. The system of claim 11 , wherein:
the first partition key column is a partition key column of a list type that contains a set of defined values in a partition of the partitioned table.
14. The system of claim 11 , wherein generating the synthesized predicate associated with the first partition key column includes:
iterating over partition definitions of the partitioned table;
obtaining a set of defined values of the first partition key column; and
generating the synthesized predicate associated with the first partition key column using the set of defined values.
15. The system of claim 11 , wherein:
the synthesized predicate associated with the first partition key column includes an operator usable for index probing.
16. The system of claim 11 , wherein:
the synthesized predicate associated with the first partition key column includes an IN operator and a set of defined values of the first partition key column.
17. The system of claim 11 , wherein the instructions, when executed, cause the system to:
generate a query plan based on the synthesized predicate associated with the first partition key column and the second index predicate associated with the second partition key column; and
execute the query plan to process the query using the index scan.
18. The system of claim 17 , wherein:
the query includes a join condition associated with the partitioned table and another table; and
executing the query plan includes performing the index scan on the partitioned table to evaluate the join condition.
19. The system of claim 11 , wherein the instructions, when executed, cause the system to:
determine that the query is missing a third index predicate associated with a third partition key column; and
responsive to determining that the query is missing the third index predicate associated with the third partition key column, generate another synthesized predicate associated with the third partition key column for the query, the another synthesized predicate being used to process the query using the index scan.
20. The system of claim 11 , wherein determining that the query is missing the first index predicate includes:
determining one or more index predicates in the query;
receiving index metadata of the partitioned table, the index metadata specifying one or more partition key columns associated with the local index; and
determining that the query is missing the first index predicate associated with the first partition key column based on the one or more index predicates in the query and the index metadata of the partitioned table.