explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vDA

Settings
# exclusive inclusive rows x rows loops node
1. 0.689 198.765 ↓ 98.0 686 1

Unique (cost=5,059.03..5,059.31 rows=7 width=421) (actual time=197.932..198.765 rows=686 loops=1)

  • Output: disc.program_id, disc.release_id, disc.version_id, disc.sr_database_key3, disc.version_mapping_id, ((SubPlan 1)), ((SubPlan 2)), (concat((SubPlan 3), CASE WHEN (disc.child_titleset_id IS NOT NULL) THEN (': '::text || ((SubPlan 4))::text) ELSE NULL::text END)), disc.release_start_dt, disc.first_aired_date, ((SubPlan 8)), disc.progserv_id, ((SubPlan 9)), (COALESCE((SubPlan 10), '0'::numeric)), ((SubPlan 11))
  • Buffers: shared hit=19083 read=3615 dirtied=10, local hit=2121
  • I/O Timings: read=48.439
2. 2.472 198.076 ↓ 98.0 686 1

Sort (cost=5,059.03..5,059.04 rows=7 width=421) (actual time=197.929..198.076 rows=686 loops=1)

  • Output: disc.program_id, disc.release_id, disc.version_id, disc.sr_database_key3, disc.version_mapping_id, ((SubPlan 1)), ((SubPlan 2)), (concat((SubPlan 3), CASE WHEN (disc.child_titleset_id IS NOT NULL) THEN (': '::text || ((SubPlan 4))::text) ELSE NULL::text END)), disc.release_start_dt, disc.first_aired_date, ((SubPlan 8)), disc.progserv_id, ((SubPlan 9)), (COALESCE((SubPlan 10), '0'::numeric)), ((SubPlan 11))
  • Sort Key: disc.program_id, disc.release_id, disc.version_id, disc.sr_database_key3, disc.version_mapping_id, ((SubPlan 1)), ((SubPlan 2)), (concat((SubPlan 3), CASE WHEN (disc.child_titleset_id IS NOT NULL) THEN (': '::text || ((SubPlan 4))::text) ELSE NULL::text END)), disc.release_start_dt, disc.first_aired_date, ((SubPlan 8)), disc.progserv_id, ((SubPlan 9)), (COALESCE((SubPlan 10), '0'::numeric)), ((SubPlan 11))
  • Sort Method: quicksort Memory: 206kB
  • Buffers: shared hit=19083 read=3615 dirtied=10, local hit=2121
  • I/O Timings: read=48.439
3. 21.520 195.604 ↓ 98.0 686 1

Seq Scan on pg_temp_39.oad_discrepancies disc (cost=0.00..5,058.93 rows=7 width=421) (actual time=0.821..195.604 rows=686 loops=1)

  • Output: disc.program_id, disc.release_id, disc.version_id, disc.sr_database_key3, disc.version_mapping_id, (SubPlan 1), (SubPlan 2), concat((SubPlan 3), CASE WHEN (disc.child_titleset_id IS NOT NULL) THEN (': '::text || ((SubPlan 4))::text) ELSE NULL::text END), disc.release_start_dt, disc.first_aired_date, (SubPlan 8), disc.progserv_id, (SubPlan 9), COALESCE((SubPlan 10), '0'::numeric), (SubPlan 11)
  • Filter: ((disc.progserv_id = (SubPlan 12)) OR (disc.progserv_id = (SubPlan 13)))
  • Buffers: shared hit=19083 read=3615 dirtied=10, local hit=2121
  • I/O Timings: read=48.439
4.          

SubPlan (forSeq Scan)

5. 1.372 6.174 ↑ 1.0 1 686

GroupAggregate (cost=0.28..2.31 rows=1 width=38) (actual time=0.009..0.009 rows=1 loops=686)

  • Output: min(dis2.progserv_id), dis2.version_id
  • Group Key: dis2.version_id
  • Buffers: local hit=2066
