Horton

Thread

  1. Parallel hints in PostgreSQL with consistent perfromance

    mohini mane <mohini.android@gmail.com> — 2023-12-27T13:15:23Z

    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 Version:* v15.5

*Operating System details:* RHL 7.x
Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                16
On-line CPU(s) list:   0-15
Thread(s) per core:    1
Core(s) per socket:    16
Socket(s):             1
NUMA node(s):          1
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 79
Model name:            Intel(R) Xeon(R) CPU E5-2673 v4 @ 2.30GHz
Stepping:              1
CPU MHz:               2294.684
BogoMIPS:              4589.36
Hypervisor vendor:     Microsoft
Virtualization type:   full
L1d cache:             32K
L1i cache:             32K
L2 cache:              256K
L3 cache:              51200K
NUMA node0 CPU(s):     0-15

*PostgreSQL Query:*
Sample sql executed through psql command prompt
force_parallel_mode=on; max_parallel_workers_per_gather=200;
max_parallel_workers=6

explain analyze select /*+ PARALLEL(A 6) */ ctid::varchar,
 md5("col1"||'~'||"col7"||'~'||"col9"::varchar) ,
md5("id"||'~'||"gender"||'~'||"firstname"||'~'||"lastname"||'~'||"address"||'~'||"city"||'~'||"salary"||'~'||"pincode"||'~'||"sales"||'~'||"phone"||'~'||"amount"||'~'||"dob"||'~'||"starttime"||'~'||"timezone"||'~'||"status"||'~'||"timenow"||'~'||"timelater"||'~'||"col2"||'~'||"col3"||'~'||"col4"||'~'||"col5"||'~'||"col6"||'~'||"col8"||'~'||"col10"||'~'||"col11"||'~'||"col12"||'~'||"col13"||'~'||"col14"||'~'||"col15"||'~'||"col16"||'~'||"col17"::varchar)
,  md5('@'||"col1"||'~'||"col7"||'~'||"col9"::varchar)  from
"sp_qarun"."basic2" A  order by 2,4,3;

*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.

*Table Structure:*
create table basic2(id int,gender char,firstname varchar(3000),
lastname varchar(3000),address varchar(3000),city varchar(900),salary
smallint,
pincode bigint,sales numeric,phone real,amount double precision,
dob date,starttime timestamp,timezone TIMESTAMP WITH TIME ZONE,
status boolean,timenow time,timelater TIME WITH TIME ZONE,col1 int,
col2 char,col3 varchar(3000),col4 varchar(3000),col5 varchar(3000),
col6 varchar(900),col7 smallint,col8 bigint,col9 numeric,col10 real,
col11 double precision,col12 date,col13 timestamp,col14 TIMESTAMP WITH TIME
ZONE,
col15 boolean,col16 time,col17 TIME WITH TIME ZONE,primary
key(col1,col7,col9));

*Table Data:* 1000000 rows with each Row has a size of 20000.

Thanks,
Mohini

  2. Re: Parallel hints in PostgreSQL with consistent perfromance

    Tomas Vondra <tomas.vondra@enterprisedb.com> — 2023-12-27T14:15:11Z

    On 12/27/23 14:15, mohini mane 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 Version:* v15.5
