explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ma99 : PG

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 8,526.470 ↓ 0.0 0 1

Insert on gi_learner t (cost=1,032,312.69..1,032,312.98 rows=1 width=21,776) (actual time=8,526.469..8,526.470 rows=0 loops=1)

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

CTE s

3. 0.001 8,526.465 ↓ 0.0 0 1

Subquery Scan on c2 (cost=1,032,312.37..1,032,312.69 rows=1 width=3,589) (actual time=8,526.465..8,526.465 rows=0 loops=1)

4. 0.001 8,526.464 ↓ 0.0 0 1

Unique (cost=1,032,312.37..1,032,312.67 rows=1 width=3,622) (actual time=8,526.464..8,526.464 rows=0 loops=1)

5. 0.148 8,526.463 ↓ 0.0 0 1

Sort (cost=1,032,312.37..1,032,312.37 rows=1 width=3,622) (actual time=8,526.463..8,526.463 rows=0 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: quicksort Memory: 25kB
6. 0.002 8,526.315 ↓ 0.0 0 1

WindowAgg (cost=1,032,312.26..1,032,312.36 rows=1 width=3,622) (actual time=8,526.315..8,526.315 rows=0 loops=1)

7. 0.015 8,526.313 ↓ 0.0 0 1

Sort (cost=1,032,312.26..1,032,312.26 rows=1 width=13,932) (actual time=8,526.313..8,526.313 rows=0 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: quicksort Memory: 25kB
8. 0.002 8,526.298 ↓ 0.0 0 1

WindowAgg (cost=1,032,312.14..1,032,312.25 rows=1 width=13,932) (actual time=8,526.297..8,526.298 rows=0 loops=1)

9. 0.010 8,526.296 ↓ 0.0 0 1

Sort (cost=1,032,312.14..1,032,312.14 rows=1 width=13,036) (actual time=8,526.296..8,526.296 rows=0 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: quicksort Memory: 25kB
10. 0.001 8,526.286 ↓ 0.0 0 1

WindowAgg (cost=1,032,312.09..1,032,312.13 rows=1 width=13,036) (actual time=8,526.286..8,526.286 rows=0 loops=1)

11. 0.010 8,526.285 ↓ 0.0 0 1

Sort (cost=1,032,312.09..1,032,312.09 rows=1 width=13,004) (actual time=8,526.285..8,526.285 rows=0 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: quicksort Memory: 25kB
12. 0.002 8,526.275 ↓ 0.0 0 1

WindowAgg (cost=1,032,312.03..1,032,312.08 rows=1 width=13,004) (actual time=8,526.275..8,526.275 rows=0 loops=1)

13. 0.011 8,526.273 ↓ 0.0 0 1

Sort (cost=1,032,312.03..1,032,312.04 rows=1 width=12,972) (actual time=8,526.273..8,526.273 rows=0 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: quicksort Memory: 25kB
14. 0.001 8,526.262 ↓ 0.0 0 1

WindowAgg (cost=1,032,311.98..1,032,312.02 rows=1 width=12,972) (actual time=8,526.262..8,526.262 rows=0 loops=1)

15. 0.011 8,526.261 ↓ 0.0 0 1

Sort (cost=1,032,311.98..1,032,311.99 rows=1 width=12,940) (actual time=8,526.261..8,526.261 rows=0 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: quicksort Memory: 25kB
16. 0.001 8,526.250 ↓ 0.0 0 1

WindowAgg (cost=1,032,311.92..1,032,311.97 rows=1 width=12,940) (actual time=8,526.250..8,526.250 rows=0 loops=1)

17. 0.011 8,526.249 ↓ 0.0 0 1

Sort (cost=1,032,311.92..1,032,311.93 rows=1 width=12,843) (actual time=8,526.249..8,526.249 rows=0 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: quicksort Memory: 25kB
18. 0.001 8,526.238 ↓ 0.0 0 1

WindowAgg (cost=1,032,311.85..1,032,311.91 rows=1 width=12,843) (actual time=8,526.238..8,526.238 rows=0 loops=1)

19. 0.009 8,526.237 ↓ 0.0 0 1

Sort (cost=1,032,311.85..1,032,311.85 rows=1 width=12,523) (actual time=8,526.237..8,526.237 rows=0 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: quicksort Memory: 25kB
20. 0.003 8,526.228 ↓ 0.0 0 1

WindowAgg (cost=1,032,311.70..1,032,311.84 rows=1 width=12,523) (actual time=8,526.227..8,526.228 rows=0 loops=1)

21. 0.010 8,526.225 ↓ 0.0 0 1

Sort (cost=1,032,311.70..1,032,311.70 rows=1 width=11,321) (actual time=8,526.225..8,526.225 rows=0 loops=1)

  • Sort Key: s_1.learner_id, s_1.b_pubid, s_1.b_sourceid
  • Sort Method: quicksort Memory: 25kB
22. 0.002 8,526.215 ↓ 0.0 0 1

WindowAgg (cost=1.12..1,032,311.69 rows=1 width=11,321) (actual time=8,526.214..8,526.215 rows=0 loops=1)

23. 825.321 8,526.213 ↓ 0.0 0 1

Nested Loop (cost=1.12..1,032,311.65 rows=1 width=11,297) (actual time=8,526.213..8,526.213 rows=0 loops=1)

  • Join Filter: (s_1.learner_id = m.learner_id)
24. 3,671.551 7,700.892 ↓ 99.1 1,301,816 1

GroupAggregate (cost=0.56..1,031,978.70 rows=13,134 width=6) (actual time=5.581..7,700.892 rows=1,301,816 loops=1)

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

Index Only Scan using i1mi_learner on mi_learner m (cost=0.56..980,789.76 rows=10,139,283 width=6) (actual time=2.303..4,029.341 rows=9,811,412 loops=1)

  • Heap Fetches: 21
26. 0.000 0.000 ↓ 0.0 0 1,301,816

Materialize (cost=0.56..4.58 rows=1 width=11,269) (actual time=0.000..0.000 rows=0 loops=1,301,816)

27. 3.260 3.260 ↓ 0.0 0 1

Index Scan using i2mi_learner on mi_learner s_1 (cost=0.56..4.58 rows=1 width=11,269) (actual time=3.260..3.260 rows=0 loops=1)

  • Index Cond: (b_batchid = '35'::numeric)
  • Filter: ((b_classname)::text = 'Learner'::text)
28. 8,526.468 8,526.468 ↓ 0.0 0 1

CTE Scan on s (cost=0.00..0.29 rows=1 width=21,776) (actual time=8,526.468..8,526.468 rows=0 loops=1)

Planning time : 61.479 ms
Execution time : 8,527.851 ms