Does RedShift support compilation cache for similar queries, how?

Question

I read the RedShift Paper: [https://assets.amazon.science/93/e0/a347021a4c6fbbccd5a056580d00/sigmod22-redshift-reinvented.pdf] (redshfit re-invented).
In chapter 2.6 Compilation Service:
> When the same or similar queries are executed, the compiled segments are reused from the cluster code compilation cache

I'm wondering how to identify similar queries?

A query with only parameters different is identified as similar queries?

e.g.

```
query1: select c1, sum(v1) from t where c2='AAA' group by c1; 
query2: select c1, sum(v1) from t where c2='BBB' group by c1; 
query3: select c1, sum(v1) from t where c2 in ('AAA') group by c1; 
```

query2 is similar to query1, but query3 is not similar to query1?

Any help would be appreciated!

Accepted Answer

Hi stanleyhunju,

There is no defined or specific list of conditions for similar queries. In your example, query 3 is considered similar to query 1 since they are searching for the same results or dataset but in a slightly different way. If you first execute query 1 and then query 3, query 3 in this case will use the result cache.

I usually use the system view [SVL_COMPILE](https://docs.aws.amazon.com/redshift/latest/dg/r_SVL_COMPILE.html) to analyze the compilation phase of queries.

Regards,
Ziad

Answer

Redshift query engine can rewrite the queries before execution. It will see that query 3 is `similar` to query 1 and can be rewritten to match it. Thus it can still leverage the compile cache for query 3.

Does RedShift support compilation cache for similar queries, how?

Relevant content