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

Hello David,

Thank you for your reply.

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

These times were to run EXPLAIN ANALYZE. I executed each query twice,
and the **average** execution time was shown in the table of the last
e-mail. Therefore, the result of the table is not simply equal to that
of the attached file. I'm sorry for the insufficient explanation.

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

I ran query 62 by "EXPLAIN (ANALYZE, TIMING OFF)" and normally. I
attached these execution results to this e-mail. At this time, I
executed each query only once (not twice). The results are as follows.

Method | Execution time with result cache (s) | Execution time
without result cache (s) | Speedup ratio
EXPLAIN (ANALYZE, TIMING ON) 67.161 59.615 -12.66%
EXPLAIN (ANALYZE, TIMING OFF) 66.142 60.660 -9.04%
Normal 66.611 60.955 -9.28%

Although there is variation in the execution time, the speedup ratio
is around -10%. So, the result cache has a 10% regression in query 62.
The overhead of EXPLAIN ANALYZE and TIMING ON do not seem to be high.

Best regards,
Yuya Watari

On Tue, Apr 13, 2021 at 7:13 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> 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