explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yWDo : fgfv

Settings
# exclusive inclusive rows x rows loops node
1. 1,835.793 14,475.405 ↑ 8,008,852,121,392.0 1 1

Hash Right Join (cost=1,368,397.43..94,816,410,804.62 rows=8,008,852,121,392 width=32) (actual time=14,435.608..14,475.405 rows=1 loops=1)

  • Output: "*SELECT* 1_1".business_nr, "*SELECT* 1_1".business_nr
  • Hash Cond: (("*SELECT* 1_1".party_id)::text = ("*SELECT* 1".party_id)::text)
2. 591.706 10,363.624 ↑ 16.0 5,521,876 1

Append (cost=0.00..2,619,621.05 rows=88,359,232 width=37) (actual time=2,197.174..10,363.624 rows=5,521,876 loops=1)

3. 0.001 2,197.141 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_1 (cost=0.00..1,309,651.66 rows=44,173,432 width=37) (actual time=2,197.141..2,197.141 rows=0 loops=1)

  • Output: "*SELECT* 1_1".business_nr, "*SELECT* 1_1".party_id
4. 929.850 2,197.140 ↓ 0.0 0 1

Nested Loop (cost=0.00..867,917.33 rows=44,173,432 width=1,861) (actual time=2,197.140..2,197.140 rows=0 loops=1)

  • Output: s1.party_id, NULL::timestamp without time zone, NULL::numeric(10,0), NULL::timestamp without time zone, NULL::timestamp without time zone, NULL::timestamp without time zone, NULL::character varying(16), NULL::character varying(16), NULL::character varying(8), NULL::character varying(8), NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0), NULL::timestamp without time zone, NULL::character varying(40), NULL::numeric(10,0), NULL::numeric(10,0), NULL::character varying(20), NULL::numeric(10,0), NULL::timestamp without time zone, NULL::character varying(30), NULL::numeric(10,0), NULL::numeric(10,0), NULL::character varying(10), NULL::character varying(90), NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0), NULL::character varying(10), NULL::timestamp without time zone, NULL::numeric(10,0), NULL::character varying(15), NULL::character varying(20), NULL::numeric(10,0), NULL::character varying(10), NULL::numeric(10,0), NULL::character varying(10), NULL::character varying(10), NULL::character varying(10), NULL::numeric(10,0), NULL::numeric(10,0), NULL::smallint, NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0), s1.business_nr, NULL::character varying(31), NULL::character varying(20), NULL::character varying(20), NULL::character varying(20), NULL::numeric(10,0), NULL::character varying(3), NULL::character varying(12), NULL::numeric(10,0), NULL::character varying(4), NULL::character varying(3), NULL::character varying(1), NULL::character varying(1), NULL::character varying(4), NULL::character varying(3), NULL::character varying(1), NULL::numeric(10,0), NULL::numeric(10,0), NULL::character varying(1), NULL::numeric(10,0)
5. 1,267.290 1,267.290 ↑ 1.0 5,521,679 1

Seq Scan on cif_1.tb1010 s1 (cost=0.00..315,719.79 rows=5,521,679 width=37) (actual time=0.024..1,267.290 rows=5,521,679 loops=1)

  • Output: s1.party_id, s1.created_ts, s1.seq_no, s1.updated_ts, s1.birth_date, s1.death_date, s1.created_by, s1.updated_by, s1.created_pgm, s1.updated_pgm, s1.dflt_greet, s1.bus_area, s1.prefer_lang, s1.stat_pe, s1.stat_date, s1.prof, s1.prof_stat, s1.sex, s1.nat_id_no, s1.marital_stat, s1.marital_stat_date, s1.city_of_birth, s1.cntry_of_birth, s1.income_crncy, s1.income, s1.empr, s1.emp_stat, s1.salary_crncy, s1.system_id, s1.salary, s1.emp_start_date, s1.sales_stat, s1.how_refer, s1.educ_level, s1.hght_val, s1.hght_unit, s1.wght_val, s1.wght_unit, s1.blood_type, s1.no_of_dpnd, s1.prim_nat, s1.sec_nat, s1.dyn_attr_flag, s1.nat_id_iss_cntry, s1.shldr_ind, s1.emp_ind, s1.business_nr, s1.civ_title, s1.soc_sec_no, s1.co_no, s1.vat_no, s1.vat_no_iss_cntry, s1.bdst_seq_no, s1.ent_no, s1.vat_ind, s1.fl_code, s1.fl_probability, s1.fl_company, s1.fl_choice, s1.fl_code_after_upd, s1.fl_prob_after_upd, s1.result_upd_wdm, s1.send_party_wdm_ind, s1.dflt_greet_ext, s1.pep, s1.paperless_ind, s1.opt_out
