Re: Parallel hints in PostgreSQL with consistent perfromance

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.

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, why there is an increase in execution time with parallel
degree 6 as compared to 2 or 4?
Please refer to the test results

I am sharing the latest test results here :
*Session variables set in psql prompt:*
# show max_parallel_workers;
max_parallel_workers
----------------------
8
(1 row)

# show max_parallel_workers_per_gather;
max_parallel_workers_per_gather
---------------------------------
6
(1 row)

*1st time query executed with PARALLEL DEGREE 2 *
explain analyze select /*+* PARALLEL(A 2)* */ * from
test_compare_all_col_src1 A;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=10.00..45524.73 rows=949636 width=97) (actual
time=0.673..173.017 rows=955000 loops=1)
Workers Planned: 4
* Workers Launched: 4*
-> Parallel Seq Scan on test_compare_all_col_src1 a
(cost=0.00..44565.09 rows=237409 width=97) (actual time=0.039..51.941
rows=191000 loops=5)
Planning Time: 0.093 ms
* Execution Time: 209.745 ms*
(6 rows)

*2nd time query executed with PARALLEL DEGREE 4*
explain analyze select /*+ *PARALLEL(A 4)* */ * from
aparopka.test_compare_all_col_src1 A;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=10.00..45524.73 rows=949636 width=97) (actual
time=0.459..174.771 rows=955000 loops=1)
Workers Planned: 4
*Workers Launched: 4*
-> Parallel Seq Scan on test_compare_all_col_src1 a
(cost=0.00..44565.09 rows=237409 width=97) (actual time=0.038..54.320
rows=191000 loops=5)
Planning Time: 0.073 ms
*Execution Time: 210.170 ms*
(6 rows)

3rd time query executed with PARALLEL DEGREE 6

explain analyze select /**+ PARALLEL(A 6)* */ * from
aparopka.test_compare_all_col_src1 A;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=10.00..45524.73 rows=949636 width=97) (actual
time=0.560..196.586 rows=955000 loops=1)
Workers Planned: 4
*Workers Launched: 4*
-> Parallel Seq Scan on test_compare_all_col_src1 a
(cost=0.00..44565.09 rows=237409 width=97) (actual time=0.049..58.741
rows=191000 loops=5)
Planning Time: 0.095 ms
*Execution Time: 235.365 ms*
(6 rows)

Table Schema :

Table "test_compare_all_col_src1"
Column | Type | Collation | Nullable |
Default | Storage | Stats target | Description
-----------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
col_smallint | integer | | |
| plain | |
col_int | integer | | |
| plain | |
col_bigint | bigint | | not null |
| plain | |
col_numeric | numeric | | |
| main | |
col_real | real | | |
| plain | |
col_double | double precision | | |
| plain | |
col_bool | boolean | | |
| plain | |
col_char | character(1) | | |
| extended | |
col_varchar | character varying(2000) | | |
| extended | |
col_date | date | | |
| plain | |
col_time | time without time zone | | |
| plain | |
col_timetz | time with time zone | | |
| plain | |
col_timestamp | timestamp without time zone | | |
| plain | |
col_timestamptz | timestamp with time zone | | |
| plain | |
Indexes:
"test_compare_all_col_src1_pkey" PRIMARY KEY, btree (col_bigint)
Replica Identity: FULL
Access method: heap

# select count(*) from test_compare_all_col_src1;
count
--------
955000
(1 row)

Thanks,
--Mohini

On Wed, 27 Dec 2023, 20:11 Jeff Janes, <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Wed, Dec 27, 2023 at 8:15 AM mohini mane <mohini(dot)android(at)gmail(dot)com>
> wrote:
>
>> Hello Team,
>> I observed that increasing the degree of parallel hint in the SELECT
>> query did not show performance improvements.
>> Below are the details of sample execution with EXPLAIN ANALYZE
>>
>
> PostgreSQL doesn't have hints, unless you are using pg_hint_plan. Which
> you should say if you are.
>
> *Output:*
>> PSQL query execution with hints 6 for 1st time => 203505.402 ms
>> PSQL query execution with hints 6 for 2nd time => 27920.272 ms
>> PSQL query execution with hints 6 for 3rd time => 27666.770 ms
>> Only 6 workers launched, and there is no reduction in execution time even
>> after increasing the degree of parallel hints in select query.
>>
>
> All you are showing here is the effect of caching the data in memory. You
> allude to changing the degree, but didn't show any results, or even
> describe what the change was. Is 6 the base from which you increased, or
> is it the result of having done the increase?
>
> Cheers,
>
> Jeff
>