Re: Parallel hints in PostgreSQL with consistent perfromance

On Fri, Dec 29, 2023 at 10:25 AM Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

>
>
> On Thu, Dec 28, 2023 at 7:47 AM mohini mane <mohini(dot)android(at)gmail(dot)com>
> 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';*
* LOAD*

> I 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]*
>
>>