On Fri, Dec 29, 2023 at 10:25 AM Jeff Janes <jeff.janes@xxxxxxxxx> wrote:
On Thu, Dec 28, 2023 at 7:47 AM mohini mane <mohini.android@xxxxxxxxx> wrote:Thank you for your response !!I am experimenting with SQL query performance for SELECT queries on large tables and I observed that changing/increasing the degree of parallel hint doesn't give the expected performance improvement.But you still have addressed the fact that PostgreSQL does not have planner hints.Are you using some nonstandard extension, or nonstandard fork?>> I am using pg_hint_plan extension to enforce the parallel execution of specific table .
postgres=# load 'pg_hint_plan';
LOADI have executed the SELECT query with 2,4 & 6 parallel degree however every time only 4 workers launched & there was a slight increase in Execution time as well,Adding an ignored comment to your SQL would not be expected to do anything. So it is not surprising that it does not do anything about the number of workers launched. It is just a comment. A note to the human who is reading the code.>> As I am using ph_hint_plan extension so as expected hints should not get ignored by the optimizer .why there is an increase in execution time with parallel degree 6 as compared to 2 or 4?Those small changes seem to be perfectly compatible with random noise. You would need to repeat them dozens of times in random order, and then do a statistical test to convince me otherwise.>> I am expecting desired number of parallel workers should get allocated as VM has sufficient vCores [16] and with needed session parameters [parallel_tuple_cost=0.1,max_parallel_workers_per_gather=6,max_parallel_workers=8 and I am using parallel hints like this : /*+ PARALLEL(A 5 hard) */ so 5 worker processes should launched this is not happening]
