explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O8Wq

Settings
# exclusive inclusive rows x rows loops node
1. 0.048 2,968.812 ↑ 1.0 50 1

Limit (cost=4,429.06..5,248.19 rows=50 width=829) (actual time=2,951.363..2,968.812 rows=50 loops=1)

2.          

CTE workspaces

3. 0.438 0.438 ↑ 1.0 1 1

Seq Scan on ag_workspace_hierarchy (cost=0.00..27.09 rows=1 width=33) (actual time=0.270..0.438 rows=1 loops=1)

  • Filter: ((parent)::text = '0600000272'::text)
  • Rows Removed by Filter: 1286
4.          

CTE permission

5. 0.000 0.000 ↓ 0.0 0

Seq Scan on ag_acl_user_permission (cost=0.00..117.71 rows=6 width=11) (never executed)

  • Filter: (r AND ((user_id)::text = '0300000544'::text))
6. 15.349 2,968.764 ↑ 48.1 50 1

Result (cost=4,284.26..43,667.79 rows=2,404 width=829) (actual time=2,951.361..2,968.764 rows=50 loops=1)

7. 953.234 2,950.915 ↑ 48.1 50 1

Sort (cost=4,284.26..4,290.27 rows=2,404 width=256) (actual time=2,950.894..2,950.915 rows=50 loops=1)

  • Sort Key: o.id DESC
  • Sort Method: top-N heapsort Memory: 64kB
8. 464.696 1,997.681 ↓ 7.8 18,715 1

WindowAgg (cost=2,948.32..4,204.41 rows=2,404 width=256) (actual time=1,524.760..1,997.681 rows=18,715 loops=1)

9. 1,367.870 1,532.985 ↓ 7.8 18,715 1

Sort (cost=2,948.32..2,954.33 rows=2,404 width=228) (actual time=1,524.595..1,532.985 rows=18,715 loops=1)

  • Sort Key: o.id
  • Sort Method: quicksort Memory: 6346kB
10. 30.442 165.115 ↓ 7.8 18,715 1

Hash Left Join (cost=1,550.91..2,813.32 rows=2,404 width=228) (actual time=52.166..165.115 rows=18,715 loops=1)

  • Hash Cond: (o.col2 = (ws0600000271.id)::text)
11. 25.434 86.094 ↓ 7.8 18,715 1

Hash Join (cost=78.88..1,308.23 rows=2,404 width=166) (actual time=3.514..86.094 rows=18,715 loops=1)

  • Hash Cond: ((o.acl_id)::text = (a.id)::text)
  • Join Filter: ((((o.org)::text = '0200002f'::text) AND (NOT COALESCE(a.block_admin_access, false))) OR (alternatives: SubPlan 4 or hashed SubPlan 5))
12. 23.928 57.676 ↓ 5.8 18,715 1

Hash Semi Join (cost=0.03..1,220.82 rows=3,206 width=175) (actual time=0.512..57.676 rows=18,715 loops=1)

  • Hash Cond: ((o.workspace_id)::text = (w.id)::text)
13. 33.298 33.298 ↓ 2.0 25,599 1

Seq Scan on ag_object o (cost=0.00..1,151.46 rows=12,823 width=175) (actual time=0.033..33.298 rows=25,599 loops=1)

  • Filter: (NOT COALESCE(deleted, false))
  • Rows Removed by Filter: 47
14. 0.006 0.450 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=38) (actual time=0.449..0.450 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.444 0.444 ↑ 1.0 1 1

CTE Scan on workspaces w (cost=0.00..0.02 rows=1 width=38) (actual time=0.274..0.444 rows=1 loops=1)

16. 1.308 2.984 ↑ 1.0 1,993 1

Hash (cost=53.93..53.93 rows=1,993 width=12) (actual time=2.984..2.984 rows=1,993 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 100kB
17. 1.676 1.676 ↑ 1.0 1,993 1

Seq Scan on ag_acl a (cost=0.00..53.93 rows=1,993 width=12) (actual time=0.016..1.676 rows=1,993 loops=1)

18.          

SubPlan (forHash Join)

19. 0.000 0.000 ↓ 0.0 0

CTE Scan on permission p (cost=0.00..0.14 rows=1 width=0) (never executed)

  • Filter: ((acl_id)::text = (o.acl_id)::text)
20. 0.000 0.000 ↓ 0.0 0

CTE Scan on permission p_1 (cost=0.00..0.12 rows=6 width=32) (never executed)

21. 23.649 48.579 ↑ 1.0 25,646 1

Hash (cost=1,151.46..1,151.46 rows=25,646 width=62) (actual time=48.579..48.579 rows=25,646 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2692kB
22. 24.930 24.930 ↑ 1.0 25,646 1

Seq Scan on ag_object ws0600000271 (cost=0.00..1,151.46 rows=25,646 width=62) (actual time=0.027..24.930 rows=25,646 loops=1)

23.          

SubPlan (forResult)

24. 2.500 2.500 ↑ 1.0 1 50

Index Scan using ag_user_pkey on ag_user u (cost=0.28..8.29 rows=1 width=26) (actual time=0.049..0.050 rows=1 loops=50)

  • Index Cond: ((id)::text = (o.created_by)::text)
Planning time : 4.013 ms
Execution time : 2,969.373 ms