6. 0.000 0.000 ↓ 0.0 0 5,521,679

Materialize (cost=0.00..29.66 rows=8 width=0) (actual time=0.000..0.000 rows=0 loops=5,521,679)

7. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on cif_v.cif_view_mgt mgt (cost=0.00..29.62 rows=8 width=0) (actual time=0.006..0.006 rows=0 loops=1)

  • Filter: ((mgt.active_schema_nm)::text = 'CIF_1'::text)
  • Rows Removed by Filter: 1
8. 900.142 7,574.777 ↑ 8.0 5,521,876 1

Subquery Scan on *SELECT* 2_1 (cost=0.00..1,309,969.40 rows=44,185,800 width=37) (actual time=0.031..7,574.777 rows=5,521,876 loops=1)

  • Output: "*SELECT* 2_1".business_nr, "*SELECT* 2_1".party_id
9. 5,261.259 6,674.635 ↑ 8.0 5,521,876 1

Nested Loop (cost=0.00..868,111.40 rows=44,185,800 width=1,861) (actual time=0.030..6,674.635 rows=5,521,876 loops=1)

  • Output: s2.party_id, NULL::timestamp without time zone, NULL::numeric(10,0), NULL::timestamp without time zone, NULL::timestamp without time zone, NULL::timestamp without time zone, NULL::character varying(16), NULL::character varying(16), NULL::character varying(8), NULL::character varying(8), NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0), NULL::timestamp without time zone, NULL::character varying(40), NULL::numeric(10,0), NULL::numeric(10,0), NULL::character varying(20), NULL::numeric(10,0), NULL::timestamp without time zone, NULL::character varying(30), NULL::numeric(10,0), NULL::numeric(10,0), NULL::character varying(10), NULL::character varying(90), NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0), NULL::character varying(10), NULL::timestamp without time zone, NULL::numeric(10,0), NULL::character varying(15), NULL::character varying(20), NULL::numeric(10,0), NULL::character varying(10), NULL::numeric(10,0), NULL::character varying(10), NULL::character varying(10), NULL::character varying(10), NULL::numeric(10,0), NULL::numeric(10,0), NULL::smallint, NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0), s2.business_nr, NULL::character varying(31), NULL::character varying(20), NULL::character varying(20), NULL::character varying(20), NULL::numeric(10,0), NULL::character varying(3), NULL::character varying(12), NULL::numeric(10,0), NULL::character varying(4), NULL::character varying(3), NULL::character varying(1), NULL::character varying(1), NULL::character varying(4), NULL::character varying(3), NULL::character varying(1), NULL::numeric(10,0), NULL::numeric(10,0), NULL::character varying(1), NULL::numeric(10,0)
10. 1,413.376 1,413.376 ↑ 1.0 5,521,876 1

