Re: Performance Evaluation of Result Cache by using TPC-DS

On Tue, 13 Apr 2021 at 21:29, Yuya Watari <watari(dot)yuya(at)gmail(dot)com> wrote:
> 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.

Many thanks for testing this.

> 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.

Can you share if these times were to run EXPLAIN ANALYZE or if they
were just the queries being executed normally?

The times in the two files you attached do look very similar to the
times in your table, so I suspect either TIMING ON is not that high an
overhead on your machine, or the results are that of EXPLAIN ANALYZE.

It would be really great if you could show the EXPLAIN (ANALYZE,
TIMING OFF) for query 62. There's a chance that the slowdown comes
from the additional EXPLAIN ANALYZE timing overhead with the Result
Cache version.

> The result cache is currently enabled by default. However, if this
> kind of performance regression is common, we have to change its
> default behavior.

Yes, the feedback we get during the beta period will help drive that
decision or if the costing needs to be adjusted.

David