explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4UsM

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 3,656,462.614 ↓ 55.7 1,280 1

Unique (cost=1,533,767.85..1,533,768.81 rows=23 width=524) (actual time=3,656,461.493..3,656,462.614 rows=1,280 loops=1)

2. 6.597 3,656,461.924 ↓ 53.3 1,280 1

Sort (cost=1,533,767.85..1,533,767.91 rows=24 width=524) (actual time=3,656,461.492..3,656,461.924 rows=1,280 loops=1)

  • 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: 385kB
3. 325.222 3,656,455.327 ↓ 53.3 1,280 1

Seq Scan on oad_discrepancies disc (cost=0.00..1,533,767.30 rows=24 width=524) (actual time=3,012.452..3,656,455.327 rows=1,280 loops=1)

  • Filter: ((progserv_id = (SubPlan 12)) OR (progserv_id = (SubPlan 13)))
  • Rows Removed by Filter: 7647
4.          

SubPlan (forSeq Scan)

5. 44.635 16,381.045 ↓ 0.0 0 8,927

GroupAggregate (cost=0.00..218.70 rows=1 width=50) (actual time=1.835..1.835 rows=0 loops=8,927)

  • Group Key: dis2.version_id
6. 16,336.410 16,336.410 ↓ 0.0 0 8,927

Seq Scan on oad_discrepancies dis2 (cost=0.00..218.69 rows=1 width=36) (actual time=1.710..1.830 rows=0 loops=8,927)

  • Filter: ((version_id = disc.version_id) AND ((local_air_date)::text = (disc.first_aired_date)::text))
  • Rows Removed by Filter: 8927
7. 2,756.402 3,639,625.374 ↑ 1.0 1 7,678

GroupAggregate (cost=0.00..425.51 rows=1 width=50) (actual time=474.032..474.033 rows=1 loops=7,678)

  • Group Key: dis3.version_id
8. 12,410.472 3,636,868.972 ↓ 359.0 359 7,678

Nested Loop Anti Join (cost=0.00..425.50 rows=1 width=36) (actual time=1.326..473.674 rows=359 loops=7,678)

  • Join Filter: (dis3.version_id = dis4.version_id)
9. 15,002.812 15,002.812 ↓ 359.0 359 7,678

Seq Scan on oad_discrepancies dis3 (cost=0.00..212.74 rows=1 width=36) (actual time=0.009..1.954 rows=359 loops=7,678)

  • Filter: ((first_aired_date IS NULL) AND (version_id = disc.version_id))
  • Rows Removed by Filter: 8568
10. 3,609,455.688 3,609,455.688 ↓ 0.0 0 2,753,208

Seq Scan on oad_discrepancies dis4 (cost=0.00..212.74 rows=12 width=18) (actual time=1.311..1.311 rows=0 loops=2,753,208)

  • Filter: ((first_aired_date IS NOT NULL) AND (version_id = disc.version_id))
  • Rows Removed by Filter: 8927
11. 25.600 78.080 ↑ 1.0 1 1,280

Hash Join (cost=2.47..3.79 rows=1 width=12) (actual time=0.056..0.061 rows=1 loops=1,280)

  • Hash Cond: (ps.program_subtype_id = p.program_subtype_id)
12. 5.120 5.120 ↑ 1.0 25 1,280

Seq Scan on program_subtype ps (cost=0.00..1.25 rows=25 width=17) (actual time=0.002..0.004 rows=25 loops=1,280)

13. 6.400 47.360 ↑ 1.0 1 1,280

Hash (cost=2.45..2.45 rows=1 width=5) (actual time=0.037..0.037 rows=1 loops=1,280)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 40.960 40.960 ↑ 1.0 1 1,280

Index Scan using ""IX_DBO_PROGRAM_1"" on program p (cost=0.43..2.45 rows=1 width=5) (actual time=0.032..0.032 rows=1 loops=1,280)

  • Index Cond: (program_id = disc.program_id)
15. 8.960 8.960 ↑ 1.0 1 1,280

Index Scan using ""IX_DBO_COUNTRY_NAME_2"" on country_name cn (cost=0.14..2.16 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=1,280)

  • Index Cond: (country_id = disc.country_id)
16. 35.840 35.840 ↑ 1.0 1 1,280

Index Scan using ""IX_DBO_TITLESET_1"" on titleset t (cost=0.43..2.45 rows=1 width=24) (actual time=0.028..0.028 rows=1 loops=1,280)

  • Index Cond: (titleset_id = disc.titleset_id)
17. 0.806 0.806 ↑ 1.0 1 31

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

  • Index Cond: (titleset_id = disc.child_titleset_id)
18. 0.000 130.560 ↑ 1.0 1 1,280

Index Only Scan using ""IX_DBO_PROGSERV_2"" on progserv p_1 (cost=0.42..19.60 rows=1 width=32) (actual time=0.101..0.102 rows=1 loops=1,280)

  • Index Cond: (progserv_id = disc.progserv_id)
  • Heap Fetches: 1
19.          

SubPlan (forIndex Only Scan)

20. 5.120 49.920 ↓ 0.0 0 1,280

