Need recommendation on PARALLEL INDEX SCAN and PARALLEL APPEND
| От | Vivekk P |
|---|---|
| Тема | Need recommendation on PARALLEL INDEX SCAN and PARALLEL APPEND |
| Дата | |
| Msg-id | CAK-uzPsXpJVsM-w+6LNccR_JXVdMNb9yoYqpa-y9Jq4wOwxOQQ@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Need recommendation on PARALLEL INDEX SCAN and PARALLEL APPEND
(Vijaykumar Jain <vijaykumarjain.github@gmail.com>)
Re: Need recommendation on PARALLEL INDEX SCAN and PARALLEL APPEND (Michael Lewis <mlewis@entrata.com>) |
| Список | pgsql-general |
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 :
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 |
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 |
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.
В списке pgsql-general по дате отправления: