explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HnEG

Settings
# exclusive inclusive rows x rows loops node
1. 0.019 74,552.150 ↑ 9.5 6 1

Sort (cost=75,077,885.04..75,077,885.18 rows=57 width=251) (actual time=74,552.150..74,552.150 rows=6 loops=1)

  • Sort Key: (CASE WHEN ((cr_items.content_type)::text = 'content_folder'::text) THEN cr_folders.label WHEN ((cr_items.content_type)::text = 'content_extlink'::text) THEN cr_extlinks.label ELSE cr_items.name END)
  • Sort Method: quicksort Memory: 26kB
2. 2,542.941 74,552.131 ↑ 9.5 6 1

Hash Join (cost=71,009,523.05..75,077,883.38 rows=57 width=251) (actual time=57,700.312..74,552.131 rows=6 loops=1)

  • Hash Cond: (c.object_id = cr_items.item_id)
3. 52,148.794 72,006.545 ↓ 34.6 50,084,493 1

HashAggregate (cost=71,002,487.65..71,016,968.05 rows=1,448,040 width=4) (actual time=53,789.437..72,006.545 rows=50,084,493 loops=1)

4. 5,302.666 19,857.751 ↑ 93.3 58,456,704 1

Nested Loop (cost=0.00..57,363,902.37 rows=5,455,434,112 width=4) (actual time=0.050..19,857.751 rows=58,456,704 loops=1)

5. 153.040 861.279 ↓ 2.9 652,086 1

Nested Loop (cost=0.00..51,010.26 rows=221,903 width=4) (actual time=0.040..861.279 rows=652,086 loops=1)

  • Join Filter: ((p.privilege)::text = (pdm.privilege)::text)
6. 0.011 0.011 ↑ 1.5 2 1

Index Scan using acs_priv_desc_map_idx on acs_privilege_descendant_map pdm (cost=0.00..3.33 rows=3 width=11) (actual time=0.009..0.011 rows=2 loops=1)

  • Index Cond: ((descendant)::text = 'read'::text)
7. 192.650 708.228 ↓ 1.2 970,590 2

Nested Loop (cost=0.00..7,164.30 rows=787,041 width=12) (actual time=0.021..354.114 rows=970,590 loops=2)

8. 0.108 0.108 ↓ 2.0 95 2

Index Scan using party_member_member_idx on party_approved_member_map pamm (cost=0.00..28.34 rows=47 width=4) (actual time=0.009..0.054 rows=95 loops=2)

  • Index Cond: (member_id = 133,449,130)
9. 515.470 515.470 ↓ 61.5 10,217 190

Index Scan using acs_permissions_grantee_idx on acs_permissions p (cost=0.00..149.75 rows=166 width=16) (actual time=0.003..2.713 rows=10,217 loops=190)

  • Index Cond: (p.grantee_id = pamm.party_id)
10. 13,693.806 13,693.806 ↑ 93.1 90 652,086

Index Scan using acs_obj_ctx_idx_ancestor_idx on acs_object_context_index c (cost=0.00..153.55 rows=8,378 width=8) (actual time=0.002..0.021 rows=90 loops=652,086)

  • Index Cond: (c.ancestor_id = p.object_id)
11. 0.002 2.369 ↑ 111.2 6 1

Hash (cost=7,027.06..7,027.06 rows=667 width=255) (actual time=2.369..2.369 rows=6 loops=1)

12. 0.012 2.367 ↑ 111.2 6 1

Hash Left Join (cost=237.58..7,027.06 rows=667 width=255) (actual time=2.319..2.367 rows=6 loops=1)

  • Hash Cond: ((cr_revisions.mime_type)::text = (cr_mime_types.mime_type)::text)
13. 0.006 2.243 ↑ 111.2 6 1

Nested Loop Left Join (cost=228.80..7,013.68 rows=667 width=235) (actual time=2.198..2.243 rows=6 loops=1)

14. 0.008 2.237 ↑ 111.2 6 1

Hash Left Join (cost=228.80..4,956.79 rows=667 width=194) (actual time=2.196..2.237 rows=6 loops=1)

  • Hash Cond: (cr_items.item_id = cr_extlinks.extlink_id)
15. 0.010 0.078 ↑ 111.2 6 1

Nested Loop Left Join (cost=0.00..4,721.31 rows=667 width=121) (actual time=0.038..0.078 rows=6 loops=1)

16. 0.010 0.050 ↑ 111.2 6 1

Nested Loop (cost=0.00..3,164.26 rows=667 width=86) (actual time=0.030..0.050 rows=6 loops=1)

17. 0.016 0.016 ↑ 111.2 6 1

Index Scan using cr_items_by_parent_id on cr_items (cost=0.00..503.31 rows=667 width=74) (actual time=0.015..0.016 rows=6 loops=1)

  • Index Cond: (parent_id = 134,750,901)
18. 0.024 0.024 ↑ 1.0 1 6

Index Scan using acs_objects_pk on acs_objects (cost=0.00..3.98 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=6)

  • Index Cond: (acs_objects.object_id = cr_items.item_id)
19. 0.018 0.018 ↑ 1.0 1 6

Index Scan using cr_folders_pk on cr_folders (cost=0.00..2.32 rows=1 width=39) (actual time=0.003..0.003 rows=1 loops=6)

  • Index Cond: (cr_items.item_id = cr_folders.folder_id)
20. 1.127 2.151 ↑ 1.0 5,680 1

Hash (cost=157.80..157.80 rows=5,680 width=77) (actual time=2.151..2.151 rows=5,680 loops=1)

21. 1.024 1.024 ↑ 1.0 5,680 1

Seq Scan on cr_extlinks (cost=0.00..157.80 rows=5,680 width=77) (actual time=0.002..1.024 rows=5,680 loops=1)

22. 0.000 0.000 ↓ 0.0 0 6

Index Scan using cr_revisions_pk on cr_revisions (cost=0.00..3.07 rows=1 width=45) (actual time=0.000..0.000 rows=0 loops=6)

  • Index Cond: (cr_items.live_revision = cr_revisions.revision_id)
23. 0.066 0.112 ↑ 1.0 257 1

Hash (cost=5.57..5.57 rows=257 width=43) (actual time=0.112..0.112 rows=257 loops=1)

24. 0.046 0.046 ↑ 1.0 257 1

Seq Scan on cr_mime_types (cost=0.00..5.57 rows=257 width=43) (actual time=0.002..0.046 rows=257 loops=1)

25.          

SubPlan (for Hash Join)

26. 0.144 0.276 ↑ 1.0 1 6

Aggregate (cost=70,987.92..70,987.93 rows=1 width=0) (actual time=0.046..0.046 rows=1 loops=6)

27. 0.132 0.132 ↓ 0.0 0 6

Index Scan using cr_items_tree_sortkey_un on cr_items ci2 (cost=0.25..70,785.77 rows=80,856 width=0) (actual time=0.022..0.022 rows=0 loops=6)

  • Index Cond: ((tree_sortkey >= $0) AND (tree_sortkey <= tree_right($0)))
  • Filter: ((content_type)::text <> 'content_folder'::text)
Total runtime : 74,745.642 ms