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
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2816266.54..2816267.79 rows=100 width=110) (actual
time=59613.454..59613.473 rows=100 loops=1)
-> Sort (cost=2816266.54..2816267.38 rows=336 width=110) (actual
time=59613.453..59613.464 rows=100 loops=1)
Sort Key: (substr((warehouse.w_warehouse_name)::text, 1, 20)),
ship_mode.sm_type, web_site.web_name
Sort Method: top-N heapsort Memory: 49kB
-> HashAggregate (cost=2816248.24..2816252.44 rows=336 width=110)
(actual time=59612.622..59612.730 rows=360 loops=1)
Group Key: substr((warehouse.w_warehouse_name)::text, 1, 20),
ship_mode.sm_type, web_site.web_name
Batches: 1 Memory Usage: 157kB
-> Hash Join (cost=2510.74..2794150.54 rows=368295 width=78)
(actual time=7.597..45818.495 rows=14336926 loops=1)
Hash Cond: (web_sales.ws_ship_mode_sk =
ship_mode.sm_ship_mode_sk)
-> Hash Join (cost=2509.29..2792056.55 rows=368356
width=36) (actual time=7.571..38820.092 rows=14337390 loops=1)
Hash Cond: (web_sales.ws_web_site_sk =
web_site.web_site_sk)
-> Hash Join (cost=2506.75..2790916.14 rows=368430
width=33) (actual time=7.554..35314.217 rows=14338265 loops=1)
Hash Cond: (web_sales.ws_warehouse_sk =
warehouse.w_warehouse_sk)
-> Hash Join (cost=2505.41..2789674.19
rows=368516 width=20) (actual time=7.544..31214.782 rows=14340028 loops=1)
Hash Cond: (web_sales.ws_ship_date_sk =
date_dim.d_date_sk)
-> Seq Scan on web_sales
(cost=0.00..2598153.08 rows=72001808 width=20) (actual time=0.026..17405.391
rows=72001237 loops=1)
-> Hash (cost=2500.73..2500.73
rows=374 width=4) (actual time=7.505..7.506 rows=365 loops=1)
Buckets: 1024 Batches: 1 Memory
Usage: 21kB
-> Seq Scan on date_dim
(cost=0.00..2500.73 rows=374 width=4) (actual time=3.599..7.462 rows=365
loops=1)
Filter: ((d_month_seq >=
1212) AND (d_month_seq <= 1223))
Rows Removed by Filter: 72684
-> Hash (cost=1.15..1.15 rows=15 width=21)
(actual time=0.007..0.007 rows=15 loops=1)
Buckets: 1024 Batches: 1 Memory Usage:
9kB
-> Seq Scan on warehouse
(cost=0.00..1.15 rows=15 width=21) (actual time=0.003..0.004 rows=15 loops=1)
-> Hash (cost=2.24..2.24 rows=24 width=11) (actual
time=0.011..0.012 rows=24 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on web_site (cost=0.00..2.24
rows=24 width=11) (actual time=0.004..0.008 rows=24 loops=1)
-> Hash (cost=1.20..1.20 rows=20 width=35) (actual
time=0.021..0.021 rows=20 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on ship_mode (cost=0.00..1.20 rows=20
width=35) (actual time=0.006..0.009 rows=20 loops=1)
Planning Time: 0.950 ms
Execution Time: 59613.803 ms
(32 rows)
Time: 59615.490 ms (00:59.615)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2839568.89..2839570.14 rows=100 width=110) (actual
time=67158.490..67158.505 rows=100 loops=1)
-> Sort (cost=2839568.89..2839569.73 rows=336 width=110) (actual
time=67158.489..67158.497 rows=100 loops=1)
Sort Key: (substr((web_sales.ws_ship_date_sk)::text, 1, 20)),
warehouse.w_warehouse_name, ship_mode.sm_carrier
Sort Method: top-N heapsort Memory: 49kB
-> HashAggregate (cost=2839550.59..2839554.79 rows=336 width=110)
(actual time=67157.796..67157.885 rows=360 loops=1)
Group Key: substr((web_sales.ws_ship_date_sk)::text, 1, 20),
warehouse.w_warehouse_name, ship_mode.sm_carrier
Batches: 1 Memory Usage: 157kB
-> Nested Loop (cost=2505.85..2817452.89 rows=368295 width=78)
(actual time=9.022..53310.759 rows=14336926 loops=1)
-> Nested Loop (cost=2505.70..2807569.94 rows=368356
width=36) (actual time=9.014..43690.253 rows=14337390 loops=1)
-> Nested Loop (cost=2505.55..2798567.14
rows=368430 width=33) (actual time=9.007..37393.316 rows=14338265 loops=1)
-> Hash Join (cost=2505.41..2789674.19
rows=368516 width=20) (actual time=8.989..29175.630 rows=14340028 loops=1)
Hash Cond: (web_sales.ws_ship_date_sk =
date_dim.d_date_sk)
-> Seq Scan on web_sales
(cost=0.00..2598153.08 rows=72001808 width=20) (actual time=0.037..16320.734
rows=72001237 loops=1)
-> Hash (cost=2500.73..2500.73
rows=374 width=4) (actual time=8.935..8.935 rows=365 loops=1)
Buckets: 1024 Batches: 1 Memory
Usage: 21kB
-> Seq Scan on date_dim
(cost=0.00..2500.73 rows=374 width=4) (actual time=4.243..8.882 rows=365
loops=1)
Filter: ((d_month_seq >=
1212) AND (d_month_seq <= 1223))
Rows Removed by Filter: 72684
-> Result Cache (cost=0.15..0.16 rows=1
width=21) (actual time=0.000..0.000 rows=1 loops=14340028)
Cache Key: web_sales.ws_warehouse_sk
Hits: 14340012 Misses: 16 Evictions: 0
Overflows: 0 Memory Usage: 2kB
-> Index Scan using warehouse_pkey on
warehouse (cost=0.14..0.15 rows=1 width=21) (actual time=0.003..0.003 rows=1
loops=16)
Index Cond: (w_warehouse_sk =
web_sales.ws_warehouse_sk)
-> Result Cache (cost=0.15..0.17 rows=1 width=11)
(actual time=0.000..0.000 rows=1 loops=14338265)
Cache Key: web_sales.ws_web_site_sk
Hits: 14338248 Misses: 17 Evictions: 0
Overflows: 0 Memory Usage: 2kB
-> Index Scan using web_site_pkey on web_site
(cost=0.14..0.16 rows=1 width=11) (actual time=0.002..0.002 rows=1 loops=17)
Index Cond: (web_site_sk =
web_sales.ws_web_site_sk)
-> Result Cache (cost=0.15..0.17 rows=1 width=35)
(actual time=0.000..0.000 rows=1 loops=14337390)
Cache Key: web_sales.ws_ship_mode_sk
Hits: 14337369 Misses: 21 Evictions: 0 Overflows:
0 Memory Usage: 3kB
-> Index Scan using ship_mode_pkey on ship_mode
(cost=0.14..0.16 rows=1 width=35) (actual time=0.002..0.002 rows=1 loops=21)
Index Cond: (sm_ship_mode_sk =
web_sales.ws_ship_mode_sk)
Planning Time: 1.597 ms
Execution Time: 67158.805 ms
(35 rows)
Time: 67161.202 ms (01:07.161)
