explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fZuU

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 10.614 ↓ 0.0 0 1

Nested Loop (cost=13.62..4,748.28 rows=1 width=669) (actual time=10.614..10.614 rows=0 loops=1)

  • Join Filter: ((pr.permitid = p.id) AND (org.id = p.permitownerorganisationid))
2.          

Initplan (for Nested Loop)

3. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on emailnotificationtype (cost=0.00..1.16 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1)

  • Filter: ((name)::text = 'RemBeforeAssesmentPeriodEnds'::text)
  • Rows Removed by Filter: 12
4. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on emailnotificationtype emailnotificationtype_1 (cost=0.00..1.16 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1)

  • Filter: ((name)::text = 'RemBeforeDecisionPeriodEnds'::text)
  • Rows Removed by Filter: 12
5. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on emailnotificationtype emailnotificationtype_2 (cost=0.00..1.16 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)

  • Filter: ((name)::text = 'RemBeforeValidityPeriodEnds'::text)
  • Rows Removed by Filter: 12
6. 0.000 0.000 ↓ 0.0 0

Seq Scan on emailnotificationtype emailnotificationtype_3 (cost=0.00..1.16 rows=1 width=4) (never executed)

  • Filter: ((name)::text = 'RemBeforeAssesmentPeriodEnds'::text)
7. 0.000 0.000 ↓ 0.0 0

Seq Scan on emailnotificationtype emailnotificationtype_4 (cost=0.00..1.16 rows=1 width=4) (never executed)

  • Filter: ((name)::text = 'RemBeforeDecisionPeriodEnds'::text)
8. 0.000 0.000 ↓ 0.0 0

Seq Scan on emailnotificationtype emailnotificationtype_5 (cost=0.00..1.16 rows=1 width=4) (never executed)

  • Filter: ((name)::text = 'RemBeforeValidityPeriodEnds'::text)
9. 0.000 0.000 ↓ 0.0 0

Seq Scan on emailnotificationtype emailnotificationtype_6 (cost=0.00..1.16 rows=1 width=4) (never executed)

  • Filter: ((name)::text = 'RemBeforeAssesmentPeriodEnds'::text)
10. 0.000 0.000 ↓ 0.0 0

Seq Scan on emailnotificationtype emailnotificationtype_7 (cost=0.00..1.16 rows=1 width=4) (never executed)

  • Filter: ((name)::text = 'RemBeforeDecisionPeriodEnds'::text)
11. 0.000 0.000 ↓ 0.0 0

Seq Scan on emailnotificationtype emailnotificationtype_8 (cost=0.00..1.16 rows=1 width=4) (never executed)

  • Filter: ((name)::text = 'RemBeforeValidityPeriodEnds'::text)
12. 0.000 0.000 ↓ 0.0 0

Seq Scan on emailnotificationtype emailnotificationtype_9 (cost=0.00..1.16 rows=1 width=4) (never executed)

  • Filter: ((name)::text = 'RemBeforeValidityPeriodEnds'::text)
13. 0.001 10.614 ↓ 0.0 0 1

Nested Loop (cost=1.57..4,555.45 rows=2 width=16) (actual time=10.614..10.614 rows=0 loops=1)

14. 0.000 10.613 ↓ 0.0 0 1

Nested Loop (cost=1.28..4,554.16 rows=3 width=53) (actual time=10.613..10.613 rows=0 loops=1)

15. 0.000 10.613 ↓ 0.0 0 1

Nested Loop (cost=1.00..4,552.58 rows=4 width=49) (actual time=10.613..10.613 rows=0 loops=1)

  • Join Filter: (usr.id = un.userid)
16. 0.000 10.613 ↓ 0.0 0 1

Nested Loop (cost=0.71..4,539.32 rows=41 width=57) (actual time=10.613..10.613 rows=0 loops=1)

17. 0.001 10.613 ↓ 0.0 0 1

Nested Loop (cost=0.42..4,509.87 rows=92 width=12) (actual time=10.613..10.613 rows=0 loops=1)

18. 10.612 10.612 ↓ 0.0 0 1

Seq Scan on permitreminder pr (cost=0.00..3,765.05 rows=80 width=4) (actual time=10.612..10.612 rows=0 loops=1)

  • Filter: (CASE WHEN (2 = $0) THEN isreminderonassignment WHEN (2 = $1) THEN isreminderondecision WHEN (2 = $2) THEN isreminderonexecution ELSE NULL::boolean END AND (NOT CASE WHEN (4 = $3) THEN COALESCE(isassignmentremindermailsent, false) WHEN (4 = $4) THEN COALESCE(isdecisionremindermailsent, false) WHEN (4 = $5) THEN COALESCE(isexecutionremindermailsent, false) ELSE NULL::boolean END) AND (to_char(now(), 'DDmmYYYY'::text) = CASE WHEN (4 = $6) THEN to_char(assignmentreminderduedate, 'DDmmYYYY'::text) WHEN (4 = $7) THEN to_char(decisionreminderduedate, 'DDmmYYYY'::text) WHEN (4 = $8) THEN to_char(executionreminderduedate, 'DDmmYYYY'::text) ELSE NULL::text END))
  • Rows Removed by Filter: 64,022
19. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_permithistory_permitid on permithistory ph (cost=0.42..9.30 rows=1 width=8) (never executed)

  • Index Cond: (permitid = pr.permitid)
  • Filter: (permitstatustypeid = 4)
20. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_user on "user" usr (cost=0.29..0.32 rows=1 width=45) (never executed)

  • Index Cond: (id = ph.userid)
  • Filter: (NOT isdeleted)
21. 0.000 0.000 ↓ 0.0 0

Index Only Scan using user_id_emailnotificationtype_id_unique on useremailnotificationpermission un (cost=0.29..0.31 rows=1 width=4) (never executed)

  • Index Cond: ((userid = ph.userid) AND (emailnotificationtypeid = 4))
  • Heap Fetches: 0
22. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_organisation on organisation org (cost=0.28..0.40 rows=1 width=4) (never executed)

  • Index Cond: (id = usr.organisationid)
  • Filter: emailenabled
23. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_users on users mem (cost=0.29..0.43 rows=1 width=16) (never executed)

  • Index Cond: (user_id = (usr.aspmembershipid)::uuid)
  • Filter: approved
24. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_permit on permit p (cost=0.42..90.59 rows=1 width=669) (never executed)

  • Index Cond: (id = ph.permitid)
  • Filter: ((permitstatustypeid = 4) AND (0 = CASE WHEN (2 = $9) THEN (SubPlan 11) ELSE '0'::bigint END))
25.          

SubPlan (for Index Scan)

26. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=89.91..89.92 rows=1 width=8) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_roadworkrequest_permitid on roadworkrequest rr (cost=0.43..89.78 rows=53 width=4) (never executed)

  • Index Cond: (permitid = p.id)