>I have done some more investigation of the patch and found that even
>without changing query planner related parameters, it seems to give
>bad plans (as in example below [1]). I think here the costing of rework each
I have done some more testing using TPC-H benchmark (For some of the queries, specially for Parallel Hash Join), and Results summary is as below.
Planning Time(ms)
Query
Base
Patch
TPC-H Q2
2.2
2.4
TPCH- Q3
0.67
0.71
TPCH- Q5
3.17
2.3
TPCH- Q7
2.43
2.4
Execution Time(ms)
Query
Base
Patch
TPC-H Q2
2826
766
TPCH- Q3
23473
24271
TPCH- Q5
21357
1432
TPCH- Q7
6779
1138
All Test files and Detail plan output is attached in mail
q2.sql, q3.sql, q.5.sql ans q7.sql are TPCH benchmark' 2nd, 3rd, 5th and 7th query
and Results with base and Parallel join are attached in q*_base.out and q*_parallel.out respectively.
Summary: With TPC-H queries where ever Hash Join is pushed under gather Node, significant improvement is visible,
with Q2, using 3 workers, time consumed is almost 1/3 of the base.
I Observed one problem, with Q5 and Q7, there some relation and snapshot references are leaked and i am getting below warning, havn't yet looked into the issue.
While looking at plans of Q5 and Q7, I have observed that Gather is
pushed below another Gather node for which we don't have appropriate
way of dealing. I think that could be the reason why you are seeing
the errors.
Also, I think it would be good if you can once check the plan/execution
time with max_parallel_degree=0 as that can give us base reference
data without parallelism, also I am wondering if have you have changed