explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TCgS : Aurora

Settings
# exclusive inclusive rows x rows loops node
1. 47,946.439 821,616.193 ↓ 0.0 0 1

Insert on gi_learner t (cost=165,956,981.54..166,924,052.36 rows=3,306,225 width=21,776) (actual time=821,616.193..821,616.193 rows=0 loops=1)

  • Conflict Resolution: UPDATE
  • Conflict Arbiter Indexes: pkgi_learner
  • Conflict Filter: (t.b_batchid <= '35'::numeric)
  • Tuples Inserted: 0
  • Conflicting Tuples: 1,301,816
2.          

CTE s

3. 1,018.534 767,834.659 ↑ 2.5 1,301,816 1

Subquery Scan on c2 (cost=164,907,255.11..165,956,981.54 rows=3,306,225 width=3,589) (actual time=743,866.166..767,834.659 rows=1,301,816 loops=1)

4. 19,358.895 766,816.125 ↑ 2.5 1,301,816 1

Unique (cost=164,907,255.11..165,907,388.17 rows=3,306,225 width=3,622) (actual time=743,866.156..766,816.125 rows=1,301,816 loops=1)

5. 130,909.814 747,457.230 ↓ 2.8 9,323,469 1

Sort (cost=164,907,255.11..164,915,520.67 rows=3,306,225 width=3,622) (actual time=743,866.155..747,457.230 rows=9,323,469 loops=1)

  • Sort Key: s_1.learner_id, (count(1) OVER (?)), (sum(CASE s_1.b_confirmationstatus WHEN 'CONFIRMED'::text THEN 1 ELSE 0 END) OVER (?)), (sum(CASE s_1.b_confirmationstatus WHEN 'NOT_CONFIRMED'::text THEN 1 ELSE 0 END) OVER (?)), s_1.b_confscore, s_1.b_confscoretype, s_1.b_suggmergeid, (first_value(s_1.b_hassuggmerge) OVER (?)), s_1.b_suggmergemasterscount, s_1.b_suggmergeconfscore, s_1.b_suggmergeconfscoretype, (first_value(s_1.b_pubid) OVER (?)), (first_value(s_1.b_sourceid) OVER (?)), (first_value(s_1.first_name) OVER (?)), (first_value(s_1.middle_name) OVER (?)), (first_value(s_1.last_name) OVER (?)), (first_value(s_1.prefix) OVER (?)), (first_value(s_1.suffix) OVER (?)), (first_value(s_1.birth_date) OVER (?)), (first_value(s_1.identification_number_ssn) OVER (?)), (first_value(s_1.identification_number_iti) OVER (?)), (first_value(s_1.home_telephone_number) OVER (?)), (first_value(s_1.cell_telephone_number) OVER (?)), (first_value(s_1.email_address) OVER (?)), (first_value(s_1.previous_id_nbr_ssn) OVER (?)), (first_value(s_1.previous_first_name) OVER (?)), (first_value(s_1.previous_last_name) OVER (?)), (first_value(s_1.source_first_name) OVER (?)), (first_value(s_1.source_middle_name) OVER (?)), (first_value(s_1.source_last_name) OVER (?)), (first_value(s_1.source_prefix) OVER (?)), (first_value(s_1.source_suffix) OVER (?)), (first_value(s_1.source_birth_date) OVER (?)), (first_value(s_1.source_id_number_itin) OVER (?)), (first_value(s_1.source_identification_num) OVER (?)), (first_value(s_1.source_email_address) OVER (?)), (first_value(s_1.src_prev_id_nbr_ssn) OVER (?)), (first_value(s_1.source_previous_first_nam) OVER (?)), (first_value(s_1.source_previous_last_name) OVER (?)), (first_value(s_1.source_student_guid) OVER (?)), (first_value(s_1.source_informix_stprofil_) OVER (?)), (first_value(s_1.source_meta_data) OVER (?)), (first_value(s_1.identification_number_stu) OVER (?)), (first_value(s_1.source_id_nbr_student_id) OVER (?)), (first_value(s_1.tek_partial_ssn) OVER (?)), (first_value(s_1.tek_ssn_is_usable_for_mat) OVER (?)), (first_value(s_1.source_inst_rec_attest_dt) OVER (?)), (first_value(s_1.tek_stprofil_is_usable_fo) OVER (?)), (first_value(s_1.merge_or_split_event_deta) OVER (?)), (first_value(s_1.phonetized_first_name) OVER (?)), (first_value(s_1.phonetized_middle_name) OVER (?)), (first_value(s_1.phonetized_last_name) OVER (?)), (first_value(s_1.concat_name) OVER (?)), (first_value(s_1.maistreet_number) OVER (?)), (first_value(s_1.maistreet_name) OVER (?)), (first_value(s_1.maiaddress2) OVER (?)), (first_value(s_1.maiaddress3) OVER (?)), (first_value(s_1.maiunit_number) OVER (?)), (first_value(s_1.maipost_box) OVER (?)), (first_value(s_1.maicity) OVER (?)), (first_value(s_1.maistate) OVER (?)), (first_value(s_1.maipostal_code) OVER (?)), (first_value(s_1.maipostal_code_primary) OVER (?)), (first_value(s_1.maipostal_code_secondary) OVER (?)), (first_value(s_1.maicountry) OVER (?)), (first_value(s_1.mailatitude) OVER (?)), (first_value(s_1.mailongitute) OVER (?)), (first_value(s_1.perstreet_number) OVER (?)), (first_value(s_1.perstreet_name) OVER (?)), (first_value(s_1.peraddress2) OVER (?)), (first_value(s_1.peraddress3) OVER (?)), (first_value(s_1.perunit_number) OVER (?)), (first_value(s_1.perpost_box) OVER (?)), (first_value(s_1.percity) OVER (?)), (first_value(s_1.perstate) OVER (?)), (first_value(s_1.perpostal_code) OVER (?)), (first_value(s_1.perpostal_code_primary) OVER (?)), (first_value(s_1.perpostal_code_secondary) OVER (?)), (first_value(s_1.percountry) OVER (?)), (first_value(s_1.perlatitude) OVER (?)), (first_value(s_1.perlongitute) OVER (?)), (first_value(s_1.soustreet_number) OVER (?)), (first_value(s_1.soustreet_name) OVER (?)), (first_value(s_1.souaddress2) OVER (?)), (first_value(s_1.souaddress3) OVER (?)), (first_value(s_1.souunit_number) OVER (?)), (first_value(s_1.soupost_box) OVER (?)), (first_value(s_1.soucity) OVER (?)), (first_value(s_1.soustate) OVER (?)), (first_value(s_1.soupostal_code) OVER (?)), (first_value(s_1.soupostal_code_primary) OVER (?)), (first_value(s_1.soupostal_code_secondary) OVER (?)), (first_value(s_1.soucountry) OVER (?)), (first_value(s_1.soulatitude) OVER (?)), (first_value(s_1.soulongitute) OVER (?)), (first_value(s_1.sopstreet_number) OVER (?)), (first_value(s_1.sopstreet_name) OVER (?)), (first_value(s_1.sopaddress2) OVER (?)), (first_value(s_1.sopaddress3) OVER (?)), (first_value(s_1.sopunit_number) OVER (?)), (first_value(s_1.soppost_box) OVER (?)), (first_value(s_1.sopcity) OVER (?)), (first_value(s_1.sopstate) OVER (?)), (first_value(s_1.soppostal_code) OVER (?)), (first_value(s_1.soppostal_code_primary) OVER (?)), (first_value(s_1.soppostal_code_secondary) OVER (?)), (first_value(s_1.sopcountry) OVER (?)), (first_value(s_1.soplatitude) OVER (?)), (first_value(s_1.soplongitute) OVER (?)), (first_value(s_1.shpcountry_code) OVER (?)), (first_value(s_1.shparea_code) OVER (?)), (first_value(s_1.shpprefix) OVER (?)), (first_value(s_1.shpline_number) OVER (?)), (first_value(s_1.shptelephone_number) OVER (?)), (first_value(s_1.scpcountry_code) OVER (?)), (first_value(s_1.scparea_code) OVER (?)), (first_value(s_1.scpprefix) OVER (?)), (first_value(s_1.scpline_number) OVER (?)), (first_value(s_1.scptelephone_number) OVER (?)), (first_value(s_1.f_institution) OVER (?))
  • Sort Method: external merge Disk: 4,375,096kB
