Thread: Need recommendation on PARALLEL INDEX SCAN and PARALLEL APPEND

Need recommendation on PARALLEL INDEX SCAN and PARALLEL APPEND

From

Vivekk P

Date:

26 October 2021, 09:09:01

Hi Team,

Please have a look on the below problem statement and suggest us if there are any ways to make the planner pick PARALLEL INDEX SCAN and PARALLEL APPEND

Problem Statement :

We have a partitioned table with a partition key column (crdt --> timestamp). A SELECT query on this table that does not invoke the partition key column undergoes INDEX SCAN on all the partitions and it is being summed up in an APPEND node

Our requirement is to make the planner pick,

--PARALLEL INDEX SCAN instead of INDEX SCAN

--PARALLEL APPEND instead of APPEND

PostgreSQL version --> 13.4

Table Structure :

Partitioned table "public.pay" Column | Type | Collation | Nullable | Default -------------------------------+--------------------------+-----------+----------+------------- id | bigint | | not null | pri | character varying(256) | | | prf | character varying(128) | | | pi | character varying(256) | | | pas | character varying(128) | | | s | payment_state | | not null | st | jsonb | | not null | rct | character varying(32) | | | prf | jsonb | | | pa | jsonb | | | always | jsonb | | | '{}'::jsonb pr | jsonb | | | pe | jsonb | | | cda | jsonb | | | tr | jsonb | | | ar | jsonb | | | crq | jsonb | | | cr | jsonb | | | prt | jsonb | | | rrq | jsonb | | | rtrt | jsonb | | | rrt | jsonb | | | tc | character varying(32) | | | crdt | timestamp with time zone | | not null | now() isfin | boolean | | | ifi | bigint | | | rid | character varying(256) | | | pce | text | | | cce | text | | | pp | jsonb | | | Partition key: RANGE (crdt) Indexes: "pay_pkey" PRIMARY KEY, btree (id, crdt) "pay_businessid_storeid_crdt" btree ((pe ->> 'businessID'::text), (pe ->> 'storeID'::text), crdt) WHERE (pe ->> 'businessID'::text) IS NOT NULL AND (pe ->> 'storeID'::text) IS NOT NULL "pay_crdt_index" btree (crdt) "pay_ifi_idx" btree (ifi) "pay_index_isfin" btree (isfin) "pay_pi_pas_key" UNIQUE CONSTRAINT, btree (pi, pas, crdt) "pay_pri_prf_key" UNIQUE CONSTRAINT, btree (pri, prf, crdt) "pay_rid_crdt" btree (rid, crdt) WHERE rid IS NOT NULL AND crdt >= '2020-04-01 00:00:00+00'::timestamp with time zone "pay_tc_index" btree (tc, crdt) "pay_user_id_pe_index" btree ((pe ->> 'userID'::text)) WHERE (pe ->> 'userID'::text) IS NOT NULL "pay_user_id_pr_index" btree ((pr ->> 'userID'::text)) WHERE (pr ->> 'userID'::text) IS NOT NULL Triggers: pay_bucardo_delta AFTER INSERT OR DELETE OR UPDATE ON pay FOR EACH ROW EXECUTE FUNCTION bucardo.delta_public_pay() pay_bucardo_note_trunc_sync_payment_pay AFTER TRUNCATE ON pay FOR EACH STATEMENT EXECUTE FUNCTION bucardo.bucardo_note_truncation('sync_payment_pay') pay_payment_completion_trigger_after_upsert AFTER INSERT OR UPDATE ON pay FOR EACH ROW EXECUTE FUNCTION handle_payment_completion() Triggers firing always: pay_trg_change_capture_pay AFTER INSERT OR UPDATE ON pay FOR EACH ROW EXECUTE FUNCTION fun_change_capture_pay() Number of partitions: 4 (Use \d+ to list them.)

Partitions :

p_p2021_09 FOR VALUES FROM ('2021-09-01 00:00:00+00') TO ('2021-10-01 00:00:00+00'),

p_p2021_10 FOR VALUES FROM ('2021-10-01 00:00:00+00') TO ('2021-11-01 00:00:00+00'),

p_p2021_11 FOR VALUES FROM ('2021-11-01 00:00:00+00') TO ('2021-12-01 00:00:00+00'),

p_default DEFAULT

Table_size :

Name	Type	Size
pay	partitioned table	0 bytes
p_default	table	8192 bytes
p_p2021_09	table	358 MB
p_p2021_10	table	370 MB
p_p2021_11	table	358 MB

Note: The table size will be in TB's in the actual scenario

Query :

