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