Hello,
Recently, the result cache feature was committed to PostgreSQL. I
tested its performance by executing TPC-DS. As a result, I found that
there were some regressions in the query performance.
I used the TPC-DS scale factor 100 in the evaluation. I executed all
of the 99 queries in the TPC-DS, and the result cache worked in the 21
queries of them. However, some queries took too much time, so I
skipped their execution. I set work_mem to 256MB, and
max_parallel_workers_per_gather to 0.
Evaluation results are as follows. The negative speedup ratio
indicates that the execution time increased by the result cache.
Query No | Execution time with result cache | Execution time
without result cache | Speedup ratio
7 142.1 142.2 0.03%
8 144.0 142.8 -0.82%
13 164.6 162.0 -1.65%
27 138.9 138.7 -0.16%
34 135.7 137.1 1.02%
43 209.5 207.2 -1.10%
48 181.5 170.7 -6.32%
55 130.6 123.8 -5.48%
61 0.014 0.037 62.06%
62 66.7 59.9 -11.36%
68 131.3 127.2 -3.17%
72 567.0 563.4 -0.65%
73 130.1 129.8 -0.29%
88 1044.5 1048.7 0.40%
91 1.2 1.1 -7.93%
96 132.2 131.7 -0.37%
As you can see from these results, many queries have a negative
speedup ratio, which means that there are negative impacts on the
query performance. In query 62, the execution time increased by
11.36%. I guess these regressions are due to the misestimation of the
cost in the planner. I attached the execution plan of query 62.
The result cache is currently enabled by default. However, if this
kind of performance regression is common, we have to change its
default behavior.
Best regards,
Yuya Watari