RLE-aware optimization of SQL queries
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:
obtaining, by a distributed database system, a first query plan for computing query results from a database having data in compressed form, wherein the first query plan specifies that a first set of one or more operators are to be executed downstream from one or more decompress operators that are to be applied to data in compressed form to generate data in decompressed form;
determining that the first query plan is logically equivalent to a second query plan specifying that a second set of one or more operators are to be executed (i) instead of the first set of one or more operators, and (ii) upstream from one or more decompress operators that are to be applied to data in compressed form to generate data in decompressed form; and
in response to determining that the first query plan is logically equivalent to the second query plan, obtaining query results from the database according to the second query plan.
2. The computer-implemented method of claim 1 , wherein the first set of one or more operators includes a first operator that corresponds to a particular type of relational operation, and wherein the second set of one or more operators includes a second operator that corresponds to the particular type of relational operation.
3. The computer-implemented method of claim 2 , wherein the first operator has a first qualification expression that corresponds to the particular type of relational operation, and wherein the second operator has a second qualification expression that (i) corresponds to the particular type of relational operation and (ii) is different from the first qualification expression.
4. The computer-implemented method of claim 3 , wherein the particular type of relational operator is a JOIN operator.
5. The computer-implemented method of claim 2 , wherein the first operator is associated with a first set of one or more aggregate functions, and wherein the second operator is associated with a second set of one or more aggregate functions that is different from the first set of one or more aggregate functions.
6. The computer-implemented method of claim 5 , wherein the particular type of relational operator is a GROUPBY operator.
7. The computer-implemented method of claim 1 , wherein determining that the first query plan is logically equivalent to the second query plan comprises:
determining, based at least on the (i) structure of the first query plan and (ii) semantics of the first query plan, that the first query plan matches the second query plan.
8. The computer-implemented method of claim 1 , wherein the first query plan specifies that the first set of one or more operators are to be executed downstream from one or more decompress operators that are to be applied, by a computing system of the distributed database system, to data in compressed form to generate data in decompressed form.
9. The computer-implemented method of claim 8 , wherein the second query plan specifies that the second set of one or more operators are to be executed upstream from one or more decompress operators that are to be applied, by a client device of the distributed database system, to data in compressed form to generate data in decompressed form.
10. The computer-implemented method of claim 9 , wherein the second query plan specifies that the second set of one or more operators are to be executed by the computing system of the distributed database system.
11. A computer program product, encoded on one or more non-transitory computer storage media, comprising instructions that when executed by one or more computers cause the one or more computers to perform operations comprising:
obtaining, by a distributed database system, a first query plan for computing query results from a database having data in compressed form, wherein the first query plan specifies that a first set of one or more operators are to be executed downstream from one or more decompress operators that are to be applied to data in compressed form to generate data in decompressed form;
determining that the first query plan is logically equivalent to a second query plan specifying that a second set of one or more operators are to be executed (i) instead of the first set of one or more operators, and (ii) upstream from one or more decompress operators that are to be applied to data in compressed form to generate data in decompressed form; and
in response to determining that the first query plan is logically equivalent to the second query plan, obtaining query results from the database according to the second query plan.
12. The computer program product of claim 11 , wherein the first set of one or more operators includes a first operator having a first qualification expression that corresponds to a particular type of relational operation, and wherein the second set of one or more operators includes a second operator having a second qualification expression that (i) corresponds to the particular type of relational operation and (ii) is different from the first qualification expression.
13. The computer program product of claim 11 , wherein the first set of one or more operators includes a first operator that is associated with a first set of one or more aggregate functions, and wherein the second set of one or more operators includes a second operator that is associated with a second set of one or more aggregate functions, the second set of one or more aggregate functions being different from the first set of one or more aggregate functions.
14. The computer program product of claim 11 , wherein the first query plan specifies that the first set of one or more operators are to be executed downstream from one or more decompress operators that are to be applied, by a computing system of the distributed database system, to data in compressed form to generate data in decompressed form.
15. The computer program product of claim 14 , wherein the second query plan specifies that the second set of one or more operators are to be executed upstream from one or more decompress operators that are to be applied, by a client device of the distributed database system, to data in compressed form to generate data in decompressed form.
16. A distributed database system comprising:
one or more computers and one or more storage devices storing instructions that are operable, when executed by the one or more computers, to cause the one or more computers to perform operations comprising:
obtaining, by the distributed database system, a first query plan for computing query results from a database having data in compressed form, wherein the first query plan specifies that a first set of one or more operators are to be executed downstream from one or more decompress operators that are to be applied to data in compressed form to generate data in decompressed form;
determining that the first query plan is logically equivalent to a second query plan specifying that a second set of one or more operators are to be executed (i) instead of the first set of one or more operators, and (ii) upstream from one or more decompress operators that are to be applied to data in compressed form to generate data in decompressed form; and
in response to determining that the first query plan is logically equivalent to the second query plan, obtaining query results from the database according to the second query plan.
17. The system of claim 16 , wherein the first set of one or more operators includes a first operator having a first qualification expression that corresponds to a particular type of relational operation, and wherein the second set of one or more operators includes a second operator having a second qualification expression that (i) corresponds to the particular type of relational operation and (ii) is different from the first qualification expression.
18. The system of claim 16 , wherein the first set of one or more operators includes a first operator that is associated with a first set of one or more aggregate functions, and wherein the second set of one or more operators includes a second operator that is associated with a second set of one or more aggregate functions, the second set of one or more aggregate functions being different from the first set of one or more aggregate functions.
19. The system of claim 16 , wherein the first query plan specifies that the first set of one or more operators are to be executed downstream from one or more decompress operators that are to be applied, by a computing system of the distributed database system, to data in compressed form to generate data in decompressed form.
20. The system of claim 19 , wherein the second query plan specifies that the second set of one or more operators are to be executed upstream from one or more decompress operators that are to be applied, by a client device of the distributed database system, to data in compressed form to generate data in decompressed form.