explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FXIP

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 8,065.287 ↑ 1.0 10 1

Limit (cost=465,803.42..465,894.52 rows=10 width=156) (actual time=5,617.821..8,065.287 rows=10 loops=1)

2.          

CTE one_call

3. 442.510 3,855.199 ↑ 1.0 509,088 1

WindowAgg (cost=385,588.72..399,655.85 rows=511,532 width=776) (actual time=2,944.428..3,855.199 rows=509,088 loops=1)

4. 2,969.395 3,412.689 ↑ 1.0 509,088 1

Sort (cost=385,588.72..386,867.55 rows=511,532 width=776) (actual time=2,944.392..3,412.689 rows=509,088 loops=1)

  • Sort Key: mor_call.call_id, mor_call.created_ts DESC, mor_call.sync_arrived DESC, mor_call.call_number DESC, mor_call.mor_call_zid DESC
  • Sort Method: external merge Disk: 204104kB
5. 443.294 443.294 ↑ 1.0 509,088 1

Index Scan using dca_mor_call_deleted_ts_idx on mor_call (cost=0.43..37,354.07 rows=511,532 width=776) (actual time=0.067..443.294 rows=509,088 loops=1)

  • Index Cond: (deleted_ts IS NULL)
6.          

CTE one_incident

7. 30.585 261.599 ↑ 1.7 28,354 1

WindowAgg (cost=64,872.14..66,048.14 rows=47,040 width=1,155) (actual time=205.193..261.599 rows=28,354 loops=1)

8. 112.989 231.014 ↑ 1.7 28,354 1

Sort (cost=64,872.14..64,989.74 rows=47,040 width=1,155) (actual time=205.180..231.014 rows=28,354 loops=1)

  • Sort Key: mor_incident.call_id, mor_incident.created_ts DESC, mor_incident.sync_arrived DESC, mor_incident.incident_id
  • Sort Method: external merge Disk: 11456kB
9. 118.025 118.025 ↑ 1.7 28,354 1

Seq Scan on mor_incident (cost=0.00..34,025.46 rows=47,040 width=1,155) (actual time=0.129..118.025 rows=28,354 loops=1)

  • Filter: ((deleted_ts IS NULL) AND (incident_type = 'CASE'::text))
  • Rows Removed by Filter: 299523
10.          

Initplan (for Limit)

11. 0.000 0.000 ↓ 0.0 0

Seq Scan on state_reporting_element_codes (cost=0.00..48.87 rows=1 width=8) (never executed)

  • Filter: (((element_tag)::text = 'domestic_violence_indicators'::text) AND ((state_code)::text = 'Y'::text))
12. 0.209 0.209 ↑ 1.0 1 1

Seq Scan on state_reporting_element_codes state_reporting_element_codes_1 (cost=0.00..48.87 rows=1 width=8) (actual time=0.016..0.209 rows=1 loops=1)

  • Filter: (((element_tag)::text = 'domestic_violence_indicators'::text) AND ((state_code)::text = 'N'::text))
  • Rows Removed by Filter: 1457
13. 0.563 8,065.275 ↑ 510.6 10 1

Nested Loop Left Join (cost=1.68..46,517.72 rows=5,106 width=156) (actual time=5,617.820..8,065.275 rows=10 loops=1)

  • Join Filter: (vd.resident_status = (res.state_code)::text)
  • Rows Removed by Join Filter: 18
14. 0.056 8,061.902 ↑ 510.6 10 1

Nested Loop Left Join (cost=1.68..46,153.36 rows=5,106 width=180) (actual time=5,617.381..8,061.902 rows=10 loops=1)

  • Join Filter: (vd.leoka_assignment = (off_assgn.state_code)::text)
  • Rows Removed by Join Filter: 140
15. 0.063 8,058.906 ↑ 510.6 10 1

Nested Loop Left Join (cost=1.68..45,035.84 rows=5,106 width=172) (actual time=5,614.447..8,058.906 rows=10 loops=1)

  • Join Filter: (vd.leoka_activity = (off_act.state_code)::text)
  • Rows Removed by Join Filter: 230
16. 0.058 8,056.073 ↑ 510.6 10 1

