I need to complex Query - need big support

Hi,

from my experience first tuning hint is -> indexes -> you did.

Second is look carefully not to use literal "in … "

Its worth a try to store

stuff like

> er.edition_code in ('EME21LEP','AET20GAT','AEL20SMA','BRA20FFS','BRA20FPT','BRA20FTP','AET20GEX','AET20GFX','AEL20FWA',
>  'AEL20OHM','GBR21IMA','MTM21TAS','AET20SAH','AET21DFC','AEC20ACS','BRA21IPR','HLN21CPM','AET21ATW','AEL21DML','AET21ABR')

something like

> er.edition_code in (select … from table)

in a table, yes, also try to index it

Please post to the list if you gain improvements,

best,

Anton

> Am 09.01.2021 um 10:15 schrieb dbatoCloud Solution <[hidden email]>:
>
> 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"