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?