6. 69,510.682 616,547.416 ↓ 2.8 9,323,469 1

WindowAgg (cost=159,082,089.23..159,396,180.61 rows=3,306,225 width=3,622) (actual time=542,395.086..616,547.416 rows=9,323,469 loops=1)

7. 38,442.624 547,036.734 ↓ 2.8 9,323,469 1

Sort (cost=159,082,089.23..159,090,354.79 rows=3,306,225 width=13,931) (actual time=542,395.047..547,036.734 rows=9,323,469 loops=1)

  • Sort Key: s_1.learner_id, (CASE WHEN (s_1.perstreet_name IS NULL) THEN 1 ELSE 0 END), s_1.source_inst_rec_attest_dt DESC, s_1.b_pubid, s_1.b_sourceid
  • Sort Method: external merge Disk: 6,623,296kB
8. 43,000.550 508,594.110 ↓ 2.8 9,323,469 1

WindowAgg (cost=138,649,155.55..139,004,574.73 rows=3,306,225 width=13,931) (actual time=461,261.808..508,594.110 rows=9,323,469 loops=1)

9. 36,411.411 465,593.560 ↓ 2.8 9,323,469 1

Sort (cost=138,649,155.55..138,657,421.11 rows=3,306,225 width=13,035) (actual time=461,261.768..465,593.560 rows=9,323,469 loops=1)

  • Sort Key: s_1.learner_id, (CASE WHEN (s_1.maistreet_name IS NULL) THEN 1 ELSE 0 END), s_1.source_inst_rec_attest_dt DESC, s_1.b_pubid, s_1.b_sourceid
  • Sort Method: external merge Disk: 6,550,320kB
