explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oe2d

Settings
# exclusive inclusive rows x rows loops node
1. 0.178 2,003.857 ↓ 0.0 0 1

Insert on stg_combined_incidents (cost=98,664.51..558,477.16 rows=10 width=841) (actual time=2,003.857..2,003.857 rows=0 loops=1)

2. 0.093 2,003.679 ↑ 1.0 10 1

Subquery Scan on *SELECT* (cost=98,664.51..558,477.16 rows=10 width=841) (actual time=531.831..2,003.679 rows=10 loops=1)

3. 0.000 2,003.586 ↑ 1.0 10 1

Limit (cost=98,664.51..558,476.66 rows=10 width=120) (actual time=531.818..2,003.586 rows=10 loops=1)

4.          

CTE get_agg_1

5. 0.002 296.858 ↓ 0.0 0 1

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

  • Group Key: cd.cdcomp
6. 0.008 296.856 ↓ 0.0 0 1

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

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

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

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

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

9. 0.023 0.049 ↑ 1.0 58 1

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

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

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

  • Filter: (description IS NOT NULL)
11.          

Initplan (forLimit)

12. 0.005 0.005 ↓ 0.0 0 1

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

  • Filter: ((code)::text = 'A'::text)
  • Rows Removed by Filter: 3
13. 0.003 0.003 ↑ 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.003 rows=1 loops=1)

  • Filter: ((abbreviation)::text = 'C'::text)
  • Rows Removed by Filter: 3
14. 0.281 2,003.584 ↑ 264,551.3 10 1

Nested Loop Left Join (cost=12,465.32..121,643,915,139.29 rows=2,645,513 width=120) (actual time=531.818..2,003.584 rows=10 loops=1)

  • Join Filter: (addr.cadaddr_zid = pt_a.convertedid)
15. 0.030 302.113 ↑ 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=301.610..302.113 rows=10 loops=1)

16. 0.270 301.973 ↑ 264,551.3 10 1

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

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

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

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

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

  • Hash Cond: ((((cc.cfusre)::bigint)::numeric = d.badge_num) AND ((cc.cfusrk)::numeric = d.key_num))
19. 0.112 0.112 ↑ 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.007..0.112 rows=85 loops=1)

20. 0.588 2.318 ↑ 1.0 1,152 1

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

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

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

22. 0.574 2.120 ↑ 1.0 1,152 1

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

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

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

24. 0.000 296.859 ↓ 0.0 0 1

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

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

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

26. 0.110 0.110 ↑ 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.010..0.011 rows=1 loops=10)

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

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

28. 64.206 64.206 ↓ 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=64.206..64.206 rows=0 loops=1)

  • Index Cond: (wtrun = 106)
29.          

SubPlan (forNested Loop Left Join)

30. 0.020 1,636.560 ↓ 0.0 0 10

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

31. 0.090 1,636.540 ↓ 0.0 0 10

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

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

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

  • Filter: (cdcomp = cc.cfcomp)
  • Rows Removed by Filter: 886953
33. 0.020 0.420 ↑ 1.0 1 10

Limit (cost=0.56..26.68 rows=1 width=4) (actual time=0.042..0.042 rows=1 loops=10)

34. 0.400 0.400 ↑ 6.0 1 10

Index Scan using cadtimes_ctcomp_idx on cadtimes ct_1 (cost=0.56..157.30 rows=6 width=4) (actual time=0.040..0.040 rows=1 loops=10)

  • Index Cond: (ctcomp = cc.cfcomp)
  • Filter: (ctstat = '11'::numeric)
  • Rows Removed by Filter: 2
Planning time : 6.543 ms
Execution time : 2,004.397 ms