explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vvAc

Settings
# exclusive inclusive rows x rows loops node
1. 0.294 1,994.913 ↓ 0.0 0 1

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

2. 0.100 1,994.619 ↑ 1.0 10 1

Subquery Scan on *SELECT* (cost=98,664.51..558,477.21 rows=10 width=841) (actual time=499.032..1,994.619 rows=10 loops=1)

3. 0.000 1,994.519 ↑ 1.0 10 1

Limit (cost=98,664.51..558,476.71 rows=10 width=120) (actual time=499.019..1,994.519 rows=10 loops=1)

4.          

CTE get_agg_1

5. 0.001 263.890 ↓ 0.0 0 1

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

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

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

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

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

  • Hash Cond: (cd.z_z_disposition_cad_incident_dispositions = cid.cad_incident_dispositionsid)
8. 223.444 223.444 ↑ 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..223.444 rows=886,953 loops=1)

9. 0.013 0.035 ↑ 1.0 58 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
10. 0.022 0.022 ↑ 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.022 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.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.262 1,994.514 ↑ 261,684.4 10 1

Nested Loop Left Join (cost=12,465.32..120,325,691,077.25 rows=2,616,844 width=120) (actual time=499.018..1,994.514 rows=10 loops=1)

  • Join Filter: (addr.cadaddr_zid = pt_a.convertedid)
15. 0.027 267.882 ↑ 261,684.4 10 1

Nested Loop Left Join (cost=12,464.89..87,467,308.65 rows=2,616,844 width=120) (actual time=267.382..267.882 rows=10 loops=1)

16. 0.222 267.725 ↑ 261,684.4 10 1

Hash Left Join (cost=12,464.46..85,683,310.87 rows=2,616,844 width=112) (actual time=267.361..267.725 rows=10 loops=1)

  • Hash Cond: (cc.cfcomp = agg1.cdcomp)
17. 0.128 3.611 ↑ 30,786.4 85 1

Hash Left Join (cost=333.60..1,451,321.92 rows=2,616,844 width=48) (actual time=3.254..3.611 rows=85 loops=1)

  • Hash Cond: ((((cc.cftkre)::bigint)::numeric = ct.badge_num) AND ((cc.cftkrk)::numeric = ct.key_num))
18. 0.175 1.967 ↑ 30,786.4 85 1

Hash Left Join (cost=166.80..979,962.68 rows=2,616,844 width=58) (actual time=1.729..1.967 rows=85 loops=1)

  • Hash Cond: ((((cc.cfusre)::bigint)::numeric = d.badge_num) AND ((cc.cfusrk)::numeric = d.key_num))
19. 0.095 0.095 ↑ 30,786.4 85 1

Seq Scan on cadcall cc (cost=0.00..508,603.44 rows=2,616,844 width=68) (actual time=0.013..0.095 rows=85 loops=1)

20. 0.411 1.697 ↑ 1.0 1,152 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 64kB
21. 1.286 1.286 ↑ 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.286 rows=1,152 loops=1)

22. 0.409 1.516 ↑ 1.0 1,152 1

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

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

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

24. 0.000 263.892 ↓ 0.0 0 1

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

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

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

26. 0.130 0.130 ↑ 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.011..0.013 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 65.420 ↓ 0.0 0 10

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

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

  • Index Cond: (wtrun = 106)
29.          

SubPlan (forNested Loop Left Join)

30. 0.020 1,660.530 ↓ 0.0 0 10

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

31. 0.090 1,660.510 ↓ 0.0 0 10

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

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

Seq Scan on caddspo cd1 (cost=0.00..45,921.00 rows=1 width=9) (actual time=138.201..166.042 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