SELECT id, pri, prf, pi, pas, s, st, a, rct, pr, pa, pr, pe, cda, crdt, tr, ar, crq, cr, prt, tc, ifi, isfin, rrt, rrq, rtrt, rid, pce, cce, pp

FROM public.pay WHERE id = 3011852315482470422;

Query Plan :

pay=# EXPLAIN (ANALYZE,BUFFERS) SELECT id, pri, prf, pi, pas, s,

st, a, rct, pr, pa, pr, pe, cda, crdt,

tr, ar, crq, cr, prt, tc, ifi, isfin, rrt,

rrq, rtrt, rid, pce, cce, pp

FROM public.pay WHERE id = 3011852315482470422;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------

Append (cost=0.29..33.09 rows=4 width=1931) (actual time=0.148..0.211 rows=0 loops=1)

Buffers: shared hit=8

-> Index Scan using pay_p2021_09_pkey on pay_p2021_09 pay_1 (cost=0.29..8.30 rows=1 width=1931) (actual time=0.015..0.022 rows=0 loops=1)

Index Cond: (id = '3011852315482470422'::bigint)

Buffers: shared hit=2

-> Index Scan using pay_p2021_10_pkey on pay_p2021_10 pay_2 (cost=0.29..8.30 rows=1 width=1931) (actual time=0.012..0.019 rows=0 loops=1)

Index Cond: (id = '3011852315482470422'::bigint)

Buffers: shared hit=2

-> Index Scan using pay_p2021_11_pkey on pay_p2021_11 pay_3 (cost=0.29..8.30 rows=1 width=1931) (actual time=0.012..0.019 rows=0 loops=1)

Index Cond: (id = '3011852315482470422'::bigint)

Buffers: shared hit=2

-> Index Scan using pay_default_pkey on pay_default pay_4 (cost=0.14..8.16 rows=1 width=1931) (actual time=0.010..0.017 rows=0 loops=1)

Index Cond: (id = '3011852315482470422'::bigint)

Buffers: shared hit=2

Planning:

Buffers: shared hit=292

Planning Time: 10.351 ms

Execution Time: 0.283 ms

Below are the workarounds that we have tried to get the desired results,

1. We have tried fine-tuning the below parameters with all possible values to get the expected results but got no luck,

Parameter	setting	unit
enable_parallel_append	on
enable_parallel_hash	on
force_parallel_mode	off
max_parallel_maintenance_workers	2
max_parallel_workers	8
max_parallel_workers_per_gather	2
min_parallel_index_scan_size	64	8kB
min_parallel_table_scan_size	1024	8kB
parallel_leader_participation	on
parallel_setup_cost	1000
parallel_tuple_cost	0.1
effective_cache_size	4GB
shared_buffers	128MB
work_mem	4MB

2. Performed VACUUM ANALYZE on the partitioned tables

Kindly help us to improve the mentioned query performance by picking up PARALLEL INDEX SCAN with PARALLEL APPEND node.

Thanks & Regards

Vivekk

PostgreSQL DBA

Disclaimer : This email (including any enclosed documents) is only intended for the person(s) to whom it is addressed and may have confidential information. Unless stated to the contrary, any opinions or comments are personal to the writer and do not represent the official view of the company. If you have received this email in error, please notify the sender immediately by reply email. Also destroy all the electronic copies by deleting the email irretrievably from your system and paper copies, if any, by shredding the same. Please do not copy this email, use it for any purposes, or disclose its contents to any other person. Any person communicating with the company by email will be deemed to have accepted the risks associated with sending information by email being interception, amendment, and loss as well as the consequences of incomplete or late delivery. Information contained in this email and any attachments may be privileged or confidential and intended for the exclusive use of the original recipient. Mistransmission is not intended to waive confidentiality or privilege.

To learn more about how we collect and process your private and confidential information, please view our Privacy Policy. If you cannot access the link, please notify us at security@zeta.tech and we will send the contents to you. By communicating with our company you acknowledge that you have read, understood and have consented (wherever applicable), to the forgoing and our company’s general disclaimers.

Re: Need recommendation on PARALLEL INDEX SCAN and PARALLEL APPEND

From

Vijaykumar Jain

Date:

26 October 2021, 10:18:15

On Tue, 26 Oct 2021 at 11:39, Vivekk P <vivekkp@zeta.tech> wrote:

Hi Team,

Please have a look on the below problem statement and suggest us if there are any ways to make the planner pick PARALLEL INDEX SCAN and PARALLEL APPEND

1. We have tried fine-tuning the below parameters with all possible values to get the expected results but got no luck,

