explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gqT1 : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #MDwC; plan #lNxE; plan #NiVG; plan #uBOY; plan #j2md

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.002 3,667.837 ↑ 1.0 1 1

Limit (cost=19,779.67..21,375.18 rows=1 width=36) (actual time=3,667.836..3,667.837 rows=1 loops=1)

2.          

CTE get_first_unit

3. 0.051 2,288.923 ↑ 8,294.8 12 1

WindowAgg (cost=17,066.51..19,554.96 rows=99,538 width=34) (actual time=2,288.876..2,288.923 rows=12 loops=1)

4. 426.513 2,288.872 ↑ 8,294.8 12 1

Sort (cost=17,066.51..17,315.35 rows=99,538 width=34) (actual time=2,288.853..2,288.872 rows=12 loops=1)

  • Sort Key: v.incno, v.createwhen, v.lastupdatedwhen, v.ccmas_tblvehinfo_zid
  • Sort Method: external merge Disk: 3,840kB
5. 1,862.359 1,862.359 ↑ 1.0 99,538 1

Seq Scan on ccmas_tblvehinfo v (cost=0.00..7,247.38 rows=99,538 width=34) (actual time=1.301..1,862.359 rows=99,538 loops=1)

6. 20.018 3,667.835 ↑ 167.0 1 1

Nested Loop (cost=224.72..266,674.94 rows=167 width=36) (actual time=3,667.835..3,667.835 rows=1 loops=1)

  • Join Filter: (vi.ccmas_tblvehinfo_zid = gfu.ccmas_tblvehinfo_zid)
  • Rows Removed by Join Filter: 210,873
7. 2,288.937 2,288.937 ↑ 8,294.8 12 1

CTE Scan on get_first_unit gfu (cost=0.00..1,990.76 rows=99,538 width=16) (actual time=2,288.880..2,288.937 rows=12 loops=1)

8. 24.979 1,358.880 ↓ 105.2 17,573 12

Materialize (cost=224.72..15,339.82 rows=167 width=28) (actual time=9.438..113.240 rows=17,573 loops=12)

9. 0.291 1,333.901 ↓ 105.7 17,654 1

Nested Loop (cost=224.72..15,338.99 rows=167 width=28) (actual time=113.213..1,333.901 rows=17,654 loops=1)

10. 14.803 1,245.155 ↓ 5.4 17,691 1

Nested Loop (cost=224.29..11,498.70 rows=3,306 width=28) (actual time=112.747..1,245.155 rows=17,691 loops=1)

11. 11.648 168.892 ↓ 5.4 17,691 1

Hash Join (cost=223.87..7,877.56 rows=3,306 width=30) (actual time=105.880..168.892 rows=17,691 loops=1)

  • Hash Cond: (vi.z_unitid_cad_units_100 = cu.cad_unitsid)
12. 51.579 51.579 ↑ 1.0 99,538 1

Seq Scan on ccmas_tblvehinfo vi (cost=0.00..7,247.38 rows=99,538 width=22) (actual time=0.022..51.579 rows=99,538 loops=1)

13. 0.058 105.665 ↑ 1.3 201 1

Hash (cost=220.52..220.52 rows=268 width=16) (actual time=105.665..105.665 rows=201 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
14. 0.630 105.607 ↑ 1.3 201 1

Merge Join (cost=1.83..220.52 rows=268 width=16) (actual time=19.366..105.607 rows=201 loops=1)

  • Merge Cond: (cu.current_usersid = u.usersid)
15. 0.199 0.199 ↑ 10.9 202 1

Index Scan using idx_cad_units_current_usersid on cad_units cu (cost=0.28..87.38 rows=2,207 width=16) (actual time=0.020..0.199 rows=202 loops=1)

16. 104.778 104.778 ↑ 1.0 7,674 1

Index Only Scan using users_pkey on users u (cost=0.28..186.88 rows=7,773 width=8) (actual time=19.334..104.778 rows=7,674 loops=1)

  • Heap Fetches: 0
17. 1,061.460 1,061.460 ↑ 1.0 1 17,691

Index Scan using clover_cfs_convert1 on ccmas_tblclosedcalls cc (cost=0.42..1.09 rows=1 width=18) (actual time=0.056..0.060 rows=1 loops=17,691)

  • Index Cond: (incno = vi.incno)
18. 88.455 88.455 ↑ 1.0 1 17,691

Index Scan using stg_combined_incidents_wtrun_convertedid_idx on stg_combined_incidents ci (cost=0.43..1.15 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=17,691)

  • Index Cond: ((wtrun = 1) AND (convertedid = cc.ccmas_tblclosedcalls_zid))
Planning time : 2,929.685 ms
Execution time : 3,669.120 ms