explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bdzW

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Insert on du_consumer t (cost=375,795.51..375,795.54 rows=1 width=1,088) (actual rows= loops=)

  • Conflict Resolution: UPDATE
  • Conflict Arbiter Indexes: pkdu_consumer
  • Conflict Filter: (t.b_matchscore < excluded.b_matchscore)
2.          

CTE s

3. 0.000 0.000 ↓ 0.0

Subquery Scan on matches (cost=375,795.49..375,795.51 rows=1 width=740) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Unique (cost=375,795.49..375,795.50 rows=1 width=704) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Sort (cost=375,795.49..375,795.49 rows=1 width=704) (actual rows= loops=)

  • Sort Key: (CASE WHEN ((lpad((s_2.b_pubid)::text, 30, '_'::text) || (s_2.b_sourceid)::text) < (lpad((s_5.b_pubid)::text, 30, '_'::text) || (s_5.b_sourceid)::text)) THEN s_2.b_pubid ELSE s_5.b_pubid END), (CASE WHEN ((lpad((s_2.b_pubid)::text, 30, '_'::text) || (s_2.b_sourceid)::text) < (lpad((s_5.b_pubid)::text, 30, '_'::text) || (s_5.b_sourceid)::text)) THEN s_2.b_sourceid ELSE s_5.b_sourceid END), (CASE WHEN ((lpad((s_2.b_pubid)::text, 30, '_'::text) || (s_2.b_sourceid)::text) < (lpad((s_5.b_pubid)::text, 30, '_'::text) || (s_5.b_sourceid)::text)) THEN s_5.b_pubid ELSE s_2.b_pubid END), (CASE WHEN ((lpad((s_2.b_pubid)::text, 30, '_'::text) || (s_2.b_sourceid)::text) < (lpad((s_5.b_pubid)::text, 30, '_'::text) || (s_5.b_sourceid)::text)) THEN s_5.b_sourceid ELSE s_2.b_sourceid END)
6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.12..375,795.48 rows=1 width=704) (actual rows= loops=)

  • Join Filter: (s_2.f_market = s_6.global_market_id)
7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.12..375,793.84 rows=1 width=72) (actual rows= loops=)

  • Join Filter: ((((s_1.standardized_addressline2)::text = (s_4.standardized_addressline2)::text) OR (s_1.address_line2 IS NULL) OR (s_4.address_line2 IS NULL)) AND (((s_1.standardized_address_lin3)::text = (s_4.standardized_address_lin3)::text) OR (s_1.address_line3 IS NULL) OR (s_4.address_line3 IS NULL)) AND (((s_1.standardized_address_lin4)::text = (s_4.standardized_address_lin4)::text) OR (s_1.address_line4 IS NULL) OR (s_4.address_line4 IS NULL)) AND (((s_1.standardized_postal_code)::text = (s_4.standardized_postal_code)::text) OR (s_1.postal_code IS NULL) OR (s_4.postal_code IS NULL)) AND ((s_1.contact_channel_type)::text = (s_4.contact_channel_type)::text) AND (CASE WHEN s_1.is_active THEN true ELSE false END = CASE WHEN s_4.is_active THEN true ELSE false END) AND ((s_1.standardized_address_line)::text = (s_4.standardized_address_line)::text) AND ((s_1.standardized_locality)::text = (s_4.standardized_locality)::text) AND (CASE WHEN s_1.usable_for_matching_rule1 THEN true ELSE false END = CASE WHEN s_4.usable_for_matching_rule1 THEN true ELSE false END))
8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.55..375,785.51 rows=1 width=192) (actual rows= loops=)

  • Join Filter: (s_2.f_market = s_3.global_market_id)
9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.55..375,783.92 rows=1 width=162) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.99..13.04 rows=1 width=283) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Index Scan using i2mi_consumer on mi_consumer s_2 (cost=0.43..4.45 rows=1 width=163) (actual rows= loops=)

  • Index Cond: (b_batchid = '3329'::numeric)
  • Filter: ('Consumer'::text = 'Consumer'::text)
12. 0.000 0.000 ↓ 0.0

Index Scan using fkmi_contact_channel_consumer on mi_contact_channel s_1 (cost=0.56..8.58 rows=1 width=136) (actual rows= loops=)

  • Index Cond: (((fp_consumer)::text = (s_2.b_pubid)::text) AND ((fs_consumer)::text = (s_2.b_sourceid)::text))
  • Filter: CASE WHEN usable_for_matching_rule1 THEN true ELSE false END
13. 0.000 0.000 ↓ 0.0

Index Scan using usr_consumer_match_rule2 on mi_consumer s_5 (cost=0.56..375,770.87 rows=1 width=163) (actual rows= loops=)

  • Index Cond: (((normalized_personal_fir)::text = (s_2.normalized_personal_fir)::text) AND ((phonetic_personal_lastnam)::text = (s_2.phonetic_personal_lastnam)::text))
  • Filter: (('Consumer'::text = 'Consumer'::text) AND (((s_2.initials)::text = (initials)::text) OR (s_2.initials IS NULL) OR (initials IS NULL)) AND ((s_2.dateofbirth = dateofbirth) OR (s_2.dateofbirth IS NULL) OR (dateofbirth IS NULL)) AND (((s_2.personal_id)::text = (personal_id)::text) OR (s_2.personal_id IS NULL) OR (personal_id IS NULL)) AND (((s_2.phonetic_personal_last_n2)::text = (phonetic_personal_last_n2)::text) OR (s_2.personal_lastname2 IS NULL) OR (personal_lastname2 IS NULL)) AND (s_2.f_market = f_market) AND (((b_pubid)::text <> (s_2.b_pubid)::text) OR ((b_sourceid)::text <> (s_2.b_sourceid)::text)))
14. 0.000 0.000 ↓ 0.0

Seq Scan on gd_market s_3 (cost=0.00..1.26 rows=26 width=30) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Index Scan using fkmi_contact_channel_consumer on mi_contact_channel s_4 (cost=0.56..8.30 rows=1 width=136) (actual rows= loops=)

  • Index Cond: (((fp_consumer)::text = (s_5.b_pubid)::text) AND ((fs_consumer)::text = (s_5.b_sourceid)::text))
  • Filter: CASE WHEN usable_for_matching_rule1 THEN true ELSE false END
16. 0.000 0.000 ↓ 0.0

Seq Scan on gd_market s_6 (cost=0.00..1.26 rows=26 width=30) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

CTE Scan on s (cost=0.00..0.03 rows=1 width=1,088) (actual rows= loops=)