10. 16,261.534 429,182.149 ↓ 2.8 9,323,469 1

WindowAgg (cost=119,705,058.55..119,837,307.55 rows=3,306,225 width=13,035) (actual time=408,565.263..429,182.149 rows=9,323,469 loops=1)

11. 35,242.599 412,920.615 ↓ 2.8 9,323,469 1

Sort (cost=119,705,058.55..119,713,324.11 rows=3,306,225 width=13,003) (actual time=408,565.240..412,920.615 rows=9,323,469 loops=1)

  • Sort Key: s_1.learner_id, (CASE WHEN (s_1.source_informix_stprofil_ IS NULL) THEN 1 ELSE 0 END), s_1.source_inst_rec_attest_dt DESC, s_1.b_pubid, s_1.b_sourceid
  • Sort Method: external merge Disk: 6,487,120kB
12. 15,916.923 377,678.016 ↓ 2.8 9,323,469 1

WindowAgg (cost=100,806,164.05..100,938,413.05 rows=3,306,225 width=13,003) (actual time=357,499.517..377,678.016 rows=9,323,469 loops=1)

13. 34,605.482 361,761.093 ↓ 2.8 9,323,469 1

Sort (cost=100,806,164.05..100,814,429.61 rows=3,306,225 width=12,971) (actual time=357,499.497..361,761.093 rows=9,323,469 loops=1)

  • Sort Key: s_1.learner_id, (CASE WHEN (s_1.source_student_guid IS NULL) THEN 1 ELSE 0 END), s_1.source_inst_rec_attest_dt DESC, s_1.b_pubid, s_1.b_sourceid
  • Sort Method: external merge Disk: 6,186,080kB
14. 13,871.629 327,155.611 ↓ 2.8 9,323,469 1

WindowAgg (cost=81,952,472.05..82,084,721.05 rows=3,306,225 width=12,971) (actual time=309,314.848..327,155.611 rows=9,323,469 loops=1)

15. 34,650.901 313,283.982 ↓ 2.8 9,323,469 1

Sort (cost=81,952,472.05..81,960,737.61 rows=3,306,225 width=12,939) (actual time=309,314.830..313,283.982 rows=9,323,469 loops=1)

  • Sort Key: s_1.learner_id, (CASE WHEN (s_1.cell_telephone_number IS NULL) THEN 1 ELSE 0 END), s_1.source_inst_rec_attest_dt DESC, s_1.b_pubid, s_1.b_sourceid
  • Sort Method: external merge Disk: 6,175,792kB
16. 23,968.379 278,633.081 ↓ 2.8 9,323,469 1

WindowAgg (cost=63,119,182.36..63,276,228.05 rows=3,306,225 width=12,939) (actual time=250,759.525..278,633.081 rows=9,323,469 loops=1)

17. 34,664.018 254,664.702 ↓ 2.8 9,323,469 1