6. 4.802 4.802 ↑ 1.0 1 686

Index Scan using oad_discrepancies_version_id_first_aired_date_idx on pg_temp_39.oad_discrepancies dis2 (cost=0.28..2.29 rows=1 width=13) (actual time=0.006..0.007 rows=1 loops=686)

  • Output: dis2.program_id, dis2.version_id, dis2.version_mapping_id, dis2.sr_database_key3, dis2.country_id, dis2.progserv_id, dis2.progserv_status_id, dis2.progserv_gmt_offset, dis2.progserv_timezone, dis2.is_active, dis2.progressed_archive, dis2.local_air_date, dis2.first_aired_date, dis2.release_start_dt, dis2.release_id, dis2.release_verified, dis2.color_type_id, dis2.duration, dis2.version_label, dis2.child_titleset_id, dis2.titleset_id
  • Index Cond: (dis2.version_id = disc.version_id)
  • Filter: ((dis2.local_air_date)::text = (disc.first_aired_date)::text)
  • Rows Removed by Filter: 0
  • Buffers: local hit=2066
7. 0.040 0.216 ↑ 1.0 1 8

GroupAggregate (cost=0.55..4.62 rows=1 width=38) (actual time=0.027..0.027 rows=1 loops=8)

  • Output: min(dis3.progserv_id), dis3.version_id
  • Group Key: dis3.version_id
  • Buffers: local hit=40
8. 0.048 0.176 ↑ 1.0 1 8

Nested Loop Anti Join (cost=0.55..4.60 rows=1 width=13) (actual time=0.021..0.022 rows=1 loops=8)

  • Output: dis3.version_id, dis3.progserv_id
  • Join Filter: (dis3.version_id = dis4.version_id)
  • Buffers: local hit=40
9. 0.080 0.080 ↑ 1.0 1 8

Index Scan using oad_discrepancies_version_id_first_aired_date_idx on pg_temp_39.oad_discrepancies dis3 (cost=0.28..2.29 rows=1 width=13) (actual time=0.009..0.010 rows=1 loops=8)

  • Output: dis3.program_id, dis3.version_id, dis3.version_mapping_id, dis3.sr_database_key3, dis3.country_id, dis3.progserv_id, dis3.progserv_status_id, dis3.progserv_gmt_offset, dis3.progserv_timezone, dis3.is_active, dis3.progressed_archive, dis3.local_air_date, dis3.first_aired_date, dis3.release_start_dt, dis3.release_id, dis3.release_verified, dis3.color_type_id, dis3.duration, dis3.version_label, dis3.child_titleset_id, dis3.titleset_id
  • Index Cond: ((dis3.version_id = disc.version_id) AND (dis3.first_aired_date IS NULL))
  • Buffers: local hit=24
10. 0.048 0.048 ↓ 0.0 0 8

Index Only Scan using oad_discrepancies_version_id_first_aired_date_idx on pg_temp_39.oad_discrepancies dis4 (cost=0.28..2.29 rows=1 width=6) (actual time=0.006..0.006 rows=0 loops=8)

  • Output: dis4.version_id, dis4.first_aired_date
  • Index Cond: ((dis4.version_id = disc.version_id) AND (dis4.first_aired_date IS NOT NULL))
  • Heap Fetches: 0
  • Buffers: local hit=16
11. 11.662 47.334 ↑ 1.0 1 686

Hash Join (cost=2.47..3.79 rows=1 width=12) (actual time=0.060..0.069 rows=1 loops=686)

  • Output: ps.program_subtype_tag
  • Inner Unique: true
  • Hash Cond: (ps.program_subtype_id = p.program_subtype_id)
  • Buffers: shared hit=2235 read=1201
  • I/O Timings: read=16.308
12. 3.430 3.430 ↑ 1.0 25 686

