Re: Parallel hints in PostgreSQL with consistent perfromance

[mohini mane <mohini.android@xxxxxxxxx>]

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.janes@xxxxxxxxx> wrote:

On Wed, Dec 27, 2023 at 8:15 AM mohini mane <mohini.android@xxxxxxxxx> 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