Nested Loop Left Join (cost=1.68..43,228.99 rows=5,106 width=196) (actual time=5,611.680..8,056.073 rows=10 loops=1)

  • Join Filter: (vd.aggravated_circumstance_2 = (sr_aac2.state_code)::text)
  • Rows Removed by Join Filter: 170
17. 0.071 8,053.625 ↑ 510.6 10 1

Nested Loop Left Join (cost=1.68..41,881.69 rows=5,106 width=220) (actual time=5,609.292..8,053.625 rows=10 loops=1)

  • Join Filter: (vd.aggravated_circumstance_1 = (sr_aac1.state_code)::text)
  • Rows Removed by Join Filter: 160
18. 0.092 8,051.084 ↑ 510.6 10 1

Nested Loop (cost=1.68..40,534.39 rows=5,106 width=244) (actual time=5,607.402..8,051.084 rows=10 loops=1)

19. 1,343.632 8,050.762 ↑ 636.5 10 1

Nested Loop (cost=1.26..37,596.91 rows=6,365 width=252) (actual time=5,607.313..8,050.762 rows=10 loops=1)

  • Join Filter: ((COALESCE(i.case_number, c.call_number) IS NOT NULL) AND (i.call_id = c.call_id))
  • Rows Removed by Join Filter: 4642803
20. 24.799 1,063.700 ↓ 5.0 10 1

Nested Loop (cost=1.26..3,467.65 rows=2 width=308) (actual time=890.417..1,063.700 rows=10 loops=1)

  • Join Filter: (co.incident_id = i.incident_id)
  • Rows Removed by Join Filter: 258410
21. 12.966 567.801 ↓ 5.0 10 1

Nested Loop (cost=1.26..2,401.02 rows=2 width=290) (actual time=567.342..567.801 rows=10 loops=1)

  • Join Filter: (co.incident_crime_type_id = o.incident_crime_type_id)
  • Rows Removed by Join Filter: 114091
22. 0.018 0.018 ↑ 14.0 3 1

Seq Scan on mor_incident_crime_type o (cost=0.00..343.09 rows=42 width=32) (actual time=0.007..0.018 rows=3 loops=1)

  • Filter: (deleted_ts IS NULL)
23. 37.389 554.817 ↓ 1,311.5 38,034 3

Materialize (cost=1.26..2,039.74 rows=29 width=313) (actual time=0.023..184.939 rows=38,034 loops=3)

24. 43.453 517.428 ↓ 1,936.8 56,168 1

Nested Loop (cost=1.26..2,039.59 rows=29 width=313) (actual time=0.048..517.428 rows=56,168 loops=1)

  • Join Filter: (vd.incident_id = co.incident_id)
25. 28.267 299.140 ↓ 1,398.7 34,967 1

Nested Loop (cost=0.84..2,019.04 rows=25 width=267) (actual time=0.032..299.140 rows=34,967 loops=1)

26. 29.538 200.019 ↓ 478.7 35,427 1

Nested Loop (cost=0.42..1,981.51 rows=74 width=247) (actual time=0.024..200.019 rows=35,427 loops=1)

27. 7.374 7.374 ↓ 141.2 23,301 1

Seq Scan on mor_ibrs_wv_vic_data vd (cost=0.00..1,262.67 rows=165 width=216) (actual time=0.005..7.374 rows=23,301 loops=1)

  • Filter: (deleted_ts IS NULL)
  • Rows Removed by Filter: 9666
28. 163.107 163.107 ↓ 2.0 2 23,301

Index Scan using dca_mor_inc_contact_incident_id_idx on mor_inc_contact cn (cost=0.42..4.35 rows=1 width=31) (actual time=0.006..0.007 rows=2 loops=23,301)

  • Index Cond: (incident_id = vd.incident_id)
  • Filter: ((deleted_ts IS NULL) AND (entity_role = 'VICTIM'::text))
  • Rows Removed by Filter: 4
29. 70.854 70.854 ↑ 1.0 1 35,427

Index Scan using ndx_pt_casesnames on pt_casesnames pt_cn (cost=0.42..0.50 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=35,427)

  • Index Cond: ((convertedid = cn.mor_inc_contact_zid) AND (wtrun = 2000))