Seq Scan on programs.program_subtype ps (cost=0.00..1.25 rows=25 width=17) (actual time=0.002..0.005 rows=25 loops=686)

  • Output: ps.program_subtype_id, ps.program_type_id, ps.program_subtype_tag
  • Buffers: shared hit=686
13. 2.058 32.242 ↑ 1.0 1 686

Hash (cost=2.45..2.45 rows=1 width=5) (actual time=0.047..0.047 rows=1 loops=686)

  • Output: p.program_subtype_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1549 read=1201
  • I/O Timings: read=16.308
14. 30.184 30.184 ↑ 1.0 1 686

Index Scan using "IX_DBO_PROGRAM_1" on programs.program p (cost=0.43..2.45 rows=1 width=5) (actual time=0.043..0.044 rows=1 loops=686)

  • Output: p.program_subtype_id
  • Index Cond: (p.program_id = disc.program_id)
  • Buffers: shared hit=1549 read=1201
  • I/O Timings: read=16.308
15. 3.430 3.430 ↑ 1.0 1 686

Index Scan using "IX_DBO_COUNTRY_NAME_2" on common.country_name cn (cost=0.14..2.16 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=686)

  • Output: cn.country_name
  • Index Cond: (cn.country_id = disc.country_id)
  • Buffers: shared hit=1370 read=2
  • I/O Timings: read=0.027
16. 30.184 30.184 ↑ 1.0 1 686

Index Scan using "IX_DBO_TITLESET_1" on programs.titleset t (cost=0.43..2.45 rows=1 width=24) (actual time=0.043..0.044 rows=1 loops=686)

  • Output: t.title_text
  • Index Cond: (t.titleset_id = disc.titleset_id)
  • Buffers: shared hit=1520 read=1231 dirtied=1
  • I/O Timings: read=16.431
17. 0.996 0.996 ↑ 1.0 1 12

Index Scan using "IX_DBO_TITLESET_1" on programs.titleset child (cost=0.43..2.45 rows=1 width=24) (actual time=0.083..0.083 rows=1 loops=12)

  • Output: child.title_text
  • Index Cond: (child.titleset_id = disc.child_titleset_id)
  • Buffers: shared hit=33 read=15 dirtied=1
  • I/O Timings: read=0.188
18. 14.924 56.938 ↑ 1.0 1 686

Index Only Scan using "IX_DBO_PROGSERV_2" on sources.progserv p_1 (cost=0.42..19.56 rows=1 width=32) (actual time=0.083..0.083 rows=1 loops=686)

  • Output: CASE WHEN ((SubPlan 5) IS NOT NULL) THEN (SubPlan 6) ELSE (SubPlan 7) END
  • Index Cond: (p_1.progserv_id = disc.progserv_id)
  • Heap Fetches: 4
  • Buffers: shared hit=10164 read=573
  • I/O Timings: read=7.516
19.          

SubPlan (forIndex Only Scan)

20. 2.744 19.894 ↓ 0.0 0 686

Nested Loop (cost=0.41..4.51 rows=1 width=5) (actual time=0.029..0.029 rows=0 loops=686)

  • Output: ea.employee_id
  • Inner Unique: true
  • Join Filter: (ea.editor_assignment_type_id = eat.editor_assignment_type_id)
  • Rows Removed by Join Filter: 2
  • Buffers: shared hit=3112 read=359
  • I/O Timings: read=4.672
21. 4.116 4.116 ↑ 1.0 1 686

Seq Scan on sources.editor_assignment_type eat (cost=0.00..1.21 rows=1 width=5) (actual time=0.004..0.006 rows=1 loops=686)

  • Output: eat.editor_assignment_type_id, eat.editor_assignment_type_desc, eat.editor_assignment_type_tag
  • Filter: ((eat.editor_assignment_type_tag)::text = 'APOLLO COVERAGE EDITOR'::text)
  • Rows Removed by Filter: 16
  • Buffers: shared hit=686
22. 13.034 13.034 ↑ 1.0 2 686

