explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BXPX : Optimization for: folder-path-listing; plan #mlLc

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.023 6.012 ↑ 18.0 5 1

Hash Join (cost=22,382.95..22,389.02 rows=90 width=32) (actual time=6.005..6.012 rows=5 loops=1)

  • Hash Cond: (objs.object_id = access.object_id)
2.          

CTE objs

3. 0.012 0.139 ↑ 36.0 5 1

HashAggregate (cost=18,905.39..18,907.19 rows=180 width=126) (actual time=0.136..0.139 rows=5 loops=1)

4. 0.001 0.127 ↑ 36.0 5 1

Append (cost=0.69..18,904.49 rows=180 width=126) (actual time=0.093..0.127 rows=5 loops=1)

5. 0.026 0.026 ↓ 0.0 0 1

Index Scan using idx_coll_main_parent_coll_name on r_coll_main (cost=0.69..303.04 rows=146 width=121) (actual time=0.026..0.026 rows=0 loops=1)

  • Index Cond: ((parent_coll_name)::text = '/iplant/home/psarando/analyses/gen-files-folders_2-595x5_2975_paths-2019-09-26-20-36-11.1/5-files-595-folders-2-deep/folder-272'::text)
6. 0.005 0.100 ↑ 6.8 5 1

Subquery Scan on *SELECT* 2 (cost=200.48..18,599.99 rows=34 width=146) (actual time=0.066..0.100 rows=5 loops=1)

7. 0.021 0.095 ↑ 6.8 5 1

Nested Loop (cost=200.48..18,599.65 rows=34 width=146) (actual time=0.066..0.095 rows=5 loops=1)

8. 0.027 0.027 ↑ 1.0 1 1

Index Scan using idx_coll_main3 on r_coll_main c (cost=0.69..4.70 rows=1 width=121) (actual time=0.026..0.027 rows=1 loops=1)

  • Index Cond: ((coll_name)::text = '/iplant/home/psarando/analyses/gen-files-folders_2-595x5_2975_paths-2019-09-26-20-36-11.1/5-files-595-folders-2-deep/folder-272'::text)
9. 0.029 0.047 ↑ 1,838.0 5 1

Bitmap Heap Scan on r_data_main d (cost=199.80..18,502.88 rows=9,190 width=41) (actual time=0.025..0.047 rows=5 loops=1)

  • Recheck Cond: (coll_id = c.coll_id)
10. 0.018 0.018 ↑ 1,838.0 5 1

Bitmap Index Scan on idx_data_main3 (cost=0.00..197.50 rows=9,190 width=0) (actual time=0.018..0.018 rows=5 loops=1)

  • Index Cond: (coll_id = c.coll_id)
11.          

CTE access

12. 0.005 5.823 ↑ 944.6 5 1

Nested Loop (cost=2,406.70..3,364.99 rows=4,723 width=8) (actual time=5.778..5.823 rows=5 loops=1)

13. 0.002 5.719 ↓ 1.5 3 1

Unique (cost=2,402.08..2,402.09 rows=2 width=8) (actual time=5.717..5.719 rows=3 loops=1)

14. 0.013 5.717 ↓ 1.5 3 1

Sort (cost=2,402.08..2,402.08 rows=2 width=8) (actual time=5.717..5.717 rows=3 loops=1)

  • Sort Key: g.group_user_id
  • Sort Method: quicksort Memory: 25kB
15. 0.004 5.704 ↓ 1.5 3 1

Nested Loop (cost=0.84..2,402.07 rows=2 width=8) (actual time=0.089..5.704 rows=3 loops=1)

16. 0.019 0.019 ↑ 1.0 1 1

Index Scan using idx_user_main2 on r_user_main u (cost=0.42..4.44 rows=1 width=8) (actual time=0.018..0.019 rows=1 loops=1)

  • Index Cond: (((user_name)::text = 'psarando'::text) AND ((zone_name)::text = 'iplant'::text))
17. 5.681 5.681 ↓ 1.5 3 1

Index Only Scan using idx_user_group1 on r_user_group g (cost=0.42..2,397.61 rows=2 width=16) (actual time=0.069..5.681 rows=3 loops=1)

  • Index Cond: (user_id = u.user_id)
  • Heap Fetches: 1
18. 0.006 0.099 ↑ 90.0 2 3

Nested Loop (cost=4.62..479.65 rows=180 width=16) (actual time=0.025..0.033 rows=2 loops=3)

19. 0.011 0.018 ↑ 36.0 5 3

HashAggregate (cost=4.05..5.85 rows=180 width=8) (actual time=0.004..0.006 rows=5 loops=3)

20. 0.007 0.007 ↑ 36.0 5 1

CTE Scan on objs objs_1 (cost=0.00..3.60 rows=180 width=8) (actual time=0.001..0.007 rows=5 loops=1)

21. 0.075 0.075 ↓ 0.0 0 15

Index Only Scan using idx_objt_access1 on r_objt_access (cost=0.57..2.62 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=15)

  • Index Cond: ((object_id = objs_1.object_id) AND (user_id = g.group_user_id))
  • Heap Fetches: 5
22. 0.141 0.141 ↑ 36.0 5 1

CTE Scan on objs (cost=0.00..3.60 rows=180 width=40) (actual time=0.139..0.141 rows=5 loops=1)

23. 0.014 5.848 ↑ 40.0 5 1

Hash (cost=108.27..108.27 rows=200 width=8) (actual time=5.848..5.848 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
24. 0.008 5.834 ↑ 40.0 5 1

HashAggregate (cost=106.27..108.27 rows=200 width=8) (actual time=5.831..5.834 rows=5 loops=1)

25. 5.826 5.826 ↑ 944.6 5 1

CTE Scan on access (cost=0.00..94.46 rows=4,723 width=8) (actual time=5.779..5.826 rows=5 loops=1)

Total runtime : 6.211 ms