RLE-aware optimization of SQL queries
View Patent ↗SQL queries are optimized to operate directly on compressed data (and obtain the correct result) rather than requiring that the data be first decompressed prior to processing a query. Certain characteristic pattern trees are mapped against a logical input query plan that includes certain logical operators such as a DECOMPRESS that precedes a JOIN or a GROUPBY in association with a COUNT to identify instances in the plan that match a characteristic pattern. Upon locating a match, the input query plan is transformed into a logically equivalent plan that operates correctly on compressed data, by analyzing the interplay of the semantics of logical query operations with the compressed data and substituting less costly structures and operations. DECOMPRESS operations are moved to operate subsequent to a JOIN or eliminated altogether, and COUNT operations are replaced by a different operation, such as SUM, that is logically equivalent for compressed data.
1. A computer-implemented method comprising:
receiving a query plan that includes a first pattern of query operators that (i) includes a decompress query operator followed by another query operator, and (ii) is configured to operate directly on decompressed data;
determining that the first pattern of query operators that (i) includes the decompress query operator followed by the other query operator, and (ii) is configured to operate directly on decompressed data, matches a second pattern of one or more query operators that (i) does not include a decompress query operator, and (ii) is configured to operate directly on compressed data;
obtaining a transformed query plan by replacing, in the query plan, the first pattern of query operators that (i) includes the decompress operator followed by the other query operator, and (ii) is configured to operate on decompressed data, with the second pattern of one or more query operators that (i) does not include the decompress query operator, and (ii) is configured to operate directly on compressed data; and
executing the transformed query plan, including executing the second pattern of one or more query operators that (i) does not include the decompress operator, and (ii) is configured to operate directly on compressed data, on compressed data.
2. The method of claim 1 , wherein determining that the first pattern of query operators matches the second pattern of one or query operators, comprises determining, based at least on the (i) structure of the first pattern of query operators and (ii) semantics of the first pattern of query operators, that the first query pattern of query operators matches the second pattern of one or more query operators.
3. The method of claim 1 , wherein receiving the query plan that includes the first pattern of query operators that (i) includes the decompress query operator followed by another query operator, and (ii) is configured to operate directly on decompressed data, comprises receiving a query plan that includes a first pattern of query operators that (i) includes a decompress query operator followed by a JOIN operator, and (ii) is configured to operate directly on decompressed data.
4. The method of claim 3 , wherein the one or more query operators which form the second pattern include the JOIN operator.
5. The method of claim 1 , wherein receiving the query plan that includes the first pattern of query operators that (i) includes the decompress query operator followed by another query operator, and (ii) is configured to operate directly on decompressed data, comprises receiving a query plan that includes a first pattern of query operators that (i) includes a decompress query operator followed by a GROUPBY operator, and (ii) is configured to operate directly on decompressed data.
6. The method of claim 5 , wherein the first pattern of query operators further includes a COUNT operator that is associated with the GROUPBY operator.
7. The method of claim 6 , wherein:
replacing, in the query plan, the first pattern of query operators with the second pattern of one or more query operators comprises replacing, in the query plan, the COUNT operator that is associated with the GROUPBY operator with a SUM operator, and
executing the transformed query plan comprises executing the GROUPBY operator on a sum of compressed data.
8. The method of claim 1 , wherein obtaining the transformed query plan comprises obtaining the transformed query plan in response to determining that the first pattern of query operators that (i) includes the decompress query operator followed by the other query operator, and (ii) is configured to operate directly on decompressed data, matches the second pattern of one or more query operators that (i) does not include a decompress query operator, and (ii) is configured to operate directly on compressed data.
9. A non-transitory computer-readable medium storing software comprising instructions executable by one or more computers which, upon such execution, cause the one or more computers to perform operations comprising:
receiving a query plan that includes a first pattern of query operators that (i) includes a decompress query operator followed by another query operator, and (ii) is configured to operate directly on decompressed data;
determining that the first pattern of query operators that (i) includes the decompress query operator followed by the other query operator, and (ii) is configured to operate directly on decompressed data, matches a second pattern of one or more query operators that (i) does not include a decompress query operator, and (ii) is configured to operate directly on compressed data;
obtaining a transformed query plan by replacing, in the query plan, the first pattern of query operators that (i) includes the decompress operator followed by the other query operator, and (ii) is configured to operate on decompressed data, with the second pattern of one or more query operators that (i) does not include the decompress query operator, and (ii) is configured to operate directly on compressed data; and
executing the transformed query plan, including executing the second pattern of one or more query operators that (i) does not include the decompress operator, and (ii) is configured to operate directly on compressed data, on compressed data.
10. The computer-readable medium of claim 9 , wherein determining that the first pattern of query operators matches the second pattern of one or query operators, comprises determining, based at least on the (i) structure of the first pattern of query operators and (ii) semantics of the first pattern of query operators, that the first query pattern of query operators matches the second pattern of one or more operators.
11. The computer-readable medium of claim 9 , wherein receiving the query plan that includes the first pattern of query operators that (i) includes the decompress query operator followed by another query operator, and (ii) is configured to operate directly on decompressed data, comprises receiving a query plan that includes a first pattern of query operators that (i) includes a decompress query operator followed by a JOIN operator, and (ii) is configured to operate directly on decompressed data.
12. The computer-readable medium of claim 11 , wherein the one or more query operators which form the second pattern include the JOIN operator.
13. The computer-readable medium of claim 9 , wherein:
receiving the query plan that includes the first pattern of query operators that (i) includes the decompress query operator followed by another query operator, and (ii) is configured to operate directly on decompressed data, comprises receiving a query plan that includes a first pattern of query operators that (i) includes a decompress query operator followed by a GROUPBY operator, and (ii) is configured to operate directly on decompressed data,
the first pattern of query operators further includes a COUNT operator that is associated with the GROUPBY operator,
replacing, in the query plan, the first pattern of query operators with the second pattern of one or more query operators comprises replacing, in the query plan, the COUNT operator that is associated with the GROUPBY operator with a SUM operator, and
executing the transformed query plan comprises executing the GROUPBY operator on a sum of compressed data.
14. The method of claim 1 , wherein receiving the query plan that includes the first pattern of query operators that (i) includes the decompress query operator followed by another query operator, and (ii) is configured to operate directly on decompressed data, comprises receiving a query plan that includes a first pattern of query operators that (i) includes a decompress query operator that is configured to operate on RLE-compressed relations followed by another query operator, and (ii) is configured to operate directly on decompressed data.
15. The computer-readable medium of claim 9 , wherein receiving the query plan that includes the first pattern of query operators that (i) includes the decompress query operator followed by another query operator, and (ii) is configured to operate directly on decompressed data, comprises receiving a query plan that includes a first pattern of query operators that (i) includes a decompress query operator that is configured to operate on RLE-compressed relations followed by another query operator, and (ii) is configured to operate directly on decompressed data.
16. The computer-readable medium of claim 9 , wherein obtaining the transformed query plan comprises obtaining the transformed query plan in response to determining that the first pattern of query operators that (i) includes the decompress query operator followed by the other query operator, and (ii) is configured to operate directly on decompressed data, matches the second pattern of one or more query operators that (i) does not include a decompress query operator, and (ii) is configured to operate directly on compressed data.