Index Scan using "IX_DBO_EDITOR_ASSIGNMENT_4" on sources.editor_assignment ea (cost=0.41..3.27 rows=2 width=10) (actual time=0.018..0.019 rows=2 loops=686)

  • Output: ea.progserv_id, ea.editor_assignment_type_id, ea.employee_id, ea.editor_assignment_id
  • Index Cond: (ea.progserv_id = p_1.progserv_id)
  • Buffers: shared hit=2426 read=359
  • I/O Timings: read=4.672
23. 0.231 0.840 ↑ 1.0 1 21

Nested Loop (cost=0.70..6.81 rows=1 width=32) (actual time=0.037..0.040 rows=1 loops=21)

  • Output: concat(e.employee_last_name, ', ', e.employee_first_name)
  • Inner Unique: true
  • Buffers: shared hit=165 read=3
  • I/O Timings: read=0.040
24. 0.084 0.399 ↑ 1.0 1 21

Nested Loop (cost=0.41..4.51 rows=1 width=5) (actual time=0.016..0.019 rows=1 loops=21)

  • Output: ea_1.employee_id
  • Inner Unique: true
  • Join Filter: (ea_1.editor_assignment_type_id = eat_1.editor_assignment_type_id)
  • Buffers: shared hit=105
25. 0.126 0.126 ↑ 1.0 1 21

Seq Scan on sources.editor_assignment_type eat_1 (cost=0.00..1.21 rows=1 width=5) (actual time=0.004..0.006 rows=1 loops=21)

  • Output: eat_1.editor_assignment_type_id, eat_1.editor_assignment_type_desc, eat_1.editor_assignment_type_tag
  • Filter: ((eat_1.editor_assignment_type_tag)::text = 'APOLLO COVERAGE EDITOR'::text)
  • Rows Removed by Filter: 16
  • Buffers: shared hit=21
26. 0.189 0.189 ↑ 2.0 1 21

Index Scan using "IX_DBO_EDITOR_ASSIGNMENT_4" on sources.editor_assignment ea_1 (cost=0.41..3.27 rows=2 width=10) (actual time=0.009..0.009 rows=1 loops=21)

  • Output: ea_1.progserv_id, ea_1.editor_assignment_type_id, ea_1.employee_id, ea_1.editor_assignment_id
  • Index Cond: (ea_1.progserv_id = p_1.progserv_id)
  • Buffers: shared hit=84
27. 0.210 0.210 ↑ 1.0 1 21

Index Scan using "IX_DBO_EMPLOYEE_2" on common.employee e (cost=0.28..2.30 rows=1 width=18) (actual time=0.010..0.010 rows=1 loops=21)

  • Output: e.employee_id, e.employee_first_name, e.employee_middle_initial, e.employee_last_name, e.username, e.glen_opid, e.apollo_username, e.ned_group_id
  • Index Cond: (e.employee_id = ea_1.employee_id)
  • Buffers: shared hit=60 read=3
  • I/O Timings: read=0.040
28. 7.161 21.280 ↑ 1.0 1 665

Nested Loop (cost=0.70..6.81 rows=1 width=32) (actual time=0.029..0.032 rows=1 loops=665)

  • Output: concat(e_1.employee_last_name, ', ', e_1.employee_first_name)
  • Inner Unique: true
  • Buffers: shared hit=5000 read=32
  • I/O Timings: read=0.432
29. 1.330 9.975 ↑ 1.0 1 665

Nested Loop (cost=0.41..4.51 rows=1 width=5) (actual time=0.013..0.015 rows=1 loops=665)

  • Output: ea_2.employee_id
  • Inner Unique: true
  • Join Filter: (ea_2.editor_assignment_type_id = eat_2.editor_assignment_type_id)
  • Rows Removed by Join Filter: 0
  • Buffers: shared hit=3256
30. 3.990 3.990 ↑ 1.0 1 665