Sort (cost=63,119,182.36..63,127,447.92 rows=3,306,225 width=12,842) (actual time=250,759.503..254,664.702 rows=9,323,469 loops=1)

  • Sort Key: s_1.learner_id, (CASE WHEN (s_1.identification_number_ssn IS NULL) THEN 1 ELSE 0 END), s_1.source_inst_rec_attest_dt DESC, s_1.b_pubid, s_1.b_sourceid
  • Sort Method: external merge Disk: 5,940,392kB
18. 32,862.777 220,000.684 ↓ 2.8 9,323,469 1

WindowAgg (cost=44,371,906.80..44,578,545.86 rows=3,306,225 width=12,842) (actual time=183,341.822..220,000.684 rows=9,323,469 loops=1)

19. 32,382.689 187,137.907 ↓ 2.8 9,323,469 1

Sort (cost=44,371,906.80..44,380,172.36 rows=3,306,225 width=12,522) (actual time=183,341.798..187,137.907 rows=9,323,469 loops=1)

  • Sort Key: s_1.learner_id, ((((((CASE WHEN (s_1.first_name IS NULL) THEN 1 ELSE 0 END + CASE WHEN (s_1.last_name IS NULL) THEN 1 ELSE 0 END) + CASE WHEN (s_1.middle_name IS NULL) THEN 1 ELSE 0 END) + CASE WHEN (s_1.previous_first_name IS NULL) THEN 1 ELSE 0 END) + CASE WHEN (s_1.previous_last_name IS NULL) THEN 1 ELSE 0 END) + CASE WHEN (s_1.suffix IS NULL) THEN 1 ELSE 0 END)), s_1.source_inst_rec_attest_dt DESC, s_1.b_pubid, s_1.b_sourceid
  • Sort Method: external merge Disk: 5,574,648kB
20. 83,904.783 154,755.218 ↓ 2.8 9,323,469 1

WindowAgg (cost=25,828,689.36..26,283,295.30 rows=3,306,225 width=12,522) (actual time=67,601.620..154,755.218 rows=9,323,469 loops=1)

21. 27,218.995 70,850.435 ↓ 2.8 9,323,469 1

Sort (cost=25,828,689.36..25,836,954.93 rows=3,306,225 width=11,320) (actual time=67,601.581..70,850.435 rows=9,323,469 loops=1)

  • Sort Key: s_1.learner_id, s_1.b_pubid, s_1.b_sourceid
  • Sort Method: external merge Disk: 4,141,992kB
22. 21,101.675 43,631.440 ↓ 2.8 9,323,469 1

WindowAgg (cost=1.12..9,446,464.37 rows=3,306,225 width=11,320) (actual time=0.161..43,631.440 rows=9,323,469 loops=1)

23. 10,216.853 22,529.765 ↓ 2.8 9,323,469 1

Nested Loop (cost=1.12..9,322,480.93 rows=3,306,225 width=11,296) (actual time=0.119..22,529.765 rows=9,323,469 loops=1)

24. 1,652.127 3,200.200 ↓ 92.5 1,301,816 1

GroupAggregate (cost=0.56..1,040,502.73 rows=14,079 width=6) (actual time=0.094..3,200.200 rows=1,301,816 loops=1)

  • Group Key: m.learner_id
  • Filter: (count(*) > 1)
  • Rows Removed by Filter: 487,943
25. 1,548.073 1,548.073 ↑ 1.0 9,811,412 1

Index Only Scan using i1mi_learner on mi_learner m (cost=0.56..990,380.21 rows=9,918,910 width=6) (actual time=0.088..1,548.073 rows=9,811,412 loops=1)

  • Heap Fetches: 6
26. 9,112.712 9,112.712 ↑ 33.6 7 1,301,816

Index Scan using i1mi_learner on mi_learner s_1 (cost=0.56..582.96 rows=235 width=11,268) (actual time=0.004..0.007 rows=7 loops=1,301,816)

  • Index Cond: (learner_id = m.learner_id)
  • Filter: ((b_batchid = '35'::numeric) AND ((b_classname)::text = 'Learner'::text))
27. 773,669.754 773,669.754 ↑ 2.5 1,301,816 1

CTE Scan on s (cost=0.00..967,070.81 rows=3,306,225 width=21,776) (actual time=743,866.192..773,669.754 rows=1,301,816 loops=1)

Planning time : 5.205 ms
Execution time : 826,958.721 ms