30. 174.835 174.835 ↑ 1.0 2 34,967

Index Scan using dca_mor_inc_ict_incident_id_idx on mor_inc_ict co (cost=0.42..0.80 rows=2 width=46) (actual time=0.005..0.005 rows=2 loops=34,967)

  • Index Cond: (incident_id = cn.incident_id)
  • Filter: (deleted_ts IS NULL)
  • Rows Removed by Filter: 1
31. 471.100 471.100 ↓ 110.0 25,842 10

CTE Scan on one_incident i (cost=0.00..1,058.40 rows=235 width=128) (actual time=20.526..47.110 rows=25,842 loops=10)

  • Filter: (rank_priority = 1)
  • Rows Removed by Filter: 39
32. 5,643.430 5,643.430 ↑ 1.1 464,281 10

CTE Scan on one_call c (cost=0.00..10,230.64 rows=511,532 width=72) (actual time=294.450..564.343 rows=464,281 loops=10)

33. 0.230 0.230 ↑ 1.0 1 10

Index Only Scan using stg_combined_cases_wtrun_convertedid_idx on stg_combined_cases stg_c (cost=0.42..0.45 rows=1 width=8) (actual time=0.022..0.023 rows=1 loops=10)

  • Index Cond: ((wtrun = 2000) AND (convertedid = c.mor_call_zid))
  • Heap Fetches: 0
34. 0.026 2.470 ↑ 1.0 17 10

Materialize (cost=0.00..45.31 rows=17 width=14) (actual time=0.183..0.247 rows=17 loops=10)

35. 2.444 2.444 ↑ 1.0 17 1

Seq Scan on state_reporting_element_codes sr_aac1 (cost=0.00..45.23 rows=17 width=14) (actual time=1.824..2.444 rows=17 loops=1)

  • Filter: ((element_tag)::text ~ 'assault_codes'::text)
  • Rows Removed by Filter: 1441
36. 0.021 2.390 ↑ 1.0 17 10

Materialize (cost=0.00..45.31 rows=17 width=14) (actual time=0.175..0.239 rows=17 loops=10)

37. 2.369 2.369 ↑ 1.0 17 1

Seq Scan on state_reporting_element_codes sr_aac2 (cost=0.00..45.23 rows=17 width=14) (actual time=1.749..2.369 rows=17 loops=1)

  • Filter: ((element_tag)::text ~ 'assault_codes'::text)
  • Rows Removed by Filter: 1441
38. 0.028 2.770 ↑ 1.0 23 10

Materialize (cost=0.00..45.34 rows=23 width=14) (actual time=0.144..0.277 rows=23 loops=10)

39. 2.742 2.742 ↑ 1.0 23 1

Seq Scan on state_reporting_element_codes off_act (cost=0.00..45.23 rows=23 width=14) (actual time=1.426..2.742 rows=23 loops=1)

  • Filter: ((element_tag)::text ~ 'officer_activities'::text)
  • Rows Removed by Filter: 1435
40. 0.025 2.940 ↑ 1.0 14 10

Materialize (cost=0.00..45.30 rows=14 width=14) (actual time=0.147..0.294 rows=14 loops=10)

41. 2.915 2.915 ↑ 1.0 14 1

Seq Scan on state_reporting_element_codes off_assgn (cost=0.00..45.23 rows=14 width=14) (actual time=1.460..2.915 rows=14 loops=1)

  • Filter: ((element_tag)::text ~ 'officer_assignments'::text)
  • Rows Removed by Filter: 1444
42. 0.004 2.810 ↑ 1.0 3 10

Materialize (cost=0.00..45.24 rows=3 width=14) (actual time=0.014..0.281 rows=3 loops=10)

43. 2.806 2.806 ↑ 1.0 3 1

Seq Scan on state_reporting_element_codes res (cost=0.00..45.23 rows=3 width=14) (actual time=0.129..2.806 rows=3 loops=1)

  • Filter: ((element_tag)::text ~ 'residence_statuses'::text)
  • Rows Removed by Filter: 1455
Planning time : 8.602 ms
Execution time : 8,155.564 ms