Nested Loop (cost=0.41..4.52 rows=1 width=5) (actual time=0.039..0.039 rows=0 loops=1,280)

  • Join Filter: (ea.editor_assignment_type_id = eat.editor_assignment_type_id)
  • Rows Removed by Join Filter: 2
21. 7.680 7.680 ↑ 1.0 1 1,280

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

  • Filter: ((editor_assignment_type_tag)::text = 'APOLLO COVERAGE EDITOR'::text)
  • Rows Removed by Filter: 16
22. 37.120 37.120 ↑ 1.0 2 1,280

Index Scan using ""IX_DBO_EDITOR_ASSIGNMENT_4"" on editor_assignment ea (cost=0.41..3.28 rows=2 width=10) (actual time=0.027..0.029 rows=2 loops=1,280)

  • Index Cond: (progserv_id = p_1.progserv_id)
23. 1.650 4.125 ↑ 1.0 1 55

Nested Loop (cost=0.70..6.82 rows=1 width=32) (actual time=0.073..0.075 rows=1 loops=55)

24. 0.770 1.430 ↑ 1.0 1 55

Nested Loop (cost=0.41..4.52 rows=1 width=5) (actual time=0.025..0.026 rows=1 loops=55)

  • Join Filter: (ea_1.editor_assignment_type_id = eat_1.editor_assignment_type_id)
25. 0.385 0.385 ↑ 1.0 1 55

Seq Scan on editor_assignment_type eat_1 (cost=0.00..1.21 rows=1 width=5) (actual time=0.005..0.007 rows=1 loops=55)

  • Filter: ((editor_assignment_type_tag)::text = 'APOLLO COVERAGE EDITOR'::text)
  • Rows Removed by Filter: 16
26. 0.275 0.275 ↑ 2.0 1 55

Index Scan using ""IX_DBO_EDITOR_ASSIGNMENT_4"" on editor_assignment ea_1 (cost=0.41..3.28 rows=2 width=10) (actual time=0.005..0.005 rows=1 loops=55)

  • Index Cond: (progserv_id = p_1.progserv_id)
27. 1.045 1.045 ↑ 1.0 1 55

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

  • Index Cond: (employee_id = ea_1.employee_id)
28. 13.755 42.875 ↑ 1.0 1 1,225

Nested Loop (cost=0.70..6.82 rows=1 width=32) (actual time=0.033..0.035 rows=1 loops=1,225)

29. 4.900 14.700 ↑ 1.0 1 1,225

Nested Loop (cost=0.41..4.52 rows=1 width=5) (actual time=0.011..0.012 rows=1 loops=1,225)

  • Join Filter: (ea_2.editor_assignment_type_id = eat_2.editor_assignment_type_id)
  • Rows Removed by Join Filter: 0
30. 4.900 4.900 ↑ 1.0 1 1,225

Seq Scan on editor_assignment_type eat_2 (cost=0.00..1.21 rows=1 width=5) (actual time=0.002..0.004 rows=1 loops=1,225)

  • Filter: ((editor_assignment_type_tag)::text = 'APOLLO EDITOR'::text)
  • Rows Removed by Filter: 16
31. 4.900 4.900 ↑ 2.0 1 1,225

Index Scan using ""IX_DBO_EDITOR_ASSIGNMENT_4"" on editor_assignment ea_2 (cost=0.41..3.28 rows=2 width=10) (actual time=0.004..0.004 rows=1 loops=1,225)

  • Index Cond: (progserv_id = p_1.progserv_id)
32. 14.420 14.420 ↑ 1.0 1 1,030

Index Scan using ""IX_DBO_EMPLOYEE_2"" on employee e_1 (cost=0.28..2.30 rows=1 width=18) (actual time=0.014..0.014 rows=1 loops=1,030)

  • Index Cond: (employee_id = ea_2.employee_id)
33. 28.160 28.160 ↑ 1.0 1 1,280

Index Scan using ""IX_DBO_LEGACY_CALL_SIGN_1"" on legacy_call_sign cs (cost=0.42..2.44 rows=1 width=7) (actual time=0.022..0.022 rows=1 loops=1,280)

  • Index Cond: (progserv_id = disc.progserv_id)
34. 32.000 32.000 ↓ 0.0 0 1,280

Index Scan using ""IX_DBO_RELEASE_PROGSERV_1"" on release_progserv rp (cost=0.43..2.45 rows=1 width=6) (actual time=0.025..0.025 rows=0 loops=1,280)

  • Index Cond: (release_id = disc.release_id)
35. 7.680 7.680 ↓ 0.0 0 1,280

Nested Loop (cost=0.85..4.89 rows=1 width=7) (actual time=0.006..0.006 rows=0 loops=1,280)

36. 0.000 1.280 ↓ 0.0 0 1,280

Index Scan using ""IX_DBO_RELEASE_PROGSERV_1"" on release_progserv rp_1 (cost=0.43..2.45 rows=1 width=6) (actual time=0.001..0.001 rows=0 loops=1,280)

  • Index Cond: (release_id = disc.release_id)
37. 2.717 2.717 ↑ 1.0 1 247

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

  • Index Cond: (progserv_id = rp_1.progserv_id)",,,,,,,,,"