Seq Scan on cif_2.tb1010 s2 (cost=0.00..315,759.25 rows=5,523,225 width=37) (actual time=0.007..1,413.376 rows=5,521,876 loops=1)

  • Output: s2.party_id, s2.created_ts, s2.seq_no, s2.updated_ts, s2.birth_date, s2.death_date, s2.created_by, s2.updated_by, s2.created_pgm, s2.updated_pgm, s2.dflt_greet, s2.bus_area, s2.prefer_lang, s2.stat_pe, s2.stat_date, s2.prof, s2.prof_stat, s2.sex, s2.nat_id_no, s2.marital_stat, s2.marital_stat_date, s2.city_of_birth, s2.cntry_of_birth, s2.income_crncy, s2.income, s2.empr, s2.emp_stat, s2.salary_crncy, s2.system_id, s2.salary, s2.emp_start_date, s2.sales_stat, s2.how_refer, s2.educ_level, s2.hght_val, s2.hght_unit, s2.wght_val, s2.wght_unit, s2.blood_type, s2.no_of_dpnd, s2.prim_nat, s2.sec_nat, s2.dyn_attr_flag, s2.nat_id_iss_cntry, s2.shldr_ind, s2.emp_ind, s2.business_nr, s2.civ_title, s2.soc_sec_no, s2.co_no, s2.vat_no, s2.vat_no_iss_cntry, s2.bdst_seq_no, s2.ent_no, s2.vat_ind, s2.fl_code, s2.fl_probability, s2.fl_company, s2.fl_choice, s2.fl_code_after_upd, s2.fl_prob_after_upd, s2.result_upd_wdm, s2.send_party_wdm_ind, s2.dflt_greet_ext, s2.pep, s2.paperless_ind, s2.opt_out
11. 0.000 0.000 ↑ 8.0 1 5,521,876

Materialize (cost=0.00..29.66 rows=8 width=0) (actual time=0.000..0.000 rows=1 loops=5,521,876)

12. 0.006 0.006 ↑ 8.0 1 1

Seq Scan on cif_v.cif_view_mgt mgt_1 (cost=0.00..29.62 rows=8 width=0) (actual time=0.006..0.006 rows=1 loops=1)

  • Filter: ((mgt_1.active_schema_nm)::text = 'CIF_2'::text)
13. 0.057 2,275.988 ↑ 18,127,935.0 1 1

Hash (cost=1,035,579.24..1,035,579.24 rows=18,127,935 width=21) (actual time=2,275.987..2,275.988 rows=1 loops=1)

  • Output: "*SELECT* 1".party_id
  • Buckets: 65536 Batches: 512 Memory Usage: 512kB
14. 191.326 2,275.931 ↑ 18,127,935.0 1 1

Hash Right Join (cost=145.58..1,035,579.24 rows=18,127,935 width=21) (actual time=2,275.930..2,275.931 rows=1 loops=1)

  • Output: "*SELECT* 1".party_id
  • Hash Cond: (("*SELECT* 1_2".party_id)::text = ("*SELECT* 1".party_id)::text)
15. 164.448 2,084.532 ↑ 16.0 1,666,236 1

Append (cost=0.00..754,184.08 rows=26,658,728 width=21) (actual time=548.165..2,084.532 rows=1,666,236 loops=1)

16. 0.001 548.137 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_2 (cost=0.00..377,090.46 rows=13,329,424 width=21) (actual time=548.137..548.137 rows=0 loops=1)

  • Output: "*SELECT* 1_2".party_id
17. 307.532 548.136 ↓ 0.0 0 1

Nested Loop (cost=0.00..243,796.22 rows=13,329,424 width=1,091) (actual time=548.136..548.136 rows=0 loops=1)

  • Output: s1_1.party_id, NULL::timestamp without time zone, NULL::numeric(10,0), NULL::timestamp without time zone, NULL::timestamp without time zone, NULL::timestamp without time zone, NULL::character varying(16), NULL::character varying(16), NULL::character varying(8), NULL::character varying(8), NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0), NULL::timestamp without time zone, NULL::numeric(10,0), NULL::numeric(10,0), NULL::character varying(20), NULL::character varying(10), NULL::character varying(20), NULL::character varying(10), NULL::character varying(16), NULL::character varying(5), NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0), NULL::character varying(15), NULL::numeric(10,0), NULL::character varying(15), NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0), NULL::smallint, NULL::numeric(10,0), NULL::numeric(10,0), NULL::timestamp without time zone, NULL::numeric(10,0), NULL::character varying(15), NULL::numeric(10,0), NULL::character varying(31), NULL::character varying(12), NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0)