Seq Scan on sources.editor_assignment_type eat_2 (cost=0.00..1.21 rows=1 width=5) (actual time=0.003..0.006 rows=1 loops=665)

  • Output: eat_2.editor_assignment_type_id, eat_2.editor_assignment_type_desc, eat_2.editor_assignment_type_tag
  • Filter: ((eat_2.editor_assignment_type_tag)::text = 'APOLLO EDITOR'::text)
  • Rows Removed by Filter: 16
  • Buffers: shared hit=665
31. 4.655 4.655 ↑ 2.0 1 665

Index Scan using "IX_DBO_EDITOR_ASSIGNMENT_4" on sources.editor_assignment ea_2 (cost=0.41..3.27 rows=2 width=10) (actual time=0.007..0.007 rows=1 loops=665)

  • Output: ea_2.progserv_id, ea_2.editor_assignment_type_id, ea_2.employee_id, ea_2.editor_assignment_id
  • Index Cond: (ea_2.progserv_id = p_1.progserv_id)
  • Buffers: shared hit=2591
32. 4.144 4.144 ↑ 1.0 1 592

Index Scan using "IX_DBO_EMPLOYEE_2" on common.employee e_1 (cost=0.28..2.30 rows=1 width=18) (actual time=0.007..0.007 rows=1 loops=592)

  • Output: e_1.employee_id, e_1.employee_first_name, e_1.employee_middle_initial, e_1.employee_last_name, e_1.username, e_1.glen_opid, e_1.apollo_username, e_1.ned_group_id
  • Index Cond: (e_1.employee_id = ea_2.employee_id)
  • Buffers: shared hit=1744 read=32
  • I/O Timings: read=0.432
33. 12.348 12.348 ↑ 1.0 1 686

Index Scan using "IX_DBO_LEGACY_CALL_SIGN_1" on sources.legacy_call_sign cs (cost=0.42..2.44 rows=1 width=7) (actual time=0.017..0.018 rows=1 loops=686)

  • Output: cs.call_sign_text
  • Index Cond: (cs.progserv_id = disc.progserv_id)
  • Buffers: shared hit=2411 read=336
  • I/O Timings: read=4.446
34. 13.034 13.034 ↓ 0.0 0 686

Index Scan using "IX_DBO_RELEASE_PROGSERV_1" on programs.release_progserv rp (cost=0.43..2.45 rows=1 width=6) (actual time=0.012..0.019 rows=0 loops=686)

  • Output: rp.progserv_id
  • Index Cond: (rp.release_id = disc.release_id)
  • Buffers: shared hit=332 read=225 dirtied=8
  • I/O Timings: read=3.117
35. 0.558 3.430 ↓ 0.0 0 686

Nested Loop (cost=0.85..4.89 rows=1 width=7) (actual time=0.005..0.005 rows=0 loops=686)

  • Output: cs2.call_sign_text
  • Inner Unique: true
  • Buffers: shared hit=1018 read=32
  • I/O Timings: read=0.405
36. 1.372 1.372 ↓ 0.0 0 686

Index Scan using "IX_DBO_RELEASE_PROGSERV_1" on programs.release_progserv rp_1 (cost=0.43..2.45 rows=1 width=6) (actual time=0.002..0.002 rows=0 loops=686)

  • Output: rp_1.release_id, rp_1.progserv_id
  • Index Cond: (rp_1.release_id = disc.release_id)
  • Buffers: shared hit=549
37. 1.500 1.500 ↑ 1.0 1 125

Index Scan using "IX_DBO_LEGACY_CALL_SIGN_1" on sources.legacy_call_sign cs2 (cost=0.42..2.44 rows=1 width=13) (actual time=0.012..0.012 rows=1 loops=125)

  • Output: cs2.progserv_id, cs2.call_sign_text
  • Index Cond: (cs2.progserv_id = rp_1.progserv_id)
  • Buffers: shared hit=469 read=32
  • I/O Timings: read=0.405