explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aIhO : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #MDwC; plan #lNxE; plan #NiVG; plan #uBOY; plan #j2md; plan #gqT1; plan #wfdo; plan #FNsv; plan #1vH

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 0.994 ↑ 1.0 1 1

Limit (cost=14.06..323.78 rows=1 width=527) (actual time=0.994..0.994 rows=1 loops=1)

2.          

Initplan (for Limit)

3. 0.000 0.005 ↑ 1.0 1 1

Limit (cost=0.00..5.50 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)

4. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on cad_commands (cost=0.00..5.50 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)

  • Filter: (active AND ((tag)::text = 'log'::text))
  • Rows Removed by Filter: 5
5. 0.000 0.000 ↓ 0.0 0

Seq Scan on cad_incidents_status_types (cost=0.00..1.05 rows=1 width=8) (never executed)

  • Filter: ((abbreviation)::text = 'C'::text)
6. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on cad_incidents_status_types cad_incidents_status_types_1 (cost=0.00..1.05 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1)

  • Filter: ((abbreviation)::text = 'A'::text)
  • Rows Removed by Filter: 3
7. 0.024 0.993 ↑ 3,867.0 1 1

Nested Loop Left Join (cost=6.46..1,197,675.60 rows=3,867 width=527) (actual time=0.993..0.993 rows=1 loops=1)

  • Join Filter: (a.agenciesid = cu.agenciesid)
  • Rows Removed by Join Filter: 2
8. 0.089 0.964 ↑ 3,867.0 1 1

Nested Loop Left Join (cost=6.46..1,191,315.79 rows=3,867 width=527) (actual time=0.964..0.964 rows=1 loops=1)

  • Join Filter: ((cu.description)::text = u.unitnumber)
  • Rows Removed by Join Filter: 933
9. 0.001 0.330 ↑ 3,867.0 1 1

Nested Loop Left Join (cost=6.46..1,115,201.89 rows=3,867 width=499) (actual time=0.330..0.330 rows=1 loops=1)

10. 0.002 0.312 ↑ 3,867.0 1 1

Nested Loop Left Join (cost=6.19..1,114,022.35 rows=3,867 width=303) (actual time=0.312..0.312 rows=1 loops=1)

  • Join Filter: (ua.unitaction = ul.unitaction)
  • Rows Removed by Join Filter: 3
11. 0.002 0.304 ↑ 3,867.0 1 1

Nested Loop (cost=6.19..1,112,570.91 rows=3,867 width=294) (actual time=0.304..0.304 rows=1 loops=1)

12. 0.008 0.264 ↑ 3,820.0 1 1

Nested Loop (cost=5.76..1,110,627.90 rows=3,820 width=286) (actual time=0.264..0.264 rows=1 loops=1)

  • Join Filter: (ul.loguser = su.userid)
  • Rows Removed by Join Filter: 82
13. 0.000 0.152 ↑ 4,043.0 1 1

Nested Loop (cost=5.76..1,015,496.57 rows=4,043 width=269) (actual time=0.152..0.152 rows=1 loops=1)

  • Join Filter: (cfsu.cfsid = c.cfsid)
14. 0.002 0.127 ↑ 8,407.0 1 1

Nested Loop (cost=5.33..1,011,474.92 rows=8,407 width=265) (actual time=0.127..0.127 rows=1 loops=1)

  • Join Filter: (ul.statusid = us.statusid)
  • Rows Removed by Join Filter: 5
15. 0.007 0.102 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.00..12.65 rows=1 width=224) (actual time=0.102..0.102 rows=1 loops=1)

  • Join Filter: (cci.cad_commandsid = cc.cad_commandsid)
  • Rows Removed by Join Filter: 65
16. 0.013 0.070 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.00..9.19 rows=1 width=29) (actual time=0.070..0.070 rows=1 loops=1)

  • Join Filter: (cc.cad_commandsid = us.z_description_cad_commands_2)
  • Rows Removed by Join Filter: 120
17. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on yrk_unitstatus us (cost=0.00..2.49 rows=1 width=21) (actual time=0.011..0.011 rows=1 loops=1)

  • Filter: (description = 'Dispatched'::text)
  • Rows Removed by Filter: 35
