From: Tsunakawa, Takayuki/綱川 貴之 <tsunakawa.ta...@fujitsu.com> >the current patch showd nice performance improvement in some (many?) patterns. > >So, I think it can be committed in PG 14, when it has addressed the plan cache >issue that Amit Langote-san posed.
Agreed. I summarized my test results for the current patch(V18) in the attached file(Please use VSCode or Notepad++ to open it, the context is a bit long). As you can see, the performance is good in many patterns(Please refer to my test script, test NO in text is correspond to number in sql file). If you have any question on my test, please feel free to ask. Regards, Tang
max_parallel_workers_per_gather=2
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Test NO | test case
| query plan
| patched(ms) | master(ms) | %reg
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | Test INSERT with underlying query.
| parallel INSERT+SELECT | 20894.069 |
27030.623 | -23%
2 | Test INSERT into a table with a foreign key.
| parallel SELECT | 80921.960 |
84416.775 | -4%
3 | Test INSERT with ON CONFLICT ... DO UPDATE
| non-parallel | 28111.186 |
29531.922 | -5%
4 | Test INSERT with parallel_leader_participation
= off; |
parallel INSERT+SELECT | 2799.354 | 5131.874 |
-45%
5 | Test INSERT with max_parallel_workers=0;
| non-parallel | 27006.385 |
26962.279 | 0%
6 | Test INSERT with parallelized aggregate
| parallel SELECT | 95482.307 |
105090.301 | -9%
7 | Test INSERT with parallel bitmap heap scan
| parallel INSERT+SELECT | 606.664 | 844.471
| -28%
8 | Test INSERT with parallel append
| parallel INSERT+SELECT | 109.896 |
239.198 | -54%
9 | Test INSERT with parallel index scan
| parallel INSERT+SELECT | 552.481 |
1238.079 | -55%
10 | Test INSERT with parallel-safe index expression
| parallel INSERT+SELECT | 1453.686 | 2908.489
| -50%
11 | Test INSERT with parallel-unsafe index
expression
| non-parallel | 2828.559 |
2837.570 | 0%
12 | Test INSERT with underlying query - and
RETURNING (no projection) |
parallel INSERT+SELECT | 417.493 | 685.430 |
-39%
13 | Test INSERT with underlying ordered query - and
RETURNING (no projection) | parallel SELECT
| 971.095 | 1717.502 | -43%
14 | Test INSERT with underlying ordered query - and
RETURNING (with projection) | parallel SELECT
| 1002.287 | 1745.255 | -43%
15 | Test INSERT into temporary table with
underlying query.
| parallel SELECT | 224.420 |
489.748 | -54%
16 | No column defaults, should use parallel
INSERT+SELECT
| parallel INSERT+SELECT | 1520.582 | 1856.966
| -18%
17 | Parallel unsafe column default, should not use
a parallel plan |
non-parallel | 2260.880 | 2251.054
| 0%
18 | Parallel restricted column default, should use
INSERT + parallel SELECT | parallel
SELECT | 1975.598 | 2249.041 |
-12%
19 | Parallel safe column default, should use
parallel INSERT+SELECT |
parallel INSERT+SELECT | 1624.270 | 2245.439 |
-28%
20 | Parallel restricted and unsafe column defaults,
should not use a parallel plan | non-parallel
| 2411.253 | 2517.183 | -4%
21 | Test INSERT into partition with underlying
query.
| parallel INSERT+SELECT | 4620.209 | 9533.214
| -52%
22 | Test INSERT into partition with parallel-unsafe
partition key expression | non-parallel
| 11961.355 | 11724.563 | 2%
23 | Test INSERT into table with parallel-safe check
constraint |
parallel INSERT+SELECT | 3917.872 | 8939.580 |
-56%
24 | Test INSERT into table with parallel-unsafe
check constraint |
non-parallel | 9249.216 | 9157.939
| 1%
25 | Test INSERT into table with before+after
parallel-safe stmt-level triggers | parallel
INSERT+SELECT | 549.534 | 969.528 | -43%
26 | Test INSERT into table with before+after
parallel-unsafe stmt-level triggers | non-parallel
| 947.072 | 967.843 | -2%
27 | Test INSERT into table with before+after
parallel-restricted stmt-level trigger | parallel SELECT
| 682.345 | 967.293 | -29%
28 | Test INSERT into table with TOAST column
| parallel INSERT+SELECT | 230.174 | 319.134
| -28%
29 | Test INSERT into table having a DOMAIN column
with parallel-unsafe CHECK constraint | non-parallel
| 9826.738 | 9985.930 | -2%
30 | Test INSERT into table having a DOMAIN column
with parallel-restricted CHECK constraint | parallel SELECT
| 3743.854 | 8731.420 | -57%
31 | Test INSERT into table having a DOMAIN column
with parallel-safe CHECK constraint | parallel SELECT
| 3757.773 | 8736.047 | -57%
32 | Test INSERT with ordered underlying query.
| parallel SELECT | 4982.904 |
9347.244 | -47%
33 | Test INSERT into foreign table with underlying
query. |
parallel SELECT | 11728.190 | 11823.020
| -1%
performance_test.sql
Description: performance_test.sql