18. 240.604 240.604 ↑ 1.0 1,666,178 1

Seq Scan on cif_1.tb1020 s1_1 (cost=0.00..77,148.78 rows=1,666,178 width=21) (actual time=0.003..240.604 rows=1,666,178 loops=1)

  • Output: s1_1.party_id, s1_1.created_ts, s1_1.seq_no, s1_1.updated_ts, s1_1.start_date, s1_1.end_date, s1_1.created_by, s1_1.updated_by, s1_1.created_pgm, s1_1.updated_pgm, s1_1.bus_area, s1_1.pref_lang, s1_1.stat_org, s1_1.stat_date, s1_1.legal_form, s1_1.type_of_org, s1_1.vat_no, s1_1.no_of_emp, s1_1.co_no, s1_1.prim_sic_cde, s1_1.duns_no, s1_1.fiscal_yr_start, s1_1.system_id, s1_1.gross_revn_crncy, s1_1.gross_revn, s1_1.fin_stat, s1_1.sales_stat, s1_1.how_refer, s1_1.no_own, s1_1.prim_nat, s1_1.sec_nat, s1_1.dyn_attr_flag, s1_1.vat_no_iss_cntry, s1_1.shldr_ind, s1_1.org_reg_date, s1_1.emp_ind, s1_1.business_nr, s1_1.dflt_greet, s1_1.civ_title, s1_1.ent_no, s1_1.vat_ind, s1_1.dflt_greet_ext, s1_1.paperless_ind, s1_1.opt_out, s1_1.ebo_exempted
19. 0.000 0.000 ↓ 0.0 0 1,666,178

Materialize (cost=0.00..29.66 rows=8 width=0) (actual time=0.000..0.000 rows=0 loops=1,666,178)

20. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on cif_v.cif_view_mgt mgt_2 (cost=0.00..29.62 rows=8 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: ((mgt_2.active_schema_nm)::text = 'CIF_1'::text)
  • Rows Removed by Filter: 1
21. 185.453 1,371.947 ↑ 8.0 1,666,236 1

Subquery Scan on *SELECT* 2_2 (cost=0.00..377,093.61 rows=13,329,304 width=21) (actual time=0.027..1,371.947 rows=1,666,236 loops=1)

  • Output: "*SELECT* 2_2".party_id
22. 944.374 1,186.494 ↑ 8.0 1,666,236 1

Nested Loop (cost=0.00..243,800.58 rows=13,329,304 width=1,091) (actual time=0.025..1,186.494 rows=1,666,236 loops=1)

  • Output: s2_1.party_id, NULL::timestamp without time zone, NULL::numeric(10,0), NULL::timestamp without time zone, NULL::timestamp without time zone, NULL::timestamp without time zone, NULL::character varying(16), NULL::character varying(16), NULL::character varying(8), NULL::character varying(8), NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0), NULL::timestamp without time zone, NULL::numeric(10,0), NULL::numeric(10,0), NULL::character varying(20), NULL::character varying(10), NULL::character varying(20), NULL::character varying(10), NULL::character varying(16), NULL::character varying(5), NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0), NULL::character varying(15), NULL::numeric(10,0), NULL::character varying(15), NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0), NULL::smallint, NULL::numeric(10,0), NULL::numeric(10,0), NULL::timestamp without time zone, NULL::numeric(10,0), NULL::character varying(15), NULL::numeric(10,0), NULL::character varying(31), NULL::character varying(12), NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0)
