explain.depesz.com

PostgreSQL's explain analyze made readable

Result: INlm

Settings
# exclusive inclusive rows x rows loops node
1. 16.955 58,500.090 ↓ 26.5 12,633 1

Nested Loop (cost=366.05..16,070.70 rows=477 width=163) (actual time=7.502..58,500.090 rows=12,633 loops=1)

2. 3.930 245.005 ↓ 26.5 12,633 1

Nested Loop (cost=365.76..13,412.58 rows=477 width=90) (actual time=6.775..245.005 rows=12,633 loops=1)

3. 8.921 215.809 ↓ 26.5 12,633 1

Nested Loop Left Join (cost=365.48..13,265.86 rows=477 width=65) (actual time=6.760..215.809 rows=12,633 loops=1)

  • Join Filter: (assessmentinprogress."timestamp" > '2018-01-01 00:00:00'::timestamp without time zone)
  • Rows Removed by Join Filter: 869
4. 7.889 194.604 ↓ 25.8 12,284 1

Nested Loop Left Join (cost=365.06..12,955.34 rows=477 width=57) (actual time=6.742..194.604 rows=12,284 loops=1)

5. 17.138 162.557 ↓ 25.3 12,079 1

Nested Loop (cost=364.63..12,644.83 rows=477 width=49) (actual time=6.715..162.557 rows=12,079 loops=1)

6. 37.241 90.522 ↓ 17.5 18,299 1

Hash Join (cost=364.20..11,962.61 rows=1,048 width=41) (actual time=6.689..90.522 rows=18,299 loops=1)

  • Hash Cond: (p.permitownerorganisationid = ownr.id)
7. 46.648 46.648 ↑ 1.0 151,939 1

Seq Scan on permit p (cost=0.00..9,680.39 rows=151,939 width=16) (actual time=0.038..46.648 rows=151,939 loops=1)

8. 0.024 6.633 ↑ 3.4 13 1

Hash (cost=363.65..363.65 rows=44 width=33) (actual time=6.633..6.633 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 6.609 6.609 ↑ 3.4 13 1

Seq Scan on organisation ownr (cost=0.00..363.65 rows=44 width=33) (actual time=0.116..6.609 rows=13 loops=1)

  • Filter: (isworkplacemodule AND (NOT isnonmoorpsm) AND ((remarks)::text !~~* 'TOPDesk%'::text) AND (organisationroleid = 3))
  • Rows Removed by Filter: 6367
10. 54.897 54.897 ↑ 1.0 1 18,299

Index Scan using ix_permithistory_permitid on permithistory assigned (cost=0.42..0.64 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=18,299)

  • Index Cond: ((permitid = p.id) AND (permitstatustypeid = 4))
  • Filter: ("timestamp" > '2018-01-01 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 0
11. 24.158 24.158 ↑ 1.0 1 12,079

Index Scan using ix_permithistory_permitid on permithistory assessmentinprogress (cost=0.42..0.64 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=12,079)

  • Index Cond: ((p.id = permitid) AND (permitstatustypeid = 6))
  • Filter: ("timestamp" > '2018-01-01 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 0
12. 12.284 12.284 ↓ 0.0 0 12,284

Index Scan using ix_permithistory_permitid on permithistory rejected (cost=0.42..0.64 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=12,284)

  • Index Cond: ((p.id = permitid) AND (permitstatustypeid = 3))
13. 25.266 25.266 ↑ 1.0 1 12,633

Index Scan using pk_organisation on organisation psm (cost=0.28..0.31 rows=1 width=33) (actual time=0.002..0.002 rows=1 loops=12,633)

  • Index Cond: (id = p.permitpublicspacemanagerid)
14. 12.633 12.633 ↑ 1.0 1 12,633

Index Scan using pk_organisation on organisation assignissuer (cost=0.28..0.31 rows=1 width=33) (actual time=0.001..0.001 rows=1 loops=12,633)

  • Index Cond: (id = p.assignmentgiverid)
15.          

SubPlan (forNested Loop)

16. 58,225.497 58,225.497 ↑ 1,000.0 1 12,633

ProjectSet (cost=0.00..5.27 rows=1,000 width=8) (actual time=4.608..4.609 rows=1 loops=12,633)

17. 0.000 0.000 ↑ 1.0 1 12,633

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=12,633)

Planning time : 5.842 ms