explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lfPY

Settings
# exclusive inclusive rows x rows loops node
1. 3.783 12,390.961 ↓ 220.1 1,761 1

Nested Loop (cost=366.05..12,776.35 rows=8 width=155) (actual time=9.153..12,390.961 rows=1,761 loops=1)

2. 2.387 127.096 ↓ 220.1 1,761 1

Nested Loop (cost=365.76..12,689.65 rows=8 width=90) (actual time=4.491..127.096 rows=1,761 loops=1)

3. 1.041 121.187 ↓ 220.1 1,761 1

Nested Loop (cost=365.48..12,687.19 rows=8 width=65) (actual time=4.484..121.187 rows=1,761 loops=1)

4. 0.375 115.991 ↓ 46.2 831 1

Nested Loop (cost=365.06..12,675.43 rows=18 width=65) (actual time=4.479..115.991 rows=831 loops=1)

5. 0.216 112.626 ↓ 31.8 1,495 1

Nested Loop (cost=364.63..12,644.83 rows=47 width=53) (actual time=4.466..112.626 rows=1,495 loops=1)

6. 26.415 75.812 ↓ 17.5 18,299 1

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

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

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

8. 0.008 2.275 ↑ 3.4 13 1

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

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

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

  • Filter: (isworkplacemodule AND (NOT isnonmoorpsm) AND ((remarks)::text !~~* 'TOPDesk%'::text) AND (organisationroleid = 3))
  • Rows Removed by Filter: 6367
10. 36.598 36.598 ↓ 0.0 0 18,299

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

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

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

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

Index Scan using ix_permithistory_permitid on permithistory assigned (cost=0.42..0.64 rows=1 width=12) (actual time=0.002..0.005 rows=2 loops=831)

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

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=1,761)

  • Index Cond: (id = p.permitpublicspacemanagerid)
14. 1.761 1.761 ↑ 1.0 1 1,761

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=1,761)

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

SubPlan (forNested Loop)

16. 0.000 0.000 ↓ 0.0 0

ProjectSet (cost=0.00..5.27 rows=1,000 width=8) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=0) (never executed)

18. 12,258.321 12,258.321 ↑ 1,000.0 1 1,761

ProjectSet (cost=0.00..5.27 rows=1,000 width=8) (actual time=6.961..6.961 rows=1 loops=1,761)

19. 0.000 0.000 ↑ 1.0 1 1,761

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

Planning time : 3.357 ms