23. 242.120 242.120 ↓ 1.0 1,666,236 1

Seq Scan on cif_2.tb1020 s2_1 (cost=0.00..77,154.63 rows=1,666,163 width=21) (actual time=0.012..242.120 rows=1,666,236 loops=1)

  • Output: s2_1.party_id, s2_1.created_ts, s2_1.seq_no, s2_1.updated_ts, s2_1.start_date, s2_1.end_date, s2_1.created_by, s2_1.updated_by, s2_1.created_pgm, s2_1.updated_pgm, s2_1.bus_area, s2_1.pref_lang, s2_1.stat_org, s2_1.stat_date, s2_1.legal_form, s2_1.type_of_org, s2_1.vat_no, s2_1.no_of_emp, s2_1.co_no, s2_1.prim_sic_cde, s2_1.duns_no, s2_1.fiscal_yr_start, s2_1.system_id, s2_1.gross_revn_crncy, s2_1.gross_revn, s2_1.fin_stat, s2_1.sales_stat, s2_1.how_refer, s2_1.no_own, s2_1.prim_nat, s2_1.sec_nat, s2_1.dyn_attr_flag, s2_1.vat_no_iss_cntry, s2_1.shldr_ind, s2_1.org_reg_date, s2_1.emp_ind, s2_1.business_nr, s2_1.dflt_greet, s2_1.civ_title, s2_1.ent_no, s2_1.vat_ind, s2_1.dflt_greet_ext, s2_1.paperless_ind, s2_1.opt_out, s2_1.ebo_exempted
24. 0.000 0.000 ↑ 8.0 1 1,666,236

Materialize (cost=0.00..29.66 rows=8 width=0) (actual time=0.000..0.000 rows=1 loops=1,666,236)

25. 0.007 0.007 ↑ 8.0 1 1

Seq Scan on cif_v.cif_view_mgt mgt_3 (cost=0.00..29.62 rows=8 width=0) (actual time=0.007..0.007 rows=1 loops=1)

  • Filter: ((mgt_3.active_schema_nm)::text = 'CIF_2'::text)
26. 0.002 0.073 ↑ 136.0 1 1

Hash (cost=143.88..143.88 rows=136 width=21) (actual time=0.073..0.073 rows=1 loops=1)

  • Output: "*SELECT* 1".party_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.000 0.071 ↑ 136.0 1 1

Append (cost=0.56..143.88 rows=136 width=21) (actual time=0.070..0.071 rows=1 loops=1)

28. 0.001 0.044 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=0.56..67.83 rows=64 width=21) (actual time=0.044..0.044 rows=0 loops=1)

  • Output: "*SELECT* 1".party_id
29. 0.001 0.043 ↓ 0.0 0 1

Nested Loop (cost=0.56..67.19 rows=64 width=749) (actual time=0.043..0.043 rows=0 loops=1)

  • Output: NULL::character varying(20), NULL::numeric(10,0), NULL::character varying(20), NULL::numeric(10,0), s1_2.party_id, NULL::character varying(20), NULL::timestamp without time zone, NULL::timestamp without time zone, NULL::timestamp without time zone, NULL::timestamp without time zone, NULL::character varying(16), NULL::character varying(16), NULL::character varying(8), NULL::character varying(8), NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0), NULL::character varying(20), NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0), NULL::character varying(30), NULL::numeric(10,0), NULL::character varying(20)
30. 0.036 0.036 ↑ 8.0 1 1

