explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aD28

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

Limit (cost=16,458.20..16,458.28 rows=1 width=962) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Subquery Scan on customreportpermitview (cost=16,458.20..16,465.08 rows=86 width=962) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Result (cost=16,458.20..16,463.57 rows=86 width=898) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=16,458.20..16,458.41 rows=86 width=866) (actual rows= loops=)

  • Sort Key: x."Permit Number
5. 0.000 0.000 ↓ 0.0

Subquery Scan on x (cost=16,453.71..16,455.43 rows=86 width=866) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

HashAggregate (cost=16,453.71..16,454.57 rows=86 width=866) (actual rows= loops=)

  • Group Key: ownr.name, assignissuer.name, psm.name, p.id, ct.assigneddate, ct.assessmentinprogressdate, ct.rejecteddate, p.createddate, ((SubPlan 1))
7. 0.000 0.000 ↓ 0.0

Append (cost=61.10..16,451.78 rows=86 width=866) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=61.10..6,792.78 rows=2 width=131) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=60.82..6,781.60 rows=2 width=98) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=60.53..6,774.37 rows=22 width=73) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=60.25..6,445.83 rows=1,000 width=48) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Sort (cost=59.83..62.33 rows=1,000 width=32) (actual rows= loops=)

  • Sort Key: ct.permitid DESC, ct.ranking
13. 0.000 0.000 ↓ 0.0

Function Scan on crosstab ct (cost=0.00..10.00 rows=1,000 width=32) (actual rows= loops=)

  • Filter: ((rejecteddate IS NOT NULL) OR (assessmentinprogressdate IS NOT NULL))
14. 0.000 0.000 ↓ 0.0

Index Scan using pk_permit on permit p (cost=0.42..6.37 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (id = ct.permitid)
15. 0.000 0.000 ↓ 0.0

Index Scan using ix_organisation_id_emailenabled on organisation psm (cost=0.28..0.33 rows=1 width=33) (actual rows= loops=)

  • Index Cond: (id = p.permitpublicspacemanagerid)
  • Filter: (isnonmoorpsm AND (((remarks)::text !~~* 'auto%'::text) OR (remarks IS NULL)))
16. 0.000 0.000 ↓ 0.0

Index Scan using ix_organisation_id_emailenabled on organisation ownr (cost=0.28..0.33 rows=1 width=33) (actual rows= loops=)

  • Index Cond: (id = p.permitownerorganisationid)
  • Filter: (isworkplacemodule AND (organisationroleid = 3))
17. 0.000 0.000 ↓ 0.0

Index Scan using ix_organisation_id_emailenabled on organisation assignissuer (cost=0.28..0.33 rows=1 width=33) (actual rows= loops=)

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

SubPlan (for Nested Loop)

19. 0.000 0.000 ↓ 0.0

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

20. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=61.10..6,830.18 rows=83 width=131) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=61.10..6,829.15 rows=83 width=127) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Nested Loop (cost=60.82..6,802.09 rows=83 width=98) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Nested Loop (cost=60.53..6,774.37 rows=85 width=73) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Nested Loop (cost=60.25..6,445.83 rows=1,000 width=48) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Sort (cost=59.83..62.33 rows=1,000 width=32) (actual rows= loops=)

  • Sort Key: ct_1.permitid DESC, ct_1.ranking
27. 0.000 0.000 ↓ 0.0

Function Scan on crosstab ct_1 (cost=0.00..10.00 rows=1,000 width=32) (actual rows= loops=)

  • Filter: ((rejecteddate IS NOT NULL) OR (assessmentinprogressdate IS NOT NULL))
28. 0.000 0.000 ↓ 0.0

Index Scan using pk_permit on permit p_1 (cost=0.42..6.37 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (id = ct_1.permitid)
29. 0.000 0.000 ↓ 0.0

Index Scan using ix_organisation_id_emailenabled on organisation ownr_1 (cost=0.28..0.33 rows=1 width=33) (actual rows= loops=)

  • Index Cond: (id = p_1.permitownerorganisationid)
  • Filter: (isworkplacemodule AND (organisationroleid = 3))
30. 0.000 0.000 ↓ 0.0

Index Scan using ix_organisation_id_emailenabled on organisation psm_1 (cost=0.28..0.33 rows=1 width=33) (actual rows= loops=)

  • Index Cond: (id = p_1.permitpublicspacemanagerid)
  • Filter: (NOT isnonmoorpsm)
31. 0.000 0.000 ↓ 0.0

Index Scan using ix_organisation_id_emailenabled on organisation assignissuer_1 (cost=0.28..0.33 rows=1 width=33) (actual rows= loops=)

  • Index Cond: (id = p_1.assignmentgiverid)
32. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 3 (cost=96.89..2,828.79 rows=1 width=131) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Nested Loop (cost=96.89..2,828.78 rows=1 width=127) (actual rows= loops=)

  • -> Index Scan using ix_organisation_id_emailenabled on organisation assignissuer_2 (cost=0.28..0.33 rows=1 width=33)" Index Cond: (id = p_2.assignmentgiverid)
34. 0.000 0.000 ↓ 0.0

Nested Loop (cost=96.60..2,828.45 rows=1 width=98) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Hash Join (cost=96.32..2,828.12 rows=1 width=73) (actual rows= loops=)

  • Hash Cond: (p_2.id = permitstatuscustomview.permitid)
36. 0.000 0.000 ↓ 0.0

Nested Loop (cost=11.49..2,739.46 rows=30 width=49) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Seq Scan on organisation psm_2 (cost=0.00..1,383.99 rows=1 width=33) (actual rows= loops=)

  • Filter: (isnonmoorpsm AND ((remarks)::text ~~* 'auto%'::text))
38. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on permit p_2 (cost=11.49..1,351.51 rows=396 width=24) (actual rows= loops=)

  • Recheck Cond: (permitpublicspacemanagerid = psm_2.id)
39. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_permit_owner (cost=0.00..11.39 rows=396 width=0) (actual rows= loops=)

  • Index Cond: (permitpublicspacemanagerid = psm_2.id)
40. 0.000 0.000 ↓ 0.0

Hash (cost=72.33..72.33 rows=1,000 width=28) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Subquery Scan on permitstatuscustomview (cost=59.83..72.33 rows=1,000 width=28) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Sort (cost=59.83..62.33 rows=1,000 width=32) (actual rows= loops=)

  • Sort Key: ct_2.permitid DESC, ct_2.ranking
43. 0.000 0.000 ↓ 0.0

Function Scan on crosstab ct_2 (cost=0.00..10.00 rows=1,000 width=32) (actual rows= loops=)

  • Filter: ((rejecteddate IS NOT NULL) OR (assessmentinprogressdate IS NOT NULL))
44. 0.000 0.000 ↓ 0.0

Index Scan using ix_organisation_id_emailenabled on organisation ownr_2 (cost=0.28..0.33 rows=1 width=33) (actual rows= loops=)

  • Index Cond: (id = p_2.permitownerorganisationid)
  • Filter: (isworkplacemodule AND (organisationroleid = 3))