explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jr8N

Settings
# exclusive inclusive rows x rows loops node
1. 0.131 25,001.704 ↓ 0.0 0 1

Insert on stg_combined_incidents (cost=282,763.97..2,017,601.19 rows=10 width=841) (actual time=25,001.704..25,001.704 rows=0 loops=1)

2. 0.109 25,001.573 ↑ 1.0 10 1

Subquery Scan on *SELECT* (cost=282,763.97..2,017,601.19 rows=10 width=841) (actual time=7,597.116..25,001.573 rows=10 loops=1)

3. 0.000 25,001.464 ↑ 1.0 10 1

Limit (cost=282,763.97..2,017,600.69 rows=10 width=120) (actual time=7,597.103..25,001.464 rows=10 loops=1)

4.          

CTE get_agg_1

5. 0.003 260.923 ↓ 0.0 0 1

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

  • Group Key: cd.cdcomp
6. 0.027 260.920 ↓ 0.0 0 1

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

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

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

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

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

9. 0.011 6.765 ↑ 1.0 58 1

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

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

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

  • Filter: (description IS NOT NULL)
11.          

Initplan (forLimit)

12. 12.804 12.804 ↓ 0.0 0 1

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

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

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

  • Filter: ((abbreviation)::text = 'C'::text)
  • Rows Removed by Filter: 3
14. 28.783 25,001.457 ↑ 264,551.3 10 1

Nested Loop Left Join (cost=196,564.77..458,953,506,491.10 rows=2,645,513 width=120) (actual time=7,597.102..25,001.457 rows=10 loops=1)

  • Join Filter: (addr.cadaddr_zid = pt_a.convertedid)
15. 4.664 3,823.584 ↑ 264,551.3 10 1

Nested Loop Left Join (cost=196,564.35..25,470,388,512.27 rows=2,645,513 width=120) (actual time=3,806.042..3,823.584 rows=10 loops=1)

  • Join Filter: (((cc.cftkrk)::numeric = ct.key_num) AND (((cc.cftkre)::bigint)::numeric = ct.badge_num))
  • Rows Removed by Join Filter: 10576
16. 4.836 3,813.980 ↑ 264,551.3 10 1

Nested Loop Left Join (cost=196,564.35..25,378,959,430.59 rows=2,645,513 width=130) (actual time=3,805.825..3,813.980 rows=10 loops=1)

  • Join Filter: (((cc.cfusrk)::numeric = d.key_num) AND (((cc.cfusre)::bigint)::numeric = d.badge_num))
  • Rows Removed by Join Filter: 10576
17. 0.015 3,801.464 ↑ 264,551.3 10 1

Nested Loop Left Join (cost=196,564.35..25,287,530,348.91 rows=2,645,513 width=140) (actual time=3,799.836..3,801.464 rows=10 loops=1)

  • Join Filter: (cc.cfcomp = agg1.cdcomp)
18. 1.468 3,540.519 ↑ 264,551.3 10 1

Hash Left Join (cost=196,564.35..1,771,201.29 rows=2,645,513 width=76) (actual time=3,538.910..3,540.519 rows=10 loops=1)

  • Hash Cond: (cc.cfcomp = addr.cacomp)
19. 7.689 7.689 ↑ 4,506.8 587 1

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

20. 573.924 3,531.362 ↓ 1.1 2,616,744 1

Hash (cost=155,893.49..155,893.49 rows=2,339,668 width=16) (actual time=3,531.362..3,531.362 rows=2,616,744 loops=1)

  • Buckets: 131072 (originally 131072) Batches: 64 (originally 32) Memory Usage: 4097kB
21. 2,957.438 2,957.438 ↓ 1.1 2,616,744 1

Seq Scan on cadaddr addr (cost=0.00..155,893.49 rows=2,339,668 width=16) (actual time=13.416..2,957.438 rows=2,616,744 loops=1)

  • Filter: ((caatyp = '1'::numeric) AND (caaseq = '0'::numeric))
  • Rows Removed by Filter: 310447
22. 260.930 260.930 ↓ 0.0 0 10

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

23. 0.670 7.680 ↑ 1.1 1,059 10

Materialize (cost=0.00..155.28 rows=1,152 width=13) (actual time=0.165..0.768 rows=1,059 loops=10)

24. 7.010 7.010 ↑ 1.0 1,152 1

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

25. 0.627 4.940 ↑ 1.1 1,059 10

Materialize (cost=0.00..155.28 rows=1,152 width=13) (actual time=0.000..0.494 rows=1,059 loops=10)

26. 4.313 4.313 ↑ 1.0 1,152 1

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

27. 0.005 275.960 ↓ 0.0 0 10

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

28. 275.955 275.955 ↓ 0.0 0 1

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

  • Index Cond: (wtrun = 106)
29.          

SubPlan (forNested Loop Left Join)

30. 0.030 1,690.610 ↓ 0.0 0 10

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

31. 0.110 1,690.580 ↓ 0.0 0 10

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

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

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

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

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

34. 19,182.470 19,182.470 ↑ 6.0 1 10

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

  • Filter: ((ctcomp = cc.cfcomp) AND (ctstat = '11'::numeric))
  • Rows Removed by Filter: 8182081
Planning time : 5.867 ms
Execution time : 25,022.458 ms