explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DoIz : CI_2

Settings
# exclusive inclusive rows x rows loops node
1. 3.402 69.870 ↓ 1.1 251 1

Hash Left Join (cost=229.38..257.01 rows=225 width=166) (actual time=45.174..69.870 rows=251 loops=1)

  • Hash Cond: (permit_version.permit_version_id = "TrafficSensitiveASD".permit_version_id)
2. 3.072 65.326 ↓ 1.1 251 1

Hash Left Join (cost=160.28..187.05 rows=225 width=168) (actual time=44.007..65.326 rows=251 loops=1)

  • Hash Cond: (permit_version.permit_version_id = permit_alteration.permit_version_id)
3. 3.106 61.704 ↓ 1.1 251 1

Hash Join (cost=158.42..184.59 rows=225 width=164) (actual time=43.431..61.704 rows=251 loops=1)

  • Hash Cond: (work.workstream_id = workstream.workstream_id)
4. 3.043 47.581 ↓ 1.1 251 1

Hash Join (cost=126.66..152.24 rows=225 width=154) (actual time=32.389..47.581 rows=251 loops=1)

  • Hash Cond: (permit.work_id = work.work_id)
5. 3.162 40.000 ↓ 1.1 251 1

Hash Join (cost=96.94..121.91 rows=225 width=154) (actual time=27.814..40.000 rows=251 loops=1)

  • Hash Cond: ((chargeable_item.ha_swa_code)::text = (ha_organisation.swa_org_ref)::text)
6. 3.161 29.164 ↓ 1.1 251 1

Hash Join (cost=64.29..88.68 rows=225 width=128) (actual time=20.109..29.164 rows=251 loops=1)

  • Hash Cond: ((chargeable_item.promoter_swa_code)::text = (promoter_organisation.swa_org_ref)::text)
7. 3.742 17.687 ↓ 1.1 251 1

Hash Join (cost=31.65..55.44 rows=225 width=102) (actual time=11.763..17.687 rows=251 loops=1)

  • Hash Cond: (permit_version.permit_id = permit.permit_id)
8. 2.226 2.226 ↓ 1.1 333 1

Seq Scan on permit_version (cost=0.00..20.41 rows=303 width=12) (actual time=0.015..2.226 rows=333 loops=1)

  • Filter: is_current_version
  • Rows Removed by Filter: 42
9. 1.751 11.719 ↓ 1.1 251 1

Hash (cost=28.80..28.80 rows=228 width=98) (actual time=11.713..11.719 rows=251 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
10. 3.368 9.968 ↓ 1.1 251 1

Hash Join (cost=19.91..28.80 rows=228 width=98) (actual time=5.022..9.968 rows=251 loops=1)

  • Hash Cond: (chargeable_item.permit_id = permit.permit_id)
11. 1.618 1.618 ↓ 1.1 251 1

Seq Scan on chargeable_item (cost=0.00..8.28 rows=228 width=39) (actual time=0.012..1.618 rows=251 loops=1)

12. 2.557 4.982 ↓ 1.1 337 1

Hash (cost=16.07..16.07 rows=307 width=59) (actual time=4.976..4.982 rows=337 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
13. 2.425 2.425 ↓ 1.1 337 1

Seq Scan on permit (cost=0.00..16.07 rows=307 width=59) (actual time=0.013..2.425 rows=337 loops=1)

14. 4.150 8.316 ↑ 1.0 562 1

Hash (cost=25.62..25.62 rows=562 width=30) (actual time=8.311..8.316 rows=562 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 43kB
15. 4.166 4.166 ↑ 1.0 562 1

Seq Scan on organisation promoter_organisation (cost=0.00..25.62 rows=562 width=30) (actual time=0.012..4.166 rows=562 loops=1)

16. 3.815 7.674 ↑ 1.0 562 1

Hash (cost=25.62..25.62 rows=562 width=30) (actual time=7.668..7.674 rows=562 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 43kB
17. 3.859 3.859 ↑ 1.0 562 1

Seq Scan on organisation ha_organisation (cost=0.00..25.62 rows=562 width=30) (actual time=0.015..3.859 rows=562 loops=1)

18. 2.210 4.538 ↓ 1.1 328 1

Hash (cost=25.99..25.99 rows=299 width=8) (actual time=4.532..4.538 rows=328 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
19. 2.328 2.328 ↓ 1.1 328 1

Seq Scan on work (cost=0.00..25.99 rows=299 width=8) (actual time=0.016..2.328 rows=328 loops=1)

20. 5.848 11.017 ↑ 1.0 567 1

Hash (cost=24.67..24.67 rows=567 width=18) (actual time=11.011..11.017 rows=567 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
21. 5.169 5.169 ↑ 1.0 567 1

Seq Scan on workstream (cost=0.00..24.67 rows=567 width=18) (actual time=0.017..5.169 rows=567 loops=1)

22. 0.273 0.550 ↓ 1.1 42 1

Hash (cost=1.38..1.38 rows=38 width=8) (actual time=0.544..0.550 rows=42 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
23. 0.277 0.277 ↓ 1.1 42 1

Seq Scan on permit_alteration (cost=0.00..1.38 rows=38 width=8) (actual time=0.014..0.277 rows=42 loops=1)

24. 0.065 1.142 ↓ 4.0 8 1

Hash (cost=69.08..69.08 rows=2 width=6) (actual time=1.136..1.142 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.105 1.077 ↓ 4.0 8 1

Subquery Scan on TrafficSensitiveASD (cost=69.04..69.08 rows=2 width=6) (actual time=0.825..1.077 rows=8 loops=1)

26. 0.113 0.972 ↓ 4.0 8 1

Unique (cost=69.04..69.06 rows=2 width=6) (actual time=0.812..0.972 rows=8 loops=1)

27. 0.137 0.859 ↓ 4.5 9 1

Sort (cost=69.04..69.05 rows=2 width=6) (actual time=0.801..0.859 rows=9 loops=1)

  • Sort Key: asd.permit_version_id, (true), (true)
  • Sort Method: quicksort Memory: 25kB
28. 0.116 0.722 ↓ 4.5 9 1

Append (cost=0.00..69.03 rows=2 width=6) (actual time=0.025..0.722 rows=9 loops=1)

29. 0.028 0.028 ↑ 1.0 1 1

Seq Scan on asd (cost=0.00..23.00 rows=1 width=6) (actual time=0.013..0.028 rows=1 loops=1)

  • Filter: (provided AND ((street_special_desig_code)::text = 'Traffic Sensitive'::text))
  • Rows Removed by Filter: 33
30. 0.237 0.578 ↓ 8.0 8 1

Nested Loop Anti Join (cost=0.00..46.01 rows=1 width=6) (actual time=0.054..0.578 rows=8 loops=1)

  • Join Filter: (a1.permit_version_id = asd_1.permit_version_id)
  • Rows Removed by Join Filter: 8
31. 0.088 0.088 ↓ 11.0 11 1

Seq Scan on asd a1 (cost=0.00..23.00 rows=1 width=4) (actual time=0.011..0.088 rows=11 loops=1)

  • Filter: ((street_special_desig_code)::text = 'Traffic Sensitive'::text)
  • Rows Removed by Filter: 23
32. 0.253 0.253 ↑ 1.0 1 11

Seq Scan on asd asd_1 (cost=0.00..23.00 rows=1 width=4) (actual time=0.016..0.023 rows=1 loops=11)

  • Filter: (provided AND ((street_special_desig_code)::text = 'Traffic Sensitive'::text))
  • Rows Removed by Filter: 28
Planning time : 1.520 ms
Execution time : 71.994 ms