explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FNsv : 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

Settings

Optimization path:

Optimization(s) for this plan:

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

Limit (cost=9.46..23.54 rows=1 width=546) (actual time=38.631..38.631 rows=1 loops=1)

2.          

Initplan (for Limit)

3. 0.000 0.011 ↑ 1.0 1 1

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

4. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on cad_commands (cost=0.00..5.50 rows=1 width=8) (actual time=0.011..0.011 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.276 0.276 ↑ 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.276..0.276 rows=1 loops=1)

  • Filter: ((abbreviation)::text = 'A'::text)
  • Rows Removed by Filter: 3
7. 0.297 38.630 ↑ 852,127.0 1 1

Nested Loop Left Join (cost=1.86..11,998,221.11 rows=852,127 width=546) (actual time=38.630..38.630 rows=1 loops=1)

  • Join Filter: (ua.unitaction = ul.unitaction)
  • Rows Removed by Join Filter: 19
8. 0.005 28.841 ↑ 150,819.0 1 1

Nested Loop Left Join (cost=1.86..9,333,168.74 rows=150,819 width=518) (actual time=28.841..28.841 rows=1 loops=1)

9. 0.088 28.806 ↑ 150,819.0 1 1

Nested Loop Left Join (cost=1.72..9,306,494.14 rows=150,819 width=518) (actual time=28.806..28.806 rows=1 loops=1)

  • Join Filter: ((cu.description)::text = u.unitnumber)
  • Rows Removed by Join Filter: 933
10. 0.001 10.478 ↑ 150,819.0 1 1

Nested Loop Left Join (cost=1.72..6,340,569.16 rows=150,819 width=490) (actual time=10.478..10.478 rows=1 loops=1)

11. 0.005 9.977 ↑ 150,819.0 1 1

Nested Loop (cost=1.44..6,294,565.23 rows=150,819 width=294) (actual time=9.977..9.977 rows=1 loops=1)

12. 0.017 9.628 ↑ 148,979.0 1 1

Nested Loop Left Join (cost=1.01..6,218,788.29 rows=148,979 width=286) (actual time=9.628..9.628 rows=1 loops=1)

  • Join Filter: (cci.cad_commandsid = cc.cad_commandsid)
  • Rows Removed by Join Filter: 65
13. 0.018 9.008 ↑ 148,979.0 1 1

Nested Loop Left Join (cost=1.01..6,073,530.95 rows=148,979 width=91) (actual time=9.008..9.008 rows=1 loops=1)

14. 0.014 8.988 ↑ 148,979.0 1 1

Nested Loop Left Join (cost=0.87..6,025,284.67 rows=148,979 width=83) (actual time=8.988..8.988 rows=1 loops=1)

  • Join Filter: (us.statusid = ul.statusid)
  • Rows Removed by Join Filter: 24
15. 0.032 1.535 ↑ 148,979.0 1 1

Nested Loop (cost=0.87..5,938,129.47 rows=148,979 width=66) (actual time=1.535..1.535 rows=1 loops=1)

  • Join Filter: (ul.loguser = su.userid)
  • Rows Removed by Join Filter: 82
16. 0.001 0.290 ↑ 157,689.0 1 1

Nested Loop (cost=0.87..2,238,462.98 rows=157,689 width=49) (actual time=0.290..0.290 rows=1 loops=1)

  • Join Filter: (cfsu.cfsid = c.cfsid)
17. 0.003 0.012 ↑ 327,867.0 1 1

Nested Loop (cost=0.44..2,081,621.67 rows=327,867 width=45) (actual time=0.012..0.012 rows=1 loops=1)

18. 0.003 0.003 ↑ 2,952,837.0 1 1

Seq Scan on yrk_callforserviceunit cfsu (cost=0.00..60,618.37 rows=2,952,837 width=9) (actual time=0.003..0.003 rows=1 loops=1)

19. 0.006 0.006 ↑ 1.0 1 1

Index Scan using yrk_unitlog_index on yrk_unitlog ul (cost=0.44..0.67 rows=1 width=40) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: ((cfsid = cfsu.cfsid) AND (unitid = cfsu.unitid))
20. 0.277 0.277 ↑ 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.277..0.277 rows=1 loops=1)

  • Index Cond: (cfsid = ul.cfsid)
21. 0.049 1.213 ↑ 18.8 83 1

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

22. 1.164 1.164 ↑ 18.8 83 1

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

23. 0.019 7.439 ↑ 1.6 25 1

Materialize (cost=0.00..2.58 rows=39 width=21) (actual time=7.410..7.439 rows=25 loops=1)

24. 7.420 7.420 ↑ 1.6 25 1

Seq Scan on yrk_unitstatus us (cost=0.00..2.39 rows=39 width=21) (actual time=7.403..7.420 rows=25 loops=1)

25. 0.002 0.002 ↓ 0.0 0 1

Index Only Scan using cad_commands_pkey on cad_commands cc (cost=0.14..0.31 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (cad_commandsid = us.z_description_cad_commands_2)
  • Heap Fetches: 0
26. 0.086 0.603 ↑ 1.0 65 1

Materialize (cost=0.00..2.98 rows=65 width=211) (actual time=0.473..0.603 rows=65 loops=1)

27. 0.517 0.517 ↑ 1.0 65 1

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

28. 0.344 0.344 ↑ 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.344..0.344 rows=1 loops=1)

  • Index Cond: ((wtrun = 101) AND (convertedid = c.yrk_callforservice_zid))
29. 0.500 0.500 ↑ 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.500..0.500 rows=1 loops=1)

  • Index Cond: (unitid = cfsu.unitid)
30. 0.225 18.240 ↑ 1.4 934 1

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

31. 18.015 18.015 ↑ 1.4 934 1

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

  • Filter: active
  • Rows Removed by Filter: 68
32. 0.030 0.030 ↑ 1.0 1 1

Index Only Scan using agencies_pkey on agencies a (cost=0.14..0.17 rows=1 width=8) (actual time=0.030..0.030 rows=1 loops=1)

  • Index Cond: (agenciesid = cu.agenciesid)
  • Heap Fetches: 1
33. 0.015 9.492 ↑ 56.5 20 1

Materialize (cost=0.00..26.95 rows=1,130 width=36) (actual time=9.479..9.492 rows=20 loops=1)

34. 9.477 9.477 ↑ 56.5 20 1

Seq Scan on yrk_unitaction ua (cost=0.00..21.30 rows=1,130 width=36) (actual time=9.475..9.477 rows=20 loops=1)

Planning time : 115.416 ms
Execution time : 39.147 ms