explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2LuZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.138 26,887.345 ↓ 0.0 0 1

Insert on stg_combined_incidents (cost=98,664.51..1,737,558.94 rows=10 width=841) (actual time=26,887.345..26,887.345 rows=0 loops=1)

2. 0.114 26,887.207 ↑ 1.0 10 1

Subquery Scan on *SELECT* (cost=98,664.51..1,737,558.94 rows=10 width=841) (actual time=3,145.698..26,887.207 rows=10 loops=1)

3. 0.000 26,887.093 ↑ 1.0 10 1

Limit (cost=98,664.51..1,737,558.44 rows=10 width=120) (actual time=3,145.684..26,887.093 rows=10 loops=1)

4.          

CTE get_agg_1

5. 0.001 279.626 ↓ 0.0 0 1

GroupAggregate (cost=79,764.90..86,197.10 rows=257,288 width=37) (actual time=279.626..279.626 rows=0 loops=1)

  • Group Key: cd.cdcomp
6. 0.006 279.625 ↓ 0.0 0 1

Sort (cost=79,764.90..80,408.12 rows=257,288 width=37) (actual time=279.625..279.625 rows=0 loops=1)

  • Sort Key: cd.cdcomp
  • Sort Method: quicksort Memory: 25kB
7. 41.488 279.619 ↓ 0.0 0 1

Hash Join (cost=2.31..49,605.18 rows=257,288 width=37) (actual time=279.619..279.619 rows=0 loops=1)

  • Hash Cond: (cd.z_z_disposition_cad_incident_dispositions = cid.cad_incident_dispositionsid)
8. 238.099 238.099 ↑ 1.0 886,953 1

Seq Scan on caddspo cd (cost=0.00..43,703.00 rows=887,200 width=12) (actual time=0.003..238.099 rows=886,953 loops=1)