Parameter setting unit
enable_parallel_append on
enable_parallel_hash on
force_parallel_mode off
max_parallel_maintenance_workers 2
max_parallel_workers 8
max_parallel_workers_per_gather 2
min_parallel_index_scan_size 64 8kB
min_parallel_table_scan_size 1024 8kB
parallel_leader_participation on
parallel_setup_cost 1000
parallel_tuple_cost 0.1
effective_cache_size 4GB
shared_buffers 128MB
work_mem 4MB

I am pretty sure there will be questions on why you want to do that or why you think this would solve any problem.

anyways,

This is just to force a parallel run, but do not do this in production.

The way we try this here, is to trick the optimizer by saying there is no cost of making use of parallel setup, so this is always the best path.(which is wrong, but..)

postgres=# select name,setting from pg_settings where name like '%para%';

name | setting

-----------------------------------+---------

enable_parallel_append | on

enable_parallel_hash | on

force_parallel_mode | off

log_parameter_max_length | -1

log_parameter_max_length_on_error | 0

max_parallel_maintenance_workers | 2

max_parallel_workers | 8

max_parallel_workers_per_gather | 2

min_parallel_index_scan_size | 64

min_parallel_table_scan_size | 1024

parallel_leader_participation | on

parallel_setup_cost | 1000

parallel_tuple_cost | 0.1

ssl_dh_params_file |

(14 rows)

postgres=# set force_parallel_mode to on;

SET

postgres=# set parallel_setup_cost to 0;

SET

postgres=# set parallel_tuple_cost to 0;

SET

postgres=# explain analyze select * from t where id > 0;

QUERY PLAN

------------------------------------------------------------------------------------------------------------------------

Gather (cost=0.00..3.76 rows=80 width=12) (actual time=2.900..5.996 rows=80 loops=1)

Workers Planned: 2

Workers Launched: 2

-> Parallel Append (cost=0.00..3.76 rows=34 width=12) (actual time=0.002..0.009 rows=27 loops=3)

-> Parallel Seq Scan on t2 t_2 (cost=0.00..1.23 rows=18 width=12) (actual time=0.005..0.009 rows=31 loops=1)

Filter: (id > 0)

-> Parallel Seq Scan on t1 t_1 (cost=0.00..1.21 rows=17 width=12) (actual time=0.004..0.006 rows=29 loops=1)

Filter: (id > 0)

-> Parallel Seq Scan on t3 t_3 (cost=0.00..1.15 rows=12 width=12) (actual time=0.001..0.003 rows=20 loops=1)

Filter: (id > 0)

Planning Time: 0.568 ms

Execution Time: 6.022 ms

(12 rows)

postgres=# set seq_page_cost to 100000; --- since we do not want seq scan but index scan

SET

postgres=# explain analyze select * from t where id > 0;

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------

Gather (cost=0.14..37.65 rows=80 width=12) (actual time=0.232..5.326 rows=80 loops=1)

Workers Planned: 2

Workers Launched: 2

-> Parallel Append (cost=0.14..37.65 rows=34 width=12) (actual time=0.007..0.020 rows=27 loops=3)

-> Parallel Index Only Scan using t2_ts_id_idx on t2 t_2 (cost=0.14..12.55 rows=18 width=12) (actual time=0.005..0.012 rows=31 loops=1)

Index Cond: (id > 0)

Heap Fetches: 31

-> Parallel Index Only Scan using t1_ts_id_idx on t1 t_1 (cost=0.14..12.53 rows=17 width=12) (actual time=0.007..0.013 rows=29 loops=1)

Index Cond: (id > 0)

Heap Fetches: 29

-> Parallel Index Only Scan using t3_ts_id_idx on t3 t_3 (cost=0.14..12.41 rows=12 width=12) (actual time=0.019..0.025 rows=20 loops=1)

Index Cond: (id > 0)

Heap Fetches: 20

Planning Time: 0.095 ms

Execution Time: 5.351 ms

(15 rows)

Again, do not do this in production. This is only for debugging purposes using 0 cost.

You can try looking at pg_hint_plan (osdn.jp) if you want to force a plan.

Also, be ready to answer, why do you want to do this or what makes you think the parallel option will work magic.

also with TB sized dbs, pls ensure your disk io/latency etc are not a problem.

maybe also bump memory and tune accordingly, to absorb disk io.

Re: Need recommendation on PARALLEL INDEX SCAN and PARALLEL APPEND

From

Michael Lewis

Date:

27 October 2021, 02:45:53

Planning Time: 10.351 ms
Execution Time: 0.283 ms

Nearly all of the time is in planning... What is your default_statistics_target?