Index Scan using xb111006_agrmntid_ptyid on cif_1.tb1110 s1_2 (cost=0.56..36.74 rows=8 width=21) (actual time=0.036..0.036 rows=1 loops=1)

  • Output: s1_2.party_role_id, s1_2.seq_no, s1_2.role_player_id, s1_2.agrmnt_role_nature, s1_2.party_id, s1_2.agrmnt_id, s1_2.created_ts, s1_2.updated_ts, s1_2.start_date, s1_2.end_date, s1_2.created_by, s1_2.updated_by, s1_2.created_pgm, s1_2.updated_pgm, s1_2.party_type, s1_2.role_stat, s1_2.end_rsn, s1_2.perc_of_rel, s1_2.contact_id, s1_2.contact_usage, s1_2.par_agrmnt_type, s1_2.bo_system_id, s1_2.sub_role_cde, s1_2.role_player_type, s1_2.party_name_id
  • Index Cond: (((s1_2.agrmnt_id)::text = 'AXCK0AYZABN30VBAXXXX'::text) AND (s1_2.agrmnt_role_nature = '1680001'::numeric))
  • Filter: (s1_2.contact_usage <> '1340003'::numeric)
31. 0.001 0.006 ↓ 0.0 0 1

Materialize (cost=0.00..29.66 rows=8 width=0) (actual time=0.006..0.006 rows=0 loops=1)

32. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on cif_v.cif_view_mgt mgt_4 (cost=0.00..29.62 rows=8 width=0) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: ((mgt_4.active_schema_nm)::text = 'CIF_1'::text)
  • Rows Removed by Filter: 1
33. 0.000 0.027 ↑ 72.0 1 1

Subquery Scan on *SELECT* 2 (cost=0.56..76.05 rows=72 width=21) (actual time=0.026..0.027 rows=1 loops=1)

  • Output: "*SELECT* 2".party_id
34. 0.003 0.027 ↑ 72.0 1 1

Nested Loop (cost=0.56..75.33 rows=72 width=749) (actual time=0.026..0.027 rows=1 loops=1)

  • Output: NULL::character varying(20), NULL::numeric(10,0), NULL::character varying(20), NULL::numeric(10,0), s2_2.party_id, NULL::character varying(20), NULL::timestamp without time zone, NULL::timestamp without time zone, NULL::timestamp without time zone, NULL::timestamp without time zone, NULL::character varying(16), NULL::character varying(16), NULL::character varying(8), NULL::character varying(8), NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0), NULL::character varying(20), NULL::numeric(10,0), NULL::numeric(10,0), NULL::numeric(10,0), NULL::character varying(30), NULL::numeric(10,0), NULL::character varying(20)
35. 0.019 0.019 ↑ 9.0 1 1

Index Scan using xb111006_agrmntid_ptyid on cif_2.tb1110 s2_2 (cost=0.56..44.79 rows=9 width=21) (actual time=0.019..0.019 rows=1 loops=1)

  • Output: s2_2.party_role_id, s2_2.seq_no, s2_2.role_player_id, s2_2.agrmnt_role_nature, s2_2.party_id, s2_2.agrmnt_id, s2_2.created_ts, s2_2.updated_ts, s2_2.start_date, s2_2.end_date, s2_2.created_by, s2_2.updated_by, s2_2.created_pgm, s2_2.updated_pgm, s2_2.party_type, s2_2.role_stat, s2_2.end_rsn, s2_2.perc_of_rel, s2_2.contact_id, s2_2.contact_usage, s2_2.par_agrmnt_type, s2_2.bo_system_id, s2_2.sub_role_cde, s2_2.role_player_type, s2_2.party_name_id
  • Index Cond: (((s2_2.agrmnt_id)::text = 'AXCK0AYZABN30VBAXXXX'::text) AND (s2_2.agrmnt_role_nature = '1680001'::numeric))
  • Filter: (s2_2.contact_usage <> '1340003'::numeric)
36. 0.004 0.005 ↑ 8.0 1 1

Materialize (cost=0.00..29.66 rows=8 width=0) (actual time=0.005..0.005 rows=1 loops=1)

37. 0.001 0.001 ↑ 8.0 1 1

Seq Scan on cif_v.cif_view_mgt mgt_5 (cost=0.00..29.62 rows=8 width=0) (actual time=0.001..0.001 rows=1 loops=1)