explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vyZR

Settings
# exclusive inclusive rows x rows loops node
1. 0.672 398.573 ↓ 343.0 686 1

Unique (cost=10,402.62..10,402.70 rows=2 width=524) (actual time=397.691..398.573 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=19886 read=2804, local hit=10545
  • I/O Timings: read=39.265
2. 2.561 397.901 ↓ 343.0 686 1

Sort (cost=10,402.62..10,402.62 rows=2 width=524) (actual time=397.689..397.901 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=19886 read=2804, local hit=10545
  • I/O Timings: read=39.265
3. 21.532 395.340 ↓ 343.0 686 1

Seq Scan on pg_temp_39.oad_discrepancies disc (cost=0.00..10,402.61 rows=2 width=524) (actual time=0.775..395.340 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=19886 read=2804, local hit=10545
  • I/O Timings: read=39.265
4.          

SubPlan (forSeq Scan)

5. 5.488 216.090 ↑ 1.0 1 686

GroupAggregate (cost=0.00..17.94 rows=1 width=50) (actual time=0.315..0.315 rows=1 loops=686)

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

Seq Scan on pg_temp_39.oad_discrepancies dis2 (cost=0.00..17.93 rows=1 width=36) (actual time=0.157..0.307 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
  • Filter: ((dis2.version_id = disc.version_id) AND ((dis2.local_air_date)::text = (disc.first_aired_date)::text))
  • Rows Removed by Filter: 685
  • Buffers: local hit=10290
7. 0.072 4.368 ↑ 1.0 1 8

GroupAggregate (cost=0.00..34.90 rows=1 width=50) (actual time=0.546..0.546 rows=1 loops=8)

  • Output: min(dis3.progserv_id), dis3.version_id
  • Group Key: dis3.version_id
  • Buffers: local hit=240
8. 0.088 4.296 ↑ 1.0 1 8

Nested Loop Anti Join (cost=0.00..34.89 rows=1 width=36) (actual time=0.425..0.537 rows=1 loops=8)

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

Seq Scan on pg_temp_39.oad_discrepancies dis3 (cost=0.00..17.44 rows=1 width=36) (actual time=0.096..0.208 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
  • Filter: ((dis3.first_aired_date IS NULL) AND (dis3.version_id = disc.version_id))
  • Rows Removed by Filter: 685
  • Buffers: local hit=120
10. 2.544 2.544 ↓ 0.0 0 8

Seq Scan on pg_temp_39.oad_discrepancies dis4 (cost=0.00..17.44 rows=1 width=18) (actual time=0.318..0.318 rows=0 loops=8)

  • Output: dis4.program_id, dis4.version_id, dis4.version_mapping_id, dis4.sr_database_key3, dis4.country_id, dis4.progserv_id, dis4.progserv_status_id, dis4.progserv_gmt_offset, dis4.progserv_timezone, dis4.is_active, dis4.progressed_archive, dis4.local_air_date, dis4.first_aired_date, dis4.release_start_dt, dis4.release_id, dis4.release_verified, dis4.color_type_id, dis4.duration, dis4.version_label, dis4.child_titleset_id, dis4.titleset_id
  • Filter: ((dis4.first_aired_date IS NOT NULL) AND (dis4.version_id = disc.version_id))
  • Rows Removed by Filter: 686
  • Buffers: local hit=120
11. 12.348 48.020 ↑ 1.0 1 686

Hash Join (cost=2.47..3.79 rows=1 width=12) (actual time=0.060..0.070 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=2388 read=1048
  • I/O Timings: read=14.811
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=1702 read=1048
  • I/O Timings: read=14.811
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=1702 read=1048
  • I/O Timings: read=14.811
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=1372
16. 28.126 28.126 ↑ 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.040..0.041 rows=1 loops=686)

  • Output: t.title_text
  • Index Cond: (t.titleset_id = disc.titleset_id)
  • Buffers: shared hit=1681 read=1070
  • I/O Timings: read=14.851
17. 0.372 0.372 ↑ 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.031..0.031 rows=1 loops=12)

  • Output: child.title_text
  • Index Cond: (child.titleset_id = disc.child_titleset_id)
  • Buffers: shared hit=35 read=13
  • I/O Timings: read=0.168
18. 15.505 53.508 ↑ 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.077..0.078 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=10489 read=248
  • I/O Timings: read=3.452
19.          

SubPlan (forIndex Only Scan)

20. 2.058 16.464 ↓ 0.0 0 686

Nested Loop (cost=0.41..4.51 rows=1 width=5) (actual time=0.024..0.024 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=3338 read=133
  • I/O Timings: read=1.837
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. 10.290 10.290 ↑ 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.013..0.015 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=2652 read=133
  • I/O Timings: read=1.837
23. 0.315 0.924 ↑ 1.0 1 21

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

  • Output: concat(e.employee_last_name, ', ', e.employee_first_name)
  • Inner Unique: true
  • Buffers: shared hit=168
24. 0.126 0.420 ↑ 1.0 1 21

Nested Loop (cost=0.41..4.51 rows=1 width=5) (actual time=0.017..0.020 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.147 0.147 ↑ 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.005..0.007 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.147 0.147 ↑ 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.007..0.007 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.189 0.189 ↑ 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.009..0.009 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=63
28. 6.496 20.615 ↑ 1.0 1 665

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

  • Output: concat(e_1.employee_last_name, ', ', e_1.employee_first_name)
  • Inner Unique: true
  • Buffers: shared hit=5030 read=2
  • I/O Timings: read=0.030
29. 1.995 9.975 ↑ 1.0 1 665

Nested Loop (cost=0.41..4.51 rows=1 width=5) (actual time=0.012..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. 3.990 3.990 ↑ 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.006..0.006 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=1774 read=2
  • I/O Timings: read=0.030
33. 10.976 10.976 ↑ 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.016..0.016 rows=1 loops=686)

  • Output: cs.call_sign_text
  • Index Cond: (cs.progserv_id = disc.progserv_id)
  • Buffers: shared hit=2511 read=236
  • I/O Timings: read=3.233
34. 5.488 5.488 ↓ 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.007..0.008 rows=0 loops=686)

  • Output: rp.progserv_id
  • Index Cond: (rp.release_id = disc.release_id)
  • Buffers: shared hit=387 read=162
  • I/O Timings: read=2.393
35. 0.683 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=1023 read=27
  • I/O Timings: read=0.357
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.375 1.375 ↑ 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.011..0.011 rows=1 loops=125)

  • Output: cs2.progserv_id, cs2.call_sign_text
  • Index Cond: (cs2.progserv_id = rp_1.progserv_id)
  • Buffers: shared hit=474 read=27
  • I/O Timings: read=0.357
Planning time : 5.786 ms
Execution time : 399.167 ms