9. 0.014 0.032 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=33) (actual time=0.032..0.032 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
10. 0.018 0.018 ↑ 1.0 58 1

Seq Scan on cad_incident_dispositions cid (cost=0.00..1.58 rows=58 width=33) (actual time=0.003..0.018 rows=58 loops=1)

  • Filter: (description IS NOT NULL)
11.          

Initplan (forLimit)

12. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on cad_incident_code_mod_types (cost=0.00..1.04 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=1)

  • Filter: ((code)::text = 'A'::text)
  • Rows Removed by Filter: 3
13. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on cad_incidents_status_types (cost=0.00..1.05 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1)

  • Filter: ((abbreviation)::text = 'C'::text)
  • Rows Removed by Filter: 3
14. 0.264 26,887.091 ↑ 264,551.3 10 1

Nested Loop Left Join (cost=12,465.32..433,571,532,686.36 rows=2,645,513 width=120) (actual time=3,145.684..26,887.091 rows=10 loops=1)

  • Join Filter: (addr.cadaddr_zid = pt_a.convertedid)
15. 0.031 283.407 ↑ 264,551.3 10 1

Nested Loop Left Join (cost=12,464.89..88,414,717.40 rows=2,645,513 width=120) (actual time=282.708..283.407 rows=10 loops=1)

16. 0.242 283.046 ↑ 264,551.3 10 1

Hash Left Join (cost=12,464.46..86,616,528.46 rows=2,645,513 width=112) (actual time=282.679..283.046 rows=10 loops=1)

  • Hash Cond: (cc.cfcomp = agg1.cdcomp)
17. 0.122 3.176 ↑ 31,123.7 85 1

Hash Left Join (cost=333.60..1,461,932.97 rows=2,645,513 width=48) (actual time=2.819..3.176 rows=85 loops=1)

  • Hash Cond: ((((cc.cftkre)::bigint)::numeric = ct.badge_num) AND ((cc.cftkrk)::numeric = ct.key_num))
18. 0.156 1.768 ↑ 31,123.7 85 1

Hash Left Join (cost=166.80..985,411.55 rows=2,645,513 width=58) (actual time=1.523..1.768 rows=85 loops=1)

  • Hash Cond: ((((cc.cfusre)::bigint)::numeric = d.badge_num) AND ((cc.cfusrk)::numeric = d.key_num))
19. 0.111 0.111 ↑ 31,123.7 85 1

Seq Scan on cadcall cc (cost=0.00..508,890.13 rows=2,645,513 width=68) (actual time=0.005..0.111 rows=85 loops=1)

20. 0.360 1.501 ↑ 1.0 1,152 1

Hash (cost=149.52..149.52 rows=1,152 width=13) (actual time=1.501..1.501 rows=1,152 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 64kB
21. 1.141 1.141 ↑ 1.0 1,152 1

Seq Scan on chfpemp d (cost=0.00..149.52 rows=1,152 width=13) (actual time=0.003..1.141 rows=1,152 loops=1)

22. 0.326 1.286 ↑ 1.0 1,152 1

Hash (cost=149.52..149.52 rows=1,152 width=13) (actual time=1.286..1.286 rows=1,152 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 64kB
23. 0.960 0.960 ↑ 1.0 1,152 1

Seq Scan on chfpemp ct (cost=0.00..149.52 rows=1,152 width=13) (actual time=0.002..0.960 rows=1,152 loops=1)

24. 0.001 279.628 ↓ 0.0 0 1

Hash (cost=5,145.76..5,145.76 rows=257,288 width=96) (actual time=279.628..279.628 rows=0 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 512kB
25. 279.627 279.627 ↓ 0.0 0 1

CTE Scan on get_agg_1 agg1 (cost=0.00..5,145.76 rows=257,288 width=96) (actual time=279.627..279.627 rows=0 loops=1)

26. 0.330 0.330 ↑ 2.0 1 10

Index Scan using cadaddr_cacomp_idx on cadaddr addr (cost=0.43..0.66 rows=2 width=16) (actual time=0.031..0.033 rows=1 loops=10)

  • Index Cond: (cacomp = cc.cfcomp)
  • Filter: ((caatyp = '1'::numeric) AND (caaseq = '0'::numeric))
  • Rows Removed by Filter: 0
27. 0.009 66.500 ↓ 0.0 0 10

Materialize (cost=0.43..74,321.21 rows=1 width=12) (actual time=6.650..6.650 rows=0 loops=10)

28. 66.491 66.491 ↓ 0.0 0 1

Index Scan using pt_addresses_convertedid_wtrun_idx on pt_addresses pt_a (cost=0.43..74,321.21 rows=1 width=12) (actual time=66.491..66.491 rows=0 loops=1)

  • Index Cond: (wtrun = 106)
29.          

SubPlan (forNested Loop Left Join)

30. 0.020 1,706.380 ↓ 0.0 0 10

Limit (cost=45,921.01..45,921.01 rows=1 width=9) (actual time=170.638..170.638 rows=0 loops=10)

31. 0.110 1,706.360 ↓ 0.0 0 10

Sort (cost=45,921.01..45,921.01 rows=1 width=9) (actual time=170.636..170.636 rows=0 loops=10)

  • Sort Key: cd1.cdseq_num DESC
  • Sort Method: quicksort Memory: 25kB
32. 1,706.250 1,706.250 ↓ 0.0 0 10

Seq Scan on caddspo cd1 (cost=0.00..45,921.00 rows=1 width=9) (actual time=141.209..170.625 rows=0 loops=10)

  • Filter: (cdcomp = cc.cfcomp)
  • Rows Removed by Filter: 886953
33. 0.050 24,830.540 ↑ 1.0 1 10

Limit (cost=0.00..117,934.86 rows=1 width=4) (actual time=2,483.054..2,483.054 rows=1 loops=10)

34. 24,830.490 24,830.490 ↑ 6.0 1 10

Seq Scan on cadtimes ct_1 (cost=0.00..707,609.17 rows=6 width=4) (actual time=2,483.049..2,483.049 rows=1 loops=10)

  • Filter: ((ctcomp = cc.cfcomp) AND (ctstat = '11'::numeric))
  • Rows Removed by Filter: 11386368
Planning time : 4.520 ms
Execution time : 26,887.809 ms