Database query processing using a pruning index
A source table organized into a set of micro-partitions is accessed by a network-based data warehouse. A pruning index is generated based on the source table. The pruning index comprises a set of filters that indicate locations of distinct values in each column of the source table. A query directed at the source table is received at the network-based data warehouse. The query is processed using the pruning index. The processing of the query comprises pruning the set of micro-partitions of the source table to scan for data matching the query, the pruning of the plurality of micro-partitions comprising identifying, using the pruning index, a sub-set of micro-partitions to scan for the data matching the query.
1. A system comprising:
at least one hardware processor; and
at least one memory storing instructions that cause the at least one hardware processor to perform operations comprising:
organizing a source table into a set of partitions;
receiving a query directed to the source table organized into the set of partitions;
accessing, from a database, a pruning index associated with the source table, the pruning index comprising a set of filters that index distinct values in each column of the source table, each filter in the set of filters being represented as a two-dimensional array in the pruning index, each filter in the set of filters corresponding to one partition from the set of partitions; and
processing the query using the pruning index, the processing of the query comprising:
identifying, using the pruning index, a sub-set of partitions to scan for data matching the query; and
scanning only the sub-set of partitions of the source table to identify data matching the query.
2. The system of claim 1 , wherein the processing of the query using the pruning index comprises:
generating a hash value based on an equality predicate included in the query;
identifying one or more values in the pruning index that match the hash value; and
identifying one or more partitions that potentially satisfy the equality predicate based on the one or more values in the pruning index that match the hash value.
3. The system of claim 1 , wherein the identifying of the sub-set of partitions to scan for the data matching the query comprises identifying one or more partitions in which data matching the query is not stored.
4. The system of claim 1 , wherein the identifying of the sub-set of partitions to scan for the data matching the query comprises identifying one or more partitions in which data matching the query is potentially stored.
5. The system of claim 1 , wherein:
each filter of the set of filters comprises multiple bloom filters, a number of bloom filters in each blocked bloom filter being based on a false positive rate constraint.
6. The system of claim 1 , wherein the operations further comprise generating the pruning index, the generating of the pruning index comprising generating a first filter for a first partition of the set of partitions in the source table, the first filter indexing distinct values in each column of the first partition.
7. The system of claim 6 , wherein the generating of the first filter comprises:
for a given data value,
identifying a cell within the first filter based on the data value;
generating a hash value based on the data value; and
populating the cell in the first filter using the hash value.
8. The system of claim 7 , wherein the operations further comprise computing a number of levels in the first filter;
wherein the identifying of the cell comprises:
identifying a slice in the first filter based on the data value and the number of levels in the first filter; and
identifying a filter column based on the data value and a number of filter columns in the first filter.
9. The system of claim 6 , wherein the generating of the first filter further comprises:
merging one or more rows of the first filter based on a target false positive rate.
10. The system of claim 1 , wherein:
each filter in the set of filters comprises a set of bloom filters; and
the pruning index further comprises a plurality of columns, the plurality of columns comprising a partition identifier column, a slice number column, and the set of bloom filters.
11. The system of claim 10 , further comprising:
clustering the pruning index based on the slice number column.
12. The system of claim 1 , wherein the operations further comprise:
detecting a change to the source table;
generating one or more additional rows for the pruning index based on the change to the source table; and
reclustering the pruning index based on slice numbers.
13. A method comprising:
organizing a source table into a set of partitions;
receiving a query directed to the source table organized into the set of partitions;
accessing, from a database, a pruning index associated with the source table, the pruning index comprising a set of filters that index distinct values in each column of the source table, each filter in the set of filters being represented as a two-dimensional array in the pruning index, each filter in the set of filters corresponding to one partition from the set of partitions; and
processing the query using the pruning index, the processing of the query comprising:
identifying, using the pruning index, a sub-set of partitions to scan for data matching the query; and
scanning only the sub-set of partitions of the source table to identify data matching the query.
14. The method of claim 13 , wherein the processing of the query using the pruning index comprises:
generating a hash value based on an equality predicate included in the query;
identifying one or more values in the pruning index that match the hash value; and
identifying one or more partitions that potentially satisfy the equality predicate based on the one or more values in the pruning index that match the hash value.
15. The method of claim 13 , wherein the identifying of the sub-set of partitions to scan for the data matching the query comprises identifying one or more partitions in which data matching the query is not stored.
16. The method of claim 13 , wherein the identifying of the sub-set of partitions to scan for the data matching the query comprises identifying one or more partitions in which data matching the query is potentially stored.
17. The method of claim 16 , wherein:
each filter of the set of filters comprises multiple bloom filters, a number of bloom filters in each blocked bloom filter being based on a false positive rate constraint.
18. The method of claim 13 , further comprising generating the pruning index, the generating of the pruning index comprising generating a first filter for a first partition of the set of partitions in the source table, the first filter indexing distinct values in each column of the first partition.
19. The method of claim 18 , wherein the generating of the first filter comprises:
for a given data value,
identifying a cell within the first filter based on the data value;
generating a hash value based on the data value; and
populating the cell in the first filter using the hash value.
20. The method of claim 19 , further comprising:
computing a number of levels in the first filter;
wherein the identifying of the cell comprises:
identifying a slice in the first filter based on the data value and the number of levels in the first filter; and
identifying a filter column based on the data value and a number of filter columns in the first filter.
21. The method of claim 18 , wherein the generating of the first filter further comprises:
merging one or more rows of the first filter based on a target false positive rate.
22. The method of claim 13 , wherein:
each filter in the set of filters comprises a set of bloom filters; and
the pruning index further comprises a plurality of columns, the plurality of columns comprising a partition identifier column, a slice number column, and the set of bloom filters.
23. The method of claim 13 , further comprising:
detecting a change to the source table;
generating one or more additional rows for the pruning index based on the change to the source table; and
reclustering the pruning index based on slice numbers.
24. A computer-storage medium comprising instructions that, when executed by one or more processors of a machine, configure the machine to perform operations comprising:
organizing a source table into a set of partitions;
receiving a query directed to the source table organized into the set of partitions;
accessing, from a database, a pruning index associated with the source table, the pruning index comprising a set of filters that index distinct values in each column of the source table, each filter in the set of filters being represented as a two-dimensional array in the pruning index, each filter in the set of filters corresponding to one partition from the set of partitions; and
processing the query using the pruning index, the processing of the query comprising:
identifying, using the pruning index, a sub-set of partitions to scan for data matching the query; and
scanning only the sub-set of partitions of the source table to identify data matching the query.
25. The computer-storage medium of claim 24 , wherein the processing of the query using the pruning index comprises:
generating a hash value based on an equality predicate included in the query;
identifying one or more values in the pruning index that match the hash value; and
identifying one or more partitions that potentially satisfy the equality predicate based on the one or more values in the pruning index that match the hash value.
26. The computer-storage medium of claim 24 , wherein the identifying of the sub-set of partitions to scan for the data matching the query comprises identifying one or more partitions in which data matching the query is not stored.
27. The computer-storage medium of claim 25 , wherein the identifying of the sub-set of partitions to scan for the data matching the query comprises identifying one or more partitions in which data matching the query is potentially stored.
28. The computer-storage medium of claim 24 , wherein:
each filter of the set of filters comprises multiple bloom filters, a number of bloom filters in each blocked bloom filter being based on a false positive rate constraint.
29. The computer-storage medium of claim 24 , wherein the operations further comprise generating the pruning index, the generating of the pruning index comprising generating a first filter for a first partition of the set of partitions in the source table, the first filter indexing distinct values in each column of the first partition.
30. The computer-storage medium of claim 29 , wherein the generating of the first filter comprises:
for a given data value,
identifying a cell within the first filter based on the data value;
generating a hash value based on the data value; and
populating the cell in the first filter using the hash value.