explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1vH : 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

Settings

Optimization path:

Optimization(s) for this plan:

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

Limit (cost=14.35..69.61 rows=1 width=527) (actual time=0.845..0.845 rows=1 loops=1)

2.          

Initplan (for Limit)

3. 0.000 0.008 ↑ 1.0 1 1

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

4. 0.008 0.008 ↑ 1.0 1 1

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

  • Filter: ((abbreviation)::text = 'A'::text)
  • Rows Removed by Filter: 3
7. 0.021 0.845 ↑ 150,819.0 1 1

Nested Loop Left Join (cost=6.75..8,334,698.87 rows=150,819 width=527) (actual time=0.845..0.845 rows=1 loops=1)

8. 0.102 0.821 ↑ 150,819.0 1 1

Nested Loop Left Join (cost=6.61..8,288,794.85 rows=150,819 width=527) (actual time=0.821..0.821 rows=1 loops=1)

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

Nested Loop Left Join (cost=6.61..5,322,869.86 rows=150,819 width=499) (actual time=0.250..0.250 rows=1 loops=1)

10. 0.004 0.238 ↑ 150,819.0 1 1

Nested Loop Left Join (cost=6.33..5,276,865.93 rows=150,819 width=303) (actual time=0.238..0.238 rows=1 loops=1)

  • Join Filter: (ua.unitaction = ul.unitaction)
  • Rows Removed by Join Filter: 19
11. 0.003 0.225 ↑ 150,819.0 1 1

Nested Loop (cost=6.33..5,220,307.49 rows=150,819 width=294) (actual time=0.225..0.225 rows=1 loops=1)

12. 0.007 0.212 ↑ 148,979.0 1 1

Nested Loop Left Join (cost=5.90..5,144,530.55 rows=148,979 width=286) (actual time=0.212..0.212 rows=1 loops=1)

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

Nested Loop Left Join (cost=5.90..4,999,273.21 rows=148,979 width=91) (actual time=0.163..0.163 rows=1 loops=1)

14. 0.003 0.159 ↑ 148,979.0 1 1

Nested Loop Left Join (cost=5.76..4,951,026.94 rows=148,979 width=83) (actual time=0.159..0.159 rows=1 loops=1)

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

Nested Loop (cost=5.76..4,863,871.73 rows=148,979 width=66) (actual time=0.139..0.139 rows=1 loops=1)

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

Nested Loop (cost=5.76..1,164,205.24 rows=157,689 width=49) (actual time=0.050..0.050 rows=1 loops=1)

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

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

  • Merge Cond: ((cfsu.cfsid = ul.cfsid) AND (cfsu.unitid = ul.unitid))
18. 0.015 0.015 ↑ 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.015..0.015 rows=1 loops=1)

19. 0.016 0.016 ↑ 18,209,450.0 1 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.016..0.016 rows=1 loops=1)

20. 0.013 0.013 ↑ 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.013..0.013 rows=1 loops=1)

  • Index Cond: (cfsid = ul.cfsid)
21. 0.019 0.079 ↑ 18.8 83 1

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

22. 0.060 0.060 ↑ 18.8 83 1

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

23. 0.007 0.017 ↑ 1.6 25 1

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

24. 0.010 0.010 ↑ 1.6 25 1

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

25. 0.001 0.001 ↓ 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.001..0.001 rows=0 loops=1)

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

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

27. 0.019 0.019 ↑ 1.0 65 1

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

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

  • Index Cond: ((wtrun = 101) AND (convertedid = c.yrk_callforservice_zid))
29. 0.007 0.009 ↑ 1.2 20 1

Materialize (cost=0.00..1.38 rows=25 width=17) (actual time=0.003..0.009 rows=20 loops=1)

30. 0.002 0.002 ↑ 1.2 20 1

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

31. 0.011 0.011 ↑ 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.011..0.011 rows=1 loops=1)

  • Index Cond: (unitid = cfsu.unitid)
32. 0.166 0.469 ↑ 1.4 934 1

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

33. 0.303 0.303 ↑ 1.4 934 1

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

  • Filter: active
  • Rows Removed by Filter: 68
34. 0.003 0.003 ↑ 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.003..0.003 rows=1 loops=1)

  • Index Cond: (agenciesid = cu.agenciesid)
  • Heap Fetches: 1
Planning time : 5.405 ms
Execution time : 1.290 ms