Hi All, I have been fine-tuning this below query. There are four large tables earlier it was choosing "Merge-join" but i have vacuumed and analyzed then now it is choosing "nested loop join" also i created composite key index to optimize it but the index is not taking it. i could see very low improvement from 27 minutes into 13+ minutes now. Could you please someone suggest me to improve this query . this data has been migrated from AWS redshift into AWS Aurora (postgreSQL ) DB. explain select distinct cth.contact_id cntct_id, cth.activity_datetime actn_dt_tm, cth.record_update_datetime updt_ts, cth.transaction_status_id trans_status_id, cts.session_id sessn_id, utm_content_text, utm_campaign_text, utm_mdm_text, utm_source_text, utm_trm_text, email_address, ssr.source_system_code src_sys_cd, ssr.source_system_name, c.mdm_contact_id, first_name, last_name, ttl.global_lov_code title, sih.Linkedin lnkdn_txt, sih.twitter twtr_txt, sih.skype skpe_txt, sih.facebook fcbk_txt, organization_name, organization_size, job_title, organization_business_volume, ph.income_currency_id, ph.nationality_id, ph.mdm_person_id mdm_prsn_id, sr.series_code, er.edition_code evnt_edtn_cd, br.brand_code, cth.record_update_datetime cth_ts, c.record_update_datetime c_ts, ph.record_update_datetime ph_ts, ctt.record_update_datetime ctt_ts, cts.record_update_datetime cts_ts, ph.record_insert_datetime ph_its from core2020.contact_transaction_history_1521_BKP_AM cth join core2020.contact_1521_BKP_AM c on c.contact_id = cth.contact_id join core2020.person_history_1521_BKP_AM ph on ph.mdm_person_id = c.mdm_contact_id join core2020.contact_transaction_track_1521_BKP_AM ctt on cth.contact_transaction_id = ctt.contact_transaction_id join core2020.contact_transaction_session_1521_BKP_AM cts on ctt.contact_transaction_track_id = cts.contact_transaction_track_id join reference2020.edition_reference2020 er on cth.edition_id = er.edition_id join reference2020.series_reference2020 sr on er.series_id = sr.series_id join reference2020.brand_reference2020 br on sr.brand_id = br.brand_id join reference2020.source_system_reference2020 ssr on cth.source_system_id = ssr.source_system_id join reference2020.relationship_reference2020 rr on ctt.relationship_id =rr.relationship_id left join (select * from reference2020.global_lov_reference2020 where global_lov_name='TITLE') ttl on ttl.global_lov_id = salutation_id left join (select mdm_contact_id, max (CASE WHEN ref.global_lov_value='Linkedin' THEN social_detail ELSE null END) AS Linkedin, Max (CASE WHEN ref.global_lov_value='Twitter' THEN social_detail ELSE null END) AS Twitter, Max (CASE WHEN ref.global_lov_value='Skype' THEN social_detail ELSE null END) AS Skype, Max (CASE WHEN ref.global_lov_value='Facebook' THEN social_detail ELSE null END) AS Facebook from core2020.contact_social_information_1521_BKP_AM si inner join (select * from reference2020.global_lov_reference2020 where global_lov_name = 'MEDIA_SOCIAL_INFORMAION') ref on si.social_information_type_id =ref.global_lov_id where status='Active' group by mdm_contact_id) sih on ph.mdm_person_id = sih.mdm_contact_id where rr.relationship_code = 'ER_SP' and ssr.source_system_code = 'SS_AEM' and er.event_end_date >= '2020-12-18' and ((c.record_update_datetime > '2020-12-18T08:35:34' and c.record_update_datetime <= '2020-12-18 14:00:18.080') or (cth.record_update_datetime > '2020-12-17T09:13:06' and cth.record_update_datetime <= '2020-12-18 14:00:18.080') or (ph.record_update_datetime > '2020-12-18T09:34:27' and ph.record_update_datetime <= '2020-12-18 14:00:18.080') or (ctt.record_update_datetime > '2020-12-17T09:13:52' and ctt.record_update_datetime <= '2020-12-18 14:00:18.080') or (cts.record_update_datetime > '2020-12-17T09:12:20' and cts.record_update_datetime <= '2020-12-18 14:00:18.080')) and er.edition_code in ('EME21LEP','AET20GAT','AEL20SMA','BRA20FFS','BRA20FPT','BRA20FTP','AET20GEX','AET20GFX','AEL20FWA', 'AEL20OHM','GBR21IMA','MTM21TAS','AET20SAH','AET21DFC','AEC20ACS','BRA21IPR','HLN21CPM','AET21ATW','AEL21DML','AET21ABR') order by ph.record_insert_datetime limit 1000 offset 0 #Explain plan output# "Limit (cost=136450717513.61..136450717513.70 rows=1 width=449)" " -> Unique (cost=136450717513.61..136450717513.70 rows=1 width=449)"" -> Sort (cost=136450717513.61..136450717513.62 rows=1 width=449)" " Sort Key: ph.record_insert_datetime, cth.contact_id, cth.activity_datetime, cth.record_update_datetime, cth.transaction_status_id, cts.session_id, cth.utm_content_text, cth.utm_campaign_text, cth.utm_mdm_text, cth.utm_source_text, cth.utm_trm_text, cth.email_address, ssr.source_system_name, c.mdm_contact_id, ph.first_name, ph.last_name, global_lov_reference.global_lov_code, (max((CASE WHEN ((global_lov_reference_1.global_lov_value)::text = 'Linkedin'::text) THEN si.social_detail ELSE NULL::character varying END)::text)), (max((CASE WHEN ((global_lov_reference_1.global_lov_value)::text = 'Twitter'::text) THEN si.social_detail ELSE NULL::character varying END)::text)), (max((CASE WHEN ((global_lov_reference_1.global_lov_value)::text = 'Skype'::text) THEN si.social_detail ELSE NULL::character varying END)::text)), (max((CASE WHEN ((global_lov_reference_1.global_lov_value)::text = 'Facebook'::text) THEN si.social_detail ELSE NULL::character varying END)::text)), ph.organization_name, ph.organization_size, ph.job_title, ph.organization_business_volume, ph.income_currency_id, ph.nationality_id, sr.series_code, er.edition_code, br.brand_code, c.record_update_datetime, ph.record_update_datetime, ctt.record_update_datetime, cts.record_update_datetime" " -> Nested Loop Left Join (cost=29835.02..136450717513.60 rows=1 width=449)" " Join Filter: ((ph.mdm_person_id)::text = (si.mdm_contact_id)::text)" " -> Nested Loop Left Join (cost=2.59..136450687659.61 rows=1 width=313)" " -> Nested Loop (cost=2.18..136450687658.10 rows=1 width=310)" " Join Filter: (ctt.relationship_id = rr.relationship_id)" " -> Nested Loop (cost=2.18..136450687648.74 rows=1 width=314)" " Join Filter: (cth.source_system_id = ssr.source_system_id)" " -> Nested Loop (cost=2.18..136450687639.48 rows=86 width=292)" " -> Nested Loop (cost=1.87..136450687525.94 rows=86 width=285)" " -> Nested Loop (cost=0.58..136450687328.34 rows=86 width=273)" " Join Filter: (((c.mdm_contact_id)::text = (ph.mdm_person_id)::text) AND (((c.record_update_datetime > '2020-12-18 08:35:34'::timestamp without time zone) AND (c.record_update_datetime <= '2020-12-18 14:00:18.08'::timestamp without time zone)) OR ((cth.record_update_datetime > '2020-12-17 09:13:06'::timestamp without time zone) AND (cth.record_update_datetime <= '2020-12-18 14:00:18.08'::timestamp without time zone)) OR ((ph.record_update_datetime > '2020-12-18 09:34:27'::timestamp without time zone) AND (ph.record_update_datetime <= '2020-12-18 14:00:18.08'::timestamp without time zone)) OR ((ctt.record_update_datetime > '2020-12-17 09:13:52'::timestamp without time zone) AND (ctt.record_update_datetime <= '2020-12-18 14:00:18.08'::timestamp without time zone)) OR ((cts.record_update_datetime > '2020-12-17 09:12:20'::timestamp without time zone) AND (cts.record_update_datetime <= '2020-12-18 14:00:18.08'::timestamp without time zone))))" " -> Seq Scan on person_history_1521_bkp_am ph (cost=0.00..652835.20 rows=18263320 width=125)" " -> Materialize (cost=0.58..92471229550.84 rows=60201 width=148)" " -> Nested Loop (cost=0.58..92471229249.83 rows=60201 width=148)" " Join Filter: (ctt.contact_transaction_track_id = cts.contact_transaction_track_id)" " -> Seq Scan on contact_transaction_session_1521_bkp_am cts (cost=0.00..1330903.16 rows=50761316 width=24)" " -> Materialize (cost=0.58..46058319667.10 rows=60954 width=140)" " -> Nested Loop (cost=0.58..46058319362.33 rows=60954 width=140)" " Join Filter: (cth.contact_transaction_id = ctt.contact_transaction_id)" " -> Seq Scan on contact_transaction_track_1521_bkp_am ctt (cost=0.00..1237342.68 rows=51396468 width=28)" " -> Materialize (cost=0.58..96304624.37 rows=59616 width=128)" " -> Nested Loop (cost=0.58..96304326.29 rows=59616 width=128)" " Join Filter: (cth.edition_id = er.edition_id)" " -> Seq Scan on edition_reference er (cost=0.00..8117.85 rows=5 width=21)" " Filter: ((event_end_date >= '2020-12-18'::date) AND ((edition_code)::text = ANY ('{EME21LEP,AET20GAT,AEL20SMA,BRA20FFS,BRA20FPT,BRA20FTP,AET20GEX,AET20GFX,AEL20FWA,AEL20OHM,GBR21IMA,MTM21TAS,AET20SAH,AET21DFC,AEC20ACS,BRA21IPR,HLN21CPM,AET21ATW,AEL21DML,AET21ABR,AET21DBR,HAN20VFA,HAN20VFE,BRA20FRM,AET20GAP,GBR20TEU,AEL21DAH,AET21DF4,AET21DME,BRA20ABF,BRA20FUC,BRA21TEC,BRA20FSV,AEL20GPC,AET20FPN,AET20DNJ,AEL21UFM,GBR20SLM,GBR21IMX,BRZ21FAG,BRZ22FEI,BRA21EXM,AET21GAM,EME21HCK,HLN21VAD}'::text[])))" " -> Materialize (cost=0.58..89117306.15 rows=50268004 width=119)" " -> Nested Loop (cost=0.58..87982348.13 rows=50268004 width=119)" " -> Seq Scan on contact_1521_bkp_am c (cost=0.00..1329661.36 rows=53223536 width=53)" " -> Bitmap Heap Scan on contact_transaction_history_1521_bkp_am cth (cost=0.58..1.61 rows=2 width=74)" " Recheck Cond: (contact_id = c.contact_id)" " -> Bitmap Index Scan on contact_transaction_history_1521_bkp_am_contact_1521_bkp_am_id_ (cost=0.00..0.58 rows=2 width=0)" " Index Cond: (contact_id = c.contact_id)" " -> Bitmap Heap Scan on series_reference sr (cost=1.29..2.30 rows=1 width=24)" " Recheck Cond: (series_id = er.series_id)" " -> Bitmap Index Scan on reference_series_reference_series_id_idx (cost=0.00..1.29 rows=1 width=0)" " Index Cond: (series_id = er.series_id)" " -> Bitmap Heap Scan on brand_reference br (cost=0.31..1.32 rows=1 width=19)" " Recheck Cond: (brand_id = sr.brand_id)" " -> Bitmap Index Scan on brand_reference_pkey (cost=0.00..0.31 rows=1 width=0)" " Index Cond: (brand_id = sr.brand_id)" " -> Materialize (cost=0.00..7.97 rows=1 width=30)" " -> Seq Scan on source_system_reference ssr (cost=0.00..7.96 rows=1 width=30)" " Filter: ((source_system_code)::text = 'SS_AEM'::text)" " -> Seq Scan on relationship_reference rr (cost=0.00..9.35 rows=1 width=8)" " Filter: ((relationship_code)::text = 'ER_SP'::text)" " -> Bitmap Heap Scan on global_lov_reference (cost=0.41..1.43 rows=1 width=11)" " Recheck Cond: (global_lov_id = ph.salutation_id)" " Filter: ((global_lov_name)::text = 'TITLE'::text)" " -> Bitmap Index Scan on reference_global_lov_global_lov_id_idx (cost=0.00..0.41 rows=1 width=0)" " Index Cond: (global_lov_id = ph.salutation_id)" " -> GroupAggregate (cost=29832.43..29845.56 rows=375 width=165)" " Group Key: si.mdm_contact_id" " -> Sort (cost=29832.43..29833.37 rows=375 width=82)" " Sort Key: si.mdm_contact_id" " -> Nested Loop (cost=0.00..29816.40 rows=375 width=82)" " Join Filter: (si.social_information_type_id = global_lov_reference_1.global_lov_id)" " -> Seq Scan on contact_social_information_1521_bkp_am si (cost=0.00..15550.81 rows=141658 width=72)" " Filter: ((status)::text = 'Active'::text)" " -> Materialize (cost=0.00..1520.37 rows=6 width=18)" " -> Seq Scan on global_lov_reference global_lov_reference_1 (cost=0.00..1520.34 rows=6 width=18)" " Filter: ((global_lov_name)::text = 'SOCIAL_MEDIA_INFORMATION'::text)" "Note: An Approved plan was used instead of the minimum cost plan." "SQL Hash: -545855157, Plan Hash: -1875523776, Minimum Cost Plan Hash: -299897002" |
Hi Bodo, I have resolved this complex query by doing small changes. What I observed the optimizer choosing nested loop join and the storage type is in AWS EBS type. So I decided to make random_page_cost=0 also I collected stats + vacuumed as I said before plus now the query performing very good and I made it down from 23 minutes into 3 minutes and 15 sec. set local random_page_cost='0'; set local seq_page_cost='1'; set local work_mem='5GB'; set local maintenance_work_mem='15GB'; set local max_parallel_workers_per_gather='5'; explain select distinct cth.contact_id cntct_id, cth.activity_datetime actn_dt_tm, cth.record_update_datetime updt_ts, cth.transaction_status_id trans_status_id, cts.session_id sessn_id, utm_content_text, utm_campaign_text, utm_mdm_text, utm_source_text, utm_trm_text, email_address, ssr.source_system_code src_sys_cd, ssr.source_system_name, c.mdm_contact_id, first_name, last_name, ttl.global_lov_code title, sih.Linkedin lnkdn_txt, sih.twitter twtr_txt, sih.skype skpe_txt, sih.facebook fcbk_txt, organization_name, organization_size, job_title, organization_business_volume, ph.income_currency_id, ph.nationality_id, ph.mdm_person_id mdm_prsn_id, sr.series_code, er.edition_code evnt_edtn_cd, br.brand_code, cth.record_update_datetime cth_ts, c.record_update_datetime c_ts, ph.record_update_datetime ph_ts, ctt.record_update_datetime ctt_ts, cts.record_update_datetime cts_ts, ph.record_insert_datetime ph_its from core.contact_transaction_history_1521_BKP_AM cth join core.contact_1521_BKP_AM c on c.contact_id = cth.contact_id join core.person_history_1521_BKP_AM ph on ph.mdm_person_id = c.mdm_contact_id join core.contact_transaction_track_1521_BKP_AM ctt on cth.contact_transaction_id = ctt.contact_transaction_id join core.contact_transaction_session_1521_BKP_AM cts on ctt.contact_transaction_track_id = cts.contact_transaction_track_id join reference.edition_reference er on cth.edition_id = er.edition_id join reference.series_reference sr on er.series_id = sr.series_id join reference.brand_reference br on sr.brand_id = br.brand_id join reference.source_system_reference ssr on cth.source_system_id = ssr.source_system_id join reference.relationship_reference rr on ctt.relationship_id =rr.relationship_id left join (select * from reference.global_lov_reference where global_lov_name='TITLE') ttl on ttl.global_lov_id = salutation_id left join (select mdm_contact_id, max (CASE WHEN ref.global_lov_value='Linkedin' THEN social_detail ELSE null END) AS Linkedin, Max (CASE WHEN ref.global_lov_value='Twitter' THEN social_detail ELSE null END) AS Twitter, Max (CASE WHEN ref.global_lov_value='Skype' THEN social_detail ELSE null END) AS Skype, Max (CASE WHEN ref.global_lov_value='Facebook' THEN social_detail ELSE null END) AS Facebook from core.contact_social_information_1521_BKP_AM si inner join (select * from reference.global_lov_reference where global_lov_name = 'SOCIAL_MEDIA_INFORMATION') ref on si.social_information_type_id =ref.global_lov_id where status='Active' group by mdm_contact_id) sih on ph.mdm_person_id = sih.mdm_contact_id where rr.relationship_code = 'ER_SP' and ssr.source_system_code = 'SS_AEM' and er.event_end_date >= '2020-12-18' and ((c.record_update_datetime > '2020-12-18T08:35:34' and c.record_update_datetime <= '2020-12-18 14:00:18.080') or (cth.record_update_datetime > '2020-12-17T09:13:06' and cth.record_update_datetime <= '2020-12-18 14:00:18.080') or (ph.record_update_datetime > '2020-12-18T09:34:27' and ph.record_update_datetime <= '2020-12-18 14:00:18.080') or (ctt.record_update_datetime > '2020-12-17T09:13:52' and ctt.record_update_datetime <= '2020-12-18 14:00:18.080') or (cts.record_update_datetime > '2020-12-17T09:12:20' and cts.record_update_datetime <= '2020-12-18 14:00:18.080')) and er.edition_code in (select edition_code from reference.edition_reference where er.edition_code in ('EME21LEP','AET20GAT','AEL20SMA','BRA20FFS','BRA20FPT','BRA20FTP','AET20GEX','AET20GFX','AEL20FWA', 'AEL20OHM','GBR21IMA','MTM21TAS','AET20SAH','AET21DFC','AEC20ACS','BRA21IPR','HLN21CPM','AET21ATW','AEL21DML','AET21ABR', 'AET21DBR','HAN20VFA','HAN20VFE','BRA20FRM','AET20GAP','GBR20TEU','AEL21DAH','AET21DF4','AET21DME','BRA20ABF','BRA20FUC', 'BRA21TEC','BRA20FSV','AEL20GPC','AET20FPN','AET20DNJ','AEL21UFM','GBR20SLM','GBR21IMX','BRZ21FAG','BRZ22FEI','BRA21EXM','AET21GAM','EME21HCK','HLN21VAD')) order by ph.record_insert_datetime limit 1000 offset 0
now the execution plan as below # " -> Unique (cost=7013189.23..7013189.41 rows=2 width=449)" " -> Sort (cost=7013189.23..7013189.24 rows=2 width=449)" " Sort Key: ph.record_insert_datetime, cth.contact_id, cth.activity_datetime, cth.record_update_datetime, cth.transaction_status_id, cts.session_id, cth.utm_content_text, cth.utm_campaign_text, cth.utm_mdm_text, cth.utm_source_text, cth.utm_trm_text, cth.email_address, ssr.source_system_name, c.mdm_contact_id, ph.first_name, ph.last_name, global_lov_reference.global_lov_code, sih.linkedin, sih.twitter, sih.skype, sih.facebook, ph.organization_name, ph.organization_size, ph.job_title, ph.organization_business_volume, ph.income_currency_id, ph.nationality_id, sr.series_code, er.edition_code, br.brand_code, c.record_update_datetime, ph.record_update_datetime, ctt.record_update_datetime, cts.record_update_datetime" " -> Nested Loop Left Join (cost=4905638.03..7013189.22 rows=2 width=449)" " Join Filter: ((ph.mdm_person_id)::text = (sih.mdm_contact_id)::text)" " -> Nested Loop Left Join (cost=4892150.29..6999630.37 rows=2 width=313)" " -> Nested Loop (cost=4892149.89..6999629.51 rows=2 width=310)" " Join Filter: (ctt.relationship_id = rr.relationship_id)" " -> Index Scan using relationship_reference_pkey on relationship_reference rr (cost=0.26..1.51 rows=1 width=8)" " Filter: ((relationship_code)::text = 'ER_SP'::text)" " -> Nested Loop (cost=4892149.62..6999627.29 rows=57 width=314)" " Join Filter: (cth.source_system_id = ssr.source_system_id)" " -> Index Scan using source_system_reference_indx on source_system_reference ssr (cost=0.27..0.85 rows=1 width=30)" " Index Cond: ((source_system_code)::text = 'SS_AEM'::text)" " -> Nested Loop (cost=4892149.36..6999571.66 rows=4382 width=292)" " -> Nested Loop (cost=4892149.08..6998301.18 rows=4382 width=285)" " -> Hash Join (cost=4892148.81..6997008.54 rows=4382 width=273)" " Hash Cond: ((c.mdm_contact_id)::text = (ph.mdm_person_id)::text)" " Join Filter: (((c.record_update_datetime > '2020-12-18 08:35:34'::timestamp without time zone) AND (c.record_update_datetime <= '2020-12-18 14:00:18.08'::timestamp without time zone)) OR ((cth.record_update_datetime > '2020-12-17 09:13:06'::timestamp without time zone) AND (cth.record_update_datetime <= '2020-12-18 14:00:18.08'::timestamp without time zone)) OR ((ph.record_update_datetime > '2020-12-18 09:34:27'::timestamp without time zone) AND (ph.record_update_datetime <= '2020-12-18 14:00:18.08'::timestamp without time zone)) OR ((ctt.record_update_datetime > '2020-12-17 09:13:52'::timestamp without time zone) AND (ctt.record_update_datetime <= '2020-12-18 14:00:18.08'::timestamp without time zone)) OR ((cts.record_update_datetime > '2020-12-17 09:12:20'::timestamp without time zone) AND (cts.record_update_datetime <= '2020-12-18 14:00:18.08'::timestamp without time zone)))" " -> Hash Join (cost=4011022.11..5931667.38 rows=3070241 width=148)" " Hash Cond: (cth.contact_id = c.contact_id)" " -> Hash Join (cost=2016066.55..3694930.34 rows=3070241 width=103)" " Hash Cond: (cts.contact_transaction_track_id = ctt.contact_transaction_track_id)" " -> Seq Scan on contact_transaction_session_1521_bkp_am cts (cost=0.00..1330903.16 rows=50761316 width=24)" " -> Hash (cost=1977208.33..1977208.33 rows=3108657 width=95)" " -> Hash Join (cost=387551.16..1977208.33 rows=3108657 width=95)" " Hash Cond: (ctt.contact_transaction_id = cth.contact_transaction_id)" " -> Seq Scan on contact_transaction_track_1521_bkp_am ctt (cost=0.00..1237342.68 rows=51396468 width=28)" " -> Hash (cost=349546.12..349546.12 rows=3040403 width=83)" " -> Nested Loop (cost=0.85..349546.12 rows=3040403 width=83)" " -> Index Scan using reference_edition_reference_series_id_idx on edition_reference er (cost=0.28..157406.81 rows=255 width=21)" " Filter: ((event_end_date >= '2020-12-18'::date) AND (SubPlan 1))" " SubPlan 1" " -> Result (cost=0.41..63.66 rows=4216 width=9)" " One-Time Filter: ((er.edition_code)::text = ANY ('{EME21LEP,AET20GAT,AEL20SMA,BRA20FFS,BRA20FPT,BRA20FTP,AET20GEX,AET20GFX,AEL20FWA,AEL20OHM,GBR21IMA,MTM21TAS,AET20SAH,AET21DFC,AEC20ACS,BRA21IPR,HLN21CPM,AET21ATW,AEL21DML,AET21ABR,AET21DBR,HAN20VFA,HAN20VFE,BRA20FRM,AET20GAP,GBR20TEU,AEL21DAH,AET21DF4,AET21DME,BRA20ABF,BRA20FUC,BRA21TEC,BRA20FSV,AEL20GPC,AET20FPN,AET20DNJ,AEL21UFM,GBR20SLM,GBR21IMX,BRZ21FAG,BRZ22FEI,BRA21EXM,AET21GAM,EME21HCK,HLN21VAD}'::text[]))" " -> Index Only Scan using reference_edition_reference_edition_code_idx on edition_reference (cost=0.41..63.66 rows=4216 width=9)" " -> Index Scan using contact_transaction_history_1521_bkp_am_edition_id_idx on contact_transaction_history_1521_bkp_am cth (cost=0.56..479.70 rows=27379 width=74)" " Index Cond: (edition_id = er.edition_id)" " -> Hash (cost=1329661.36..1329661.36 rows=53223536 width=53)" " -> Seq Scan on contact_1521_bkp_am c (cost=0.00..1329661.36 rows=53223536 width=53)" " -> Hash (cost=652835.20..652835.20 rows=18263320 width=125)" " -> Seq Scan on person_history_1521_bkp_am ph (cost=0.00..652835.20 rows=18263320 width=125)" " -> Index Scan using reference_series_reference_series_id_idx on series_reference sr (cost=0.28..0.30 rows=1 width=24)" " Index Cond: (series_id = er.series_id)" " -> Index Scan using brand_reference_pkey on brand_reference br (cost=0.27..0.29 rows=1 width=19)" " Index Cond: (brand_id = sr.brand_id)" " -> Index Scan using reference_global_lov_global_lov_id_idx on global_lov_reference (cost=0.41..0.42 rows=1 width=11)" " Index Cond: (global_lov_id = ph.salutation_id)" " Filter: ((global_lov_name)::text = 'TITLE'::text)" " -> Materialize (cost=13487.74..13548.54 rows=375 width=165)" " -> Subquery Scan on sih (cost=13487.74..13546.67 rows=375 width=165)" " -> Finalize GroupAggregate (cost=13487.74..13542.92 rows=375 width=165)" " Group Key: si.mdm_contact_id" " -> Gather Merge (cost=13487.74..13534.63 rows=363 width=165)" " Workers Planned: 3" " -> Partial GroupAggregate (cost=12487.70..12491.94 rows=121 width=165)" " Group Key: si.mdm_contact_id" " -> Sort (cost=12487.70..12488.01 rows=121 width=82)" " Sort Key: si.mdm_contact_id" " -> Hash Join (cost=104.66..12483.52 rows=121 width=82)" " Hash Cond: (si.social_information_type_id = global_lov_reference_1.global_lov_id)" " -> Parallel Seq Scan on contact_social_information_1521_bkp_am si (cost=0.00..12258.68 rows=45696 width=72)" " Filter: ((status)::text = 'Active'::text)" " -> Hash (cost=104.59..104.59 rows=6 width=18)" " -> Index Scan using reference_global_lov_global_lov_id_idx on global_lov_reference global_lov_reference_1 (cost=0.41..104.59 rows=6 width=18)" " Filter: ((global_lov_name)::text = 'SOCIAL_MEDIA_INFORMATION'::text)" Thanks & Best Wishes, Ashok
--------------------------------------------------------------------------------------------------------------------- Ashokkumar Mani (OCP12c/11g/10g/9i, AWS SAA, M103) Dubai , UAE | BLR , INDIA M: +971 54 723 0075 | +91 90086 70302 | WApp : +91 81975 99922 W: https://dbatocloudsolution.blogspot.in/
| E: [hidden email] On Sat, Jan 9, 2021 at 4:41 PM Bodo Greif <[hidden email]> wrote: Hi, |
On Sat, Jan 9, 2021 at 11:30:12PM +0530, dbatoCloud Solution wrote:
> Hi Bodo, > > I have resolved this complex query by doing small changes. What I observed the > optimizer choosing nested loop join and the storage type is in AWS EBS type. So > I decided to make random_page_cost=0 also I collected stats + vacuumed as I > said before plus now the query performing very good and I made it down from 23 > minutes into 3 minutes and 15 sec. We usually recommend random_page_cost=1.1 for storage where the random page cost is similar to the sequential cost (which is 1.0). -- Bruce Momjian <[hidden email]> https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee |
Free forum by Nabble | Edit this page |