Im currently testing my query with modifying the max_parallel_workers_per_gather parameter. Before running my query, i set the parameter to
set max_parallel_workers_per_gather = 2
and running my query
explain (analyze,
verbose,
buffers)
select
...
from
tbl_task a
left join tbl_task_download b on
a.task_id = b.task_id
inner join (
select
*
from
tbl_site
where
area in (
select
area
from
tbl_user_area
where
user_id = '20160801604')
and cluster in (
select
cluster
from
tbl_user_cluster
where
user_id = '20160801604')) c on
a.site_id = c.site_id
inner join tbl_user_division c4 on
upper(a.division) = upper(c4.division)
and c4.user_id = '20160801604'
where
( (a.it_status = '0')
and (a.task_type in ('Movement+Collection', 'Replacement', 'Physical Count', 'Maintenance', 'Scrap On Site')
and upper(a.vendor) in (
select
upper(vendor)
from
tbl_user_vendor
where
user_id = '20160801604'))
or (a.status like 'Approved%' ))
and (a.team_count= 0
and a.status = '-')
order by
last_update desc;
and get the following query plan (3.5 secs)
Unique (cost=203829.93..205869.32 rows=8110 width=351) (actual time=3351.460..3591.724 rows=34634 loops=1)
Output: a.id, a.asset_status, a.project_id, a.task_id, a.phase, a.site_id, a.attachment_file, a.plan_date, a.it_status, a.status, a.remark, a.task_type, (to_char(a.last_update, 'YYYY-MM-DD HH24:MI'::text)), a.last_updater_id, a.task_id_reference, a.source_site_id, a.destination, a.vendor, a.admin_id, a.system_key, a.po_service_number, a.ne_id, a.atf_no, a.longitude, a.latitude, a.package_status, a.reopen_status, a.reopen_iom_number, a.reopen_remark, a.site_id_destination, a.origin_pic_engineer, a.destination_pic_engineer, a.work_type, a.po_service_number, a.division, a.pm_indosat, a.creator_id, a.network, a.engineer_submitted, a.site_id_ori, a.ne_id_old, a.actual_inbound_date, a.planned_inbound_date, a.pm_vendor, a.actual_outbound_date, a.outbound_pickup_name, a.outbound_pickup_phone, a.mr_number, b.download_user_id, a.team_count, a.do_number, a.permission_status, a.caf_status, a.ms_site_status, a.isr_status, a.pln_shutdown_status, tbl_site.site_ownership
Buffers: shared hit=106013 read=166673 written=261, temp read=1227 written=1230
-> Gather Merge (cost=203829.93..204774.47 rows=8110 width=351) (actual time=3351.456..3433.822 rows=34634 loops=1)
Output: a.id, a.asset_status, a.project_id, a.task_id, a.phase, a.site_id, a.attachment_file, a.plan_date, a.it_status, a.status, a.remark, a.task_type, (to_char(a.last_update, 'YYYY-MM-DD HH24:MI'::text)), a.last_updater_id, a.task_id_reference, a.source_site_id, a.destination, a.vendor, a.admin_id, a.system_key, a.po_service_number, a.ne_id, a.atf_no, a.longitude, a.latitude, a.package_status, a.reopen_status, a.reopen_iom_number, a.reopen_remark, a.site_id_destination, a.origin_pic_engineer, a.destination_pic_engineer, a.work_type, a.po_service_number, a.division, a.pm_indosat, a.creator_id, a.network, a.engineer_submitted, a.site_id_ori, a.ne_id_old, a.actual_inbound_date, a.planned_inbound_date, a.pm_vendor, a.actual_outbound_date, a.outbound_pickup_name, a.outbound_pickup_phone, a.mr_number, b.download_user_id, a.team_count, a.do_number, a.permission_status, a.caf_status, a.ms_site_status, a.isr_status, a.pln_shutdown_status, tbl_site.site_ownership
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=106013 read=166673 written=261, temp read=1227 written=1230
-> Sort (cost=202829.90..202838.35 rows=3379 width=351) (actual time=3260.872..3268.208 rows=11545 loops=3)
Output: a.id, a.asset_status, a.project_id, a.task_id, a.phase, a.site_id, a.attachment_file, a.plan_date, a.it_status, a.status, a.remark, a.task_type, (to_char(a.last_update, 'YYYY-MM-DD HH24:MI'::text)), a.last_updater_id, a.task_id_reference, a.source_site_id, a.destination, a.vendor, a.admin_id, a.system_key, a.po_service_number, a.ne_id, a.atf_no, a.longitude, a.latitude, a.package_status, a.reopen_status, a.reopen_iom_number, a.reopen_remark, a.site_id_destination, a.origin_pic_engineer, a.destination_pic_engineer, a.work_type, a.po_service_number, a.division, a.pm_indosat, a.creator_id, a.network, a.engineer_submitted, a.site_id_ori, a.ne_id_old, a.actual_inbound_date, a.planned_inbound_date, a.pm_vendor, a.actual_outbound_date, a.outbound_pickup_name, a.outbound_pickup_phone, a.mr_number, b.download_user_id, a.team_count, a.do_number, a.permission_status, a.caf_status, a.ms_site_status, a.isr_status, a.pln_shutdown_status, tbl_site.site_ownership
Sort Key: (to_char(a.last_update, 'YYYY-MM-DD HH24:MI'::text)) DESC, a.id, a.asset_status, a.project_id, a.task_id, a.phase, a.site_id, a.attachment_file, a.plan_date, a.it_status, a.remark, a.task_type, a.last_updater_id, a.task_id_reference, a.source_site_id, a.destination, a.vendor, a.admin_id, a.system_key, a.po_service_number, a.ne_id, a.atf_no, a.longitude, a.latitude, a.package_status, a.reopen_status, a.reopen_iom_number, a.reopen_remark, a.site_id_destination, a.origin_pic_engineer, a.destination_pic_engineer, a.work_type, a.division, a.pm_indosat, a.creator_id, a.network, a.engineer_submitted, a.site_id_ori, a.ne_id_old, a.actual_inbound_date, a.planned_inbound_date, a.pm_vendor, a.actual_outbound_date, a.outbound_pickup_name, a.outbound_pickup_phone, a.mr_number, b.download_user_id, a.do_number, a.permission_status, a.caf_status, a.ms_site_status, a.isr_status, a.pln_shutdown_status, tbl_site.site_ownership
Sort Method: external merge Disk: 3424kB
Buffers: shared hit=106013 read=166673 written=261, temp read=1227 written=1230
Worker 0: actual time=3229.846..3237.542 rows=12048 loops=1
Sort Method: external merge Disk: 3416kB
Buffers: shared hit=36813 read=55728 written=79, temp read=427 written=428
Worker 1: actual time=3203.348..3210.268 rows=10484 loops=1
Sort Method: external merge Disk: 2976kB
Buffers: shared hit=32201 read=52421 written=74, temp read=372 written=373
-> Hash Join (cost=9018.95..202631.85 rows=3379 width=351) (actual time=609.357..3169.556 rows=11545 loops=3)
Output: a.id, a.asset_status, a.project_id, a.task_id, a.phase, a.site_id, a.attachment_file, a.plan_date, a.it_status, a.status, a.remark, a.task_type, to_char(a.last_update, 'YYYY-MM-DD HH24:MI'::text), a.last_updater_id, a.task_id_reference, a.source_site_id, a.destination, a.vendor, a.admin_id, a.system_key, a.po_service_number, a.ne_id, a.atf_no, a.longitude, a.latitude, a.package_status, a.reopen_status, a.reopen_iom_number, a.reopen_remark, a.site_id_destination, a.origin_pic_engineer, a.destination_pic_engineer, a.work_type, a.po_service_number, a.division, a.pm_indosat, a.creator_id, a.network, a.engineer_submitted, a.site_id_ori, a.ne_id_old, a.actual_inbound_date, a.planned_inbound_date, a.pm_vendor, a.actual_outbound_date, a.outbound_pickup_name, a.outbound_pickup_phone, a.mr_number, b.download_user_id, a.team_count, a.do_number, a.permission_status, a.caf_status, a.ms_site_status, a.isr_status, a.pln_shutdown_status, tbl_site.site_ownership
Inner Unique: true
Hash Cond: ((tbl_site.cluster)::text = tbl_user_cluster.cluster)
Buffers: shared hit=105904 read=166672 written=260
Worker 0: actual time=561.601..3140.316 rows=12048 loops=1
Buffers: shared hit=36759 read=55727 written=78
Worker 1: actual time=586.102..3100.667 rows=10484 loops=1
Buffers: shared hit=32146 read=52421 written=74
-> Nested Loop Left Join (cost=6775.40..200333.39 rows=3379 width=341) (actual time=601.587..3139.865 rows=11954 loops=3)
Output: a.id, a.asset_status, a.project_id, a.task_id, a.phase, a.site_id, a.attachment_file, a.plan_date, a.it_status, a.status, a.remark, a.task_type, a.last_update, a.last_updater_id, a.task_id_reference, a.source_site_id, a.destination, a.vendor, a.admin_id, a.system_key, a.po_service_number, a.ne_id, a.atf_no, a.longitude, a.latitude, a.package_status, a.reopen_status, a.reopen_iom_number, a.reopen_remark, a.site_id_destination, a.origin_pic_engineer, a.destination_pic_engineer, a.work_type, a.division, a.pm_indosat, a.creator_id, a.network, a.engineer_submitted, a.site_id_ori, a.ne_id_old, a.actual_inbound_date, a.planned_inbound_date, a.pm_vendor, a.actual_outbound_date, a.outbound_pickup_name, a.outbound_pickup_phone, a.mr_number, a.team_count, a.do_number, a.permission_status, a.caf_status, a.ms_site_status, a.isr_status, a.pln_shutdown_status, b.download_user_id, tbl_site.site_ownership, tbl_site.cluster
Inner Unique: true
Buffers: shared hit=105841 read=166643 written=254
Worker 0: actual time=558.988..3115.695 rows=12438 loops=1
Buffers: shared hit=36728 read=55726 written=78
Worker 1: actual time=583.672..3076.721 rows=10875 loops=1
Buffers: shared hit=32114 read=52421 written=74
-> Hash Join (cost=6774.97..196797.56 rows=3379 width=329) (actual time=601.423..2320.235 rows=11954 loops=3)
Output: a.id, a.asset_status, a.project_id, a.task_id, a.phase, a.site_id, a.attachment_file, a.plan_date, a.it_status, a.status, a.remark, a.task_type, a.last_update, a.last_updater_id, a.task_id_reference, a.source_site_id, a.destination, a.vendor, a.admin_id, a.system_key, a.po_service_number, a.ne_id, a.atf_no, a.longitude, a.latitude, a.package_status, a.reopen_status, a.reopen_iom_number, a.reopen_remark, a.site_id_destination, a.origin_pic_engineer, a.destination_pic_engineer, a.work_type, a.division, a.pm_indosat, a.creator_id, a.network, a.engineer_submitted, a.site_id_ori, a.ne_id_old, a.actual_inbound_date, a.planned_inbound_date, a.pm_vendor, a.actual_outbound_date, a.outbound_pickup_name, a.outbound_pickup_phone, a.mr_number, a.team_count, a.do_number, a.permission_status, a.caf_status, a.ms_site_status, a.isr_status, a.pln_shutdown_status, tbl_site.site_ownership, tbl_site.cluster
Hash Cond: (upper((a.division)::text) = upper(c4.division))
Buffers: shared hit=91 read=164758 written=36
Worker 0: actual time=558.752..2288.718 rows=12438 loops=1
Buffers: shared hit=43 read=55072 written=7
Worker 1: actual time=583.550..2291.358 rows=10875 loops=1
Buffers: shared hit=48 read=51849 written=10
-> Parallel Hash Join (cost=6586.56..196280.96 rows=10240 width=329) (actual time=599.042..2272.164 rows=12118 loops=3)
Output: a.id, a.asset_status, a.project_id, a.task_id, a.phase, a.site_id, a.attachment_file, a.plan_date, a.it_status, a.status, a.remark, a.task_type, a.last_update, a.last_updater_id, a.task_id_reference, a.source_site_id, a.destination, a.vendor, a.admin_id, a.system_key, a.po_service_number, a.ne_id, a.atf_no, a.longitude, a.latitude, a.package_status, a.reopen_status, a.reopen_iom_number, a.reopen_remark, a.site_id_destination, a.origin_pic_engineer, a.destination_pic_engineer, a.work_type, a.division, a.pm_indosat, a.creator_id, a.network, a.engineer_submitted, a.site_id_ori, a.ne_id_old, a.actual_inbound_date, a.planned_inbound_date, a.pm_vendor, a.actual_outbound_date, a.outbound_pickup_name, a.outbound_pickup_phone, a.mr_number, a.team_count, a.do_number, a.permission_status, a.caf_status, a.ms_site_status, a.isr_status, a.pln_shutdown_status, tbl_site.site_ownership, tbl_site.cluster
Hash Cond: (a.site_id = tbl_site.site_id)
...
Planning Time: 17.879 ms
Execution Time: 3597.396 ms
but then i set it to 8, which allows more workers to work on the parallel process in the query
set max_parallel_workers_per_gather = 8
but i get an even longer query execution (15 secs)
Unique (cost=183688.95..185764.50 rows=8110 width=351) (actual time=14823.942..15048.532 rows=34634 loops=1)
Output: a.id, a.asset_status, a.project_id, a.task_id, a.phase, a.site_id, a.attachment_file, a.plan_date, a.it_status, a.status, a.remark, a.task_type, (to_char(a.last_update, 'YYYY-MM-DD HH24:MI'::text)), a.last_updater_id, a.task_id_reference, a.source_site_id, a.destination, a.vendor, a.admin_id, a.system_key, a.po_service_number, a.ne_id, a.atf_no, a.longitude, a.latitude, a.package_status, a.reopen_status, a.reopen_iom_number, a.reopen_remark, a.site_id_destination, a.origin_pic_engineer, a.destination_pic_engineer, a.work_type, a.po_service_number, a.division, a.pm_indosat, a.creator_id, a.network, a.engineer_submitted, a.site_id_ori, a.ne_id_old, a.actual_inbound_date, a.planned_inbound_date, a.pm_vendor, a.actual_outbound_date, a.outbound_pickup_name, a.outbound_pickup_phone, a.mr_number, b.download_user_id, a.team_count, a.do_number, a.permission_status, a.caf_status, a.ms_site_status, a.isr_status, a.pln_shutdown_status, tbl_site.site_ownership
Buffers: shared hit=9708801 read=169477 written=1059
-> Gather Merge (cost=183688.95..184669.65 rows=8110 width=351) (actual time=14823.941..14890.674 rows=34634 loops=1)
Output: a.id, a.asset_status, a.project_id, a.task_id, a.phase, a.site_id, a.attachment_file, a.plan_date, a.it_status, a.status, a.remark, a.task_type, (to_char(a.last_update, 'YYYY-MM-DD HH24:MI'::text)), a.last_updater_id, a.task_id_reference, a.source_site_id, a.destination, a.vendor, a.admin_id, a.system_key, a.po_service_number, a.ne_id, a.atf_no, a.longitude, a.latitude, a.package_status, a.reopen_status, a.reopen_iom_number, a.reopen_remark, a.site_id_destination, a.origin_pic_engineer, a.destination_pic_engineer, a.work_type, a.po_service_number, a.division, a.pm_indosat, a.creator_id, a.network, a.engineer_submitted, a.site_id_ori, a.ne_id_old, a.actual_inbound_date, a.planned_inbound_date, a.pm_vendor, a.actual_outbound_date, a.outbound_pickup_name, a.outbound_pickup_phone, a.mr_number, b.download_user_id, a.team_count, a.do_number, a.permission_status, a.caf_status, a.ms_site_status, a.isr_status, a.pln_shutdown_status, tbl_site.site_ownership
Workers Planned: 5
Workers Launched: 5
Buffers: shared hit=9708801 read=169477 written=1059
-> Sort (cost=182688.87..182692.92 rows=1622 width=351) (actual time=14691.192..14692.035 rows=5772 loops=6)
Output: a.id, a.asset_status, a.project_id, a.task_id, a.phase, a.site_id, a.attachment_file, a.plan_date, a.it_status, a.status, a.remark, a.task_type, (to_char(a.last_update, 'YYYY-MM-DD HH24:MI'::text)), a.last_updater_id, a.task_id_reference, a.source_site_id, a.destination, a.vendor, a.admin_id, a.system_key, a.po_service_number, a.ne_id, a.atf_no, a.longitude, a.latitude, a.package_status, a.reopen_status, a.reopen_iom_number, a.reopen_remark, a.site_id_destination, a.origin_pic_engineer, a.destination_pic_engineer, a.work_type, a.po_service_number, a.division, a.pm_indosat, a.creator_id, a.network, a.engineer_submitted, a.site_id_ori, a.ne_id_old, a.actual_inbound_date, a.planned_inbound_date, a.pm_vendor, a.actual_outbound_date, a.outbound_pickup_name, a.outbound_pickup_phone, a.mr_number, b.download_user_id, a.team_count, a.do_number, a.permission_status, a.caf_status, a.ms_site_status, a.isr_status, a.pln_shutdown_status, tbl_site.site_ownership
Sort Key: (to_char(a.last_update, 'YYYY-MM-DD HH24:MI'::text)) DESC, a.id, a.asset_status, a.project_id, a.task_id, a.phase, a.site_id, a.attachment_file, a.plan_date, a.it_status, a.remark, a.task_type, a.last_updater_id, a.task_id_reference, a.source_site_id, a.destination, a.vendor, a.admin_id, a.system_key, a.po_service_number, a.ne_id, a.atf_no, a.longitude, a.latitude, a.package_status, a.reopen_status, a.reopen_iom_number, a.reopen_remark, a.site_id_destination, a.origin_pic_engineer, a.destination_pic_engineer, a.work_type, a.division, a.pm_indosat, a.creator_id, a.network, a.engineer_submitted, a.site_id_ori, a.ne_id_old, a.actual_inbound_date, a.planned_inbound_date, a.pm_vendor, a.actual_outbound_date, a.outbound_pickup_name, a.outbound_pickup_phone, a.mr_number, b.download_user_id, a.do_number, a.permission_status, a.caf_status, a.ms_site_status, a.isr_status, a.pln_shutdown_status, tbl_site.site_ownership
Sort Method: quicksort Memory: 3084kB
Buffers: shared hit=9708801 read=169477 written=1059
Worker 0: actual time=14698.370..14699.207 rows=5761 loops=1
Sort Method: quicksort Memory: 3068kB
Buffers: shared hit=1616768 read=28740 written=167
Worker 1: actual time=14679.332..14680.210 rows=5828 loops=1
Sort Method: quicksort Memory: 3082kB
Buffers: shared hit=1624016 read=27992 written=167
Worker 2: actual time=14636.175..14636.994 rows=5689 loops=1
Sort Method: quicksort Memory: 3020kB
Buffers: shared hit=1596358 read=25119 written=166
Worker 3: actual time=14612.967..14613.808 rows=5756 loops=1
Sort Method: quicksort Memory: 3057kB
Buffers: shared hit=1613350 read=26074 written=173
Worker 4: actual time=14698.627..14699.513 rows=5810 loops=1
Sort Method: quicksort Memory: 3078kB
Buffers: shared hit=1634697 read=28244 written=201
-> Hash Join (cost=2560.55..182602.39 rows=1622 width=351) (actual time=295.643..14653.666 rows=5772 loops=6)
Output: a.id, a.asset_status, a.project_id, a.task_id, a.phase, a.site_id, a.attachment_file, a.plan_date, a.it_status, a.status, a.remark, a.task_type, to_char(a.last_update, 'YYYY-MM-DD HH24:MI'::text), a.last_updater_id, a.task_id_reference, a.source_site_id, a.destination, a.vendor, a.admin_id, a.system_key, a.po_service_number, a.ne_id, a.atf_no, a.longitude, a.latitude, a.package_status, a.reopen_status, a.reopen_iom_number, a.reopen_remark, a.site_id_destination, a.origin_pic_engineer, a.destination_pic_engineer, a.work_type, a.po_service_number, a.division, a.pm_indosat, a.creator_id, a.network, a.engineer_submitted, a.site_id_ori, a.ne_id_old, a.actual_inbound_date, a.planned_inbound_date, a.pm_vendor, a.actual_outbound_date, a.outbound_pickup_name, a.outbound_pickup_phone, a.mr_number, b.download_user_id, a.team_count, a.do_number, a.permission_status, a.caf_status, a.ms_site_status, a.isr_status, a.pln_shutdown_status, tbl_site.site_ownership
Inner Unique: true
Hash Cond: ((tbl_site.cluster)::text = tbl_user_cluster.cluster)
Buffers: shared hit=9708527 read=169476 written=1059
Worker 0: actual time=310.293..14660.461 rows=5761 loops=1
Buffers: shared hit=1616714 read=28739 written=167
Worker 1: actual time=282.411..14641.317 rows=5828 loops=1
Buffers: shared hit=1623961 read=27992 written=167
Worker 2: actual time=239.919..14598.755 rows=5689 loops=1
Buffers: shared hit=1596303 read=25119 written=166
Worker 3: actual time=220.905..14576.578 rows=5756 loops=1
Buffers: shared hit=1613295 read=26074 written=173
Worker 4: actual time=296.406..14660.473 rows=5810 loops=1
Buffers: shared hit=1634642 read=28244 written=201
-> Nested Loop Left Join (cost=317.00..180332.48 rows=1622 width=341) (actual time=293.244..14634.763 rows=5977 loops=6)
Output: a.id, a.asset_status, a.project_id, a.task_id, a.phase, a.site_id, a.attachment_file, a.plan_date, a.it_status, a.status, a.remark, a.task_type, a.last_update, a.last_updater_id, a.task_id_reference, a.source_site_id, a.destination, a.vendor, a.admin_id, a.system_key, a.po_service_number, a.ne_id, a.atf_no, a.longitude, a.latitude, a.package_status, a.reopen_status, a.reopen_iom_number, a.reopen_remark, a.site_id_destination, a.origin_pic_engineer, a.destination_pic_engineer, a.work_type, a.division, a.pm_indosat, a.creator_id, a.network, a.engineer_submitted, a.site_id_ori, a.ne_id_old, a.actual_inbound_date, a.planned_inbound_date, a.pm_vendor, a.actual_outbound_date, a.outbound_pickup_name, a.outbound_pickup_phone, a.mr_number, a.team_count, a.do_number, a.permission_status, a.caf_status, a.ms_site_status, a.isr_status, a.pln_shutdown_status, b.download_user_id, tbl_site.site_ownership, tbl_site.cluster
Inner Unique: true
Buffers: shared hit=9708383 read=169447 written=1056
Worker 0: actual time=307.929..14641.388 rows=5972 loops=1
Buffers: shared hit=1616685 read=28739 written=167
Worker 1: actual time=280.008..14622.103 rows=5999 loops=1
Buffers: shared hit=1623932 read=27992 written=167
Worker 2: actual time=237.554..14579.018 rows=5896 loops=1
Buffers: shared hit=1596274 read=25119 written=166
Worker 3: actual time=218.549..14559.074 rows=5959 loops=1
Buffers: shared hit=1613266 read=26074 written=173
Worker 4: actual time=293.611..14640.539 rows=6038 loops=1
Buffers: shared hit=1634642 read=28215 written=198
-> Nested Loop (cost=316.58..178635.19 rows=1622 width=329) (actual time=293.070..14232.807 rows=5977 loops=6)
Output: a.id, a.asset_status, a.project_id, a.task_id, a.phase, a.site_id, a.attachment_file, a.plan_date, a.it_status, a.status, a.remark, a.task_type, a.last_update, a.last_updater_id, a.task_id_reference, a.source_site_id, a.destination, a.vendor, a.admin_id, a.system_key, a.po_service_number, a.ne_id, a.atf_no, a.longitude, a.latitude, a.package_status, a.reopen_status, a.reopen_iom_number, a.reopen_remark, a.site_id_destination, a.origin_pic_engineer, a.destination_pic_engineer, a.work_type, a.division, a.pm_indosat, a.creator_id, a.network, a.engineer_submitted, a.site_id_ori, a.ne_id_old, a.actual_inbound_date, a.planned_inbound_date, a.pm_vendor, a.actual_outbound_date, a.outbound_pickup_name, a.outbound_pickup_phone, a.mr_number, a.team_count, a.do_number, a.permission_status, a.caf_status, a.ms_site_status, a.isr_status, a.pln_shutdown_status, tbl_site.site_ownership, tbl_site.cluster
Join Filter: (tbl_user_area.area = (tbl_site.area)::text)
Rows Removed by Join Filter: 394471
Buffers: shared hit=9603173 read=167019 written=863
Worker 0: actual time=307.810..14257.292 rows=5972 loops=1
Buffers: shared hit=1599169 read=28334 written=142
Worker 1: actual time=279.748..14232.878 rows=5999 loops=1
Buffers: shared hit=1606372 read=27548 written=134
Worker 2: actual time=237.309..14174.349 rows=5896 loops=1
Buffers: shared hit=1578935 read=24756 written=132
Worker 3: actual time=218.433..14107.372 rows=5959 loops=1
Buffers: shared hit=1595795 read=25658 written=133
Worker 4: actual time=293.390..14250.929 rows=6038 loops=1
Buffers: shared hit=1616919 read=27814 written=161
-> Hash Join (cost=316.16..174581.47 rows=5163 width=316) (actual time=291.359..2101.032 rows=400448 loops=6)
Output: a.id, a.asset_status, a.project_id, a.task_id, a.phase, a.site_id, a.attachment_file, a.plan_date, a.it_status, a.status, a.remark, a.task_type, a.last_update, a.last_updater_id, a.task_id_reference, a.source_site_id, a.destination, a.vendor, a.admin_id, a.system_key, a.po_service_number, a.ne_id, a.atf_no, a.longitude, a.latitude, a.package_status, a.reopen_status, a.reopen_iom_number, a.reopen_remark, a.site_id_destination, a.origin_pic_engineer, a.destination_pic_engineer, a.work_type, a.division, a.pm_indosat, a.creator_id, a.network, a.engineer_submitted, a.site_id_ori, a.ne_id_old, a.actual_inbound_date, a.planned_inbound_date, a.pm_vendor, a.actual_outbound_date, a.outbound_pickup_name, a.outbound_pickup_phone, a.mr_number, a.team_count, a.do_number, a.permission_status, a.caf_status, a.ms_site_status, a.isr_status, a.pln_shutdown_status, tbl_user_area.area
Hash Cond: (upper((a.division)::text) = upper(c4.division))
Buffers: shared hit=206 read=159233 written=72
Worker 0: actual time=306.107..2118.540 rows=400124 loops=1
Buffers: shared hit=39 read=26967 written=5
Worker 1: actual time=277.808..2088.363 rows=401933 loops=1
...
-> Index Scan using site_idx on public.tbl_site (cost=0.42..0.77 rows=1 width=51) (actual time=0.030..0.030 rows=1 loops=2402687)
...
Planning:
Buffers: shared hit=36 read=12
Planning Time: 6.762 ms
Execution Time: 15051.213 ms
Note that i have cleared my query cached in between running the first and second time with different max_parallel_workers_per_gather setting.
I noticed there is a change of plan that results 2402687x loops at "Index Scan using site_idx on public.tbl_site" that makes the query much slower.
Is it true that lowering max_parallel_workers_per_gather actually has benefit of improving query speed?
Is it true that lowering max_parallel_workers_per_gather actually has benefit of improving query speed?
Since you are staring at the evidence, obviously it is true that this can happen.
When you increase max_parallel_workers_per_gather, I think you are implicitly saying that you have enough IO capacity that you can fruitfully have at least that many random read requests outstanding at the same time without them substantially competing with each other. If that is not true (or much of the capacity is already being consumed by other queries or background tasks), then it is easy to see why it would switch to a more random-IO intensive plan, even when that plan cannot deliver on its expectations.