> 
> *Operating System details:* RHL 7.x
> Architecture:          x86_64
> CPU op-mode(s):        32-bit, 64-bit
> Byte Order:            Little Endian
> CPU(s):                16
> On-line CPU(s) list:   0-15
> Thread(s) per core:    1
> Core(s) per socket:    16
> Socket(s):             1
> NUMA node(s):          1
> Vendor ID:             GenuineIntel
> CPU family:            6
> Model:                 79
> Model name:            Intel(R) Xeon(R) CPU E5-2673 v4 @ 2.30GHz
> Stepping:              1
> CPU MHz:               2294.684
> BogoMIPS:              4589.36
> Hypervisor vendor:     Microsoft
> Virtualization type:   full
> L1d cache:             32K
> L1i cache:             32K
> L2 cache:              256K
> L3 cache:              51200K
> NUMA node0 CPU(s):     0-15
> 
> *PostgreSQL Query:* 
> Sample sql executed through psql command prompt
> force_parallel_mode=on; max_parallel_workers_per_gather=200;
> max_parallel_workers=6
> 
> explain analyze select /*+ PARALLEL(A 6) */ ctid::varchar,
>  md5("col1"||'~'||"col7"||'~'||"col9"::varchar) ,
> md5("id"||'~'||"gender"||'~'||"firstname"||'~'||"lastname"||'~'||"address"||'~'||"city"||'~'||"salary"||'~'||"pincode"||'~'||"sales"||'~'||"phone"||'~'||"amount"||'~'||"dob"||'~'||"starttime"||'~'||"timezone"||'~'||"status"||'~'||"timenow"||'~'||"timelater"||'~'||"col2"||'~'||"col3"||'~'||"col4"||'~'||"col5"||'~'||"col6"||'~'||"col8"||'~'||"col10"||'~'||"col11"||'~'||"col12"||'~'||"col13"||'~'||"col14"||'~'||"col15"||'~'||"col16"||'~'||"col17"::varchar) ,  md5('@'||"col1"||'~'||"col7"||'~'||"col9"::varchar)  from "sp_qarun"."basic2" A  order by 2,4,3;
> 

Postgres doesn't support hints, so the /* ... */ part of the query is
just a comment and doesn't effect the parallelism at all. The main thing
influencing that are the GUC values you set before.


> *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.
> 

It's unclear if what exactly you changed, and what case you're comparing
the timing to. As I explained earlier, the hint comment has no effect.
So if that's what you increased, it's not surprising the timing does not
change.

Also, max_parallel_workers is the maximum total number of parallel
workers, i.e. it's upper bound of max_parallel_workers_per_gather. So if
you set it to 6, there will never be more than 6 workers, no matter what
value max_parallel_workers_per_gather is set to.

FWIW force_parallel_more is really meant for testing (in the context of
developing the database itself), it's hardly the thing you should do in
any other case, like for example testing performance.


> *Table Structure:*
> create table basic2(id int,gender char,firstname varchar(3000),
> lastname varchar(3000),address varchar(3000),city varchar(900),salary
> smallint,
> pincode bigint,sales numeric,phone real,amount double precision,
> dob date,starttime timestamp,timezone TIMESTAMP WITH TIME ZONE,
> status boolean,timenow time,timelater TIME WITH TIME ZONE,col1 int,
> col2 char,col3 varchar(3000),col4 varchar(3000),col5 varchar(3000),
> col6 varchar(900),col7 smallint,col8 bigint,col9 numeric,col10 real,
> col11 double precision,col12 date,col13 timestamp,col14 TIMESTAMP WITH
> TIME ZONE,
> col15 boolean,col16 time,col17 TIME WITH TIME ZONE,primary
> key(col1,col7,col9)); 
> 
> *Table Data:* 1000000 rows with each Row has a size of 20000.
> 

Without the data we can't actually try running the query.

In general it's a good idea to show the "explain analyze" output for the
cases you're comparing. Not only that shows what the database is doing,
it also shows timings for different parts of the query, how many workers
were planned / actually started etc.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  3. Re: Parallel hints in PostgreSQL with consistent perfromance

    Jeff Janes <jeff.janes@gmail.com> — 2023-12-27T14:41:36Z

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

  4. Re: Parallel hints in PostgreSQL with consistent perfromance

    mohini mane <mohini.android@gmail.com> — 2023-12-28T12:46:59Z

    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@gmail.com> wrote:

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

  5. Re: Parallel hints in PostgreSQL with consistent perfromance

    Matheus de Oliveira <matioli.matheus@gmail.com> — 2023-12-28T13:12:10Z

    On Thu, Dec 28, 2023 at 9:47 AM mohini mane <mohini.android@gmail.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.
>

Why do you believe you are changing the degree of parallelism? PostgreSQL
does not have parallel hints (or any hint in comments), so you are just
changing a comment in the queries, which changes nothing at all in the
execution plan.

Unless you are not using vanilla PostgreSQL or you have some extension in
place, in which case you didn't provide enough information.

Best regards,

-- 
Matheus de Oliveira

  6. Re: Parallel hints in PostgreSQL with consistent perfromance

    David G. Johnston <david.g.johnston@gmail.com> — 2023-12-28T13:40:00Z

    On Thursday, December 28, 2023, mohini mane <mohini.android@gmail.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.
>
> 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?
>

Random environmental effects.

Also, analyzing a performance test without understanding how “buffers” are
used is largely pointless.

Whatever told you about that comment syntax is hallucinating.

Please don’t reply by top-posting. Inline reply to the comments others make
directly and trim as needed.  Simply restating your first email isn’t
productive.

You cannot enforce the number of workers used, only the the maximum.  That
is you knob.

David J.

  7. Re: Parallel hints in PostgreSQL with consistent perfromance

    Jeff Janes <jeff.janes@gmail.com> — 2023-12-29T04:55:04Z

    On Thu, Dec 28, 2023 at 7:47 AM mohini mane <mohini.android@gmail.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 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.


> 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.


>

  8. Re: Parallel hints in PostgreSQL with consistent perfromance

    mohini mane <mohini.android@gmail.com> — 2024-01-02T15:12:39Z

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

>
>
> On Thu, Dec 28, 2023 at 7:47 AM mohini mane <mohini.android@gmail.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]*
>
>>

  9. Re: Parallel hints in PostgreSQL with consistent perfromance

    David G. Johnston <david.g.johnston@gmail.com> — 2024-01-02T16:14:37Z

    On Tue, Jan 2, 2024 at 8:12 AM mohini mane <mohini.android@gmail.com> wrote:

>
> 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 .*
>>
>
Sounds like a bug you should go tell the pg_hint_plan authors about then.

David J.

  10. Re: Parallel hints in PostgreSQL with consistent perfromance

    mohini mane <mohini.android@gmail.com> — 2024-01-02T18:06:10Z

    On Tue, 2 Jan 2024, 21:45 David G. Johnston, <david.g.johnston@gmail.com>
wrote:

> On Tue, Jan 2, 2024 at 8:12 AM mohini mane <mohini.android@gmail.com>
> wrote:
>
>>
>> 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 pg_hint_plan extension so as expected hints should
>>> not get ignored by the optimizer .*
>>>
>>
> Sounds like a bug you should go tell the pg_hint_plan authors about then.
>
    *>> I am getting same results with or without extension [in my case
it's pg_hint_plan] still I will check with the respective team, Thanks .*

>
> David J.
>
>

  11. Re: Parallel hints in PostgreSQL with consistent perfromance

    Jeff Janes <jeff.janes@gmail.com> — 2024-01-04T01:43:21Z

    > *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)
>

Your alias is not enclosed in double quotes, so it is downcased to "a" (as
can be seen from the alias printed in the plan).  But pg_hint_plan hints
don't follow the downcasing convention, so the hint on "A" does not match
the alias "a", and so is ignored.

Cheers,

Jeff

>

  12. Re: Parallel hints in PostgreSQL with consistent perfromance

    mohini mane <mohini.android@gmail.com> — 2024-01-04T07:52:37Z

    On Thu, Jan 4, 2024 at 7:13 AM Jeff Janes <jeff.janes@gmail.com> wrote:

>
>
>> *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)
>>
>
> Your alias is not enclosed in double quotes, so it is downcased to "a" (as
> can be seen from the alias printed in the plan).  But pg_hint_plan hints
> don't follow the downcasing convention, so the hint on "A" does not match
> the alias "a", and so is ignored.
>  * >> Thanks Jeff for the response !   It worked with "A" alias *
> Cheers,
>
> Jeff
>
>>