18. 0.046 0.046 ↑ 1.0 120 1

Seq Scan on cad_commands cc (cost=0.00..5.20 rows=120 width=8) (actual time=0.020..0.046 rows=120 loops=1)

19. 0.025 0.025 ↑ 1.0 65 1

Seq Scan on z_cad_command_info cci (cost=0.00..2.65 rows=65 width=211) (actual time=0.012..0.025 rows=65 loops=1)

20. 0.005 0.023 ↑ 54,644.5 6 1

Merge Join (cost=5.33..1,007,363.93 rows=327,867 width=45) (actual time=0.019..0.023 rows=6 loops=1)

  • Merge Cond: ((cfsu.cfsid = ul.cfsid) AND (cfsu.unitid = ul.unitid))
21. 0.010 0.010 ↑ 2,952,837.0 1 1

Index Scan using yrk_cfsunit on yrk_callforserviceunit cfsu (cost=0.43..83,479.98 rows=2,952,837 width=9) (actual time=0.010..0.010 rows=1 loops=1)

22. 0.008 0.008 ↑ 3,034,908.3 6 1

Index Scan using yrk_unitlog_index on yrk_unitlog ul (cost=0.44..913,694.19 rows=18,209,450 width=40) (actual time=0.007..0.008 rows=6 loops=1)

23. 0.025 0.025 ↑ 1.0 1 1

Index Scan using unique_cfsid on yrk_callforservice c (cost=0.43..0.47 rows=1 width=12) (actual time=0.025..0.025 rows=1 loops=1)

  • Index Cond: (cfsid = ul.cfsid)
24. 0.026 0.104 ↑ 18.8 83 1

Materialize (cost=0.00..286.46 rows=1,564 width=25) (actual time=0.017..0.104 rows=83 loops=1)

25. 0.078 0.078 ↑ 18.8 83 1

Seq Scan on yrk_securityuser su (cost=0.00..278.64 rows=1,564 width=25) (actual time=0.012..0.078 rows=83 loops=1)

26. 0.038 0.038 ↑ 1.0 1 1

Index Scan using stg_combined_incidents_wtrun_convertedid_idx on stg_combined_incidents ci (cost=0.43..0.50 rows=1 width=24) (actual time=0.038..0.038 rows=1 loops=1)

  • Index Cond: ((wtrun = 101) AND (convertedid = c.yrk_callforservice_zid))
27. 0.004 0.006 ↑ 6.2 4 1

Materialize (cost=0.00..1.38 rows=25 width=17) (actual time=0.005..0.006 rows=4 loops=1)

28. 0.002 0.002 ↑ 6.2 4 1

Seq Scan on yrk_unitaction ua (cost=0.00..1.25 rows=25 width=17) (actual time=0.002..0.002 rows=4 loops=1)

29. 0.017 0.017 ↑ 1.0 1 1

Index Scan using yrk_unit1 on yrk_unit u (cost=0.28..0.30 rows=1 width=200) (actual time=0.017..0.017 rows=1 loops=1)

  • Index Cond: (unitid = cfsu.unitid)
30. 0.201 0.545 ↑ 1.4 934 1

Materialize (cost=0.00..72.62 rows=1,311 width=28) (actual time=0.017..0.545 rows=934 loops=1)

31. 0.344 0.344 ↑ 1.4 934 1

Seq Scan on cad_units cu (cost=0.00..66.07 rows=1,311 width=28) (actual time=0.013..0.344 rows=934 loops=1)

  • Filter: active
  • Rows Removed by Filter: 68
32. 0.003 0.005 ↑ 33.7 3 1

Materialize (cost=0.00..8.52 rows=101 width=8) (actual time=0.004..0.005 rows=3 loops=1)

33. 0.002 0.002 ↑ 33.7 3 1

Seq Scan on agencies a (cost=0.00..8.01 rows=101 width=8) (actual time=0.002..0.002 rows=3 loops=1)

Planning time : 7.094 ms
Execution time : 1.444 ms