explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sWIi

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 30,265.821 ↑ 111.2 6 1

Sort (cost=209,033,000.82..209,033,002.49 rows=667 width=251) (actual time=30,265.821..30,265.821 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. 342.147 30,265.795 ↑ 111.2 6 1

Hash Semi Join (cost=131,234,817.57..209,032,969.53 rows=667 width=251) (actual time=29,953.260..30,265.795 rows=6 loops=1)

  • Hash Cond: (cr_items.item_id = c.object_id)
3. 0.012 1.677 ↑ 111.2 6 1

Hash Left Join (cost=237.58..7,027.07 rows=667 width=255) (actual time=1.617..1.677 rows=6 loops=1)

  • Hash Cond: (cr_items.item_id = cr_extlinks.extlink_id)
4. 0.004 0.160 ↑ 111.2 6 1

Nested Loop (cost=8.78..6,791.59 rows=667 width=182) (actual time=0.108..0.160 rows=6 loops=1)

5. 0.005 0.120 ↑ 111.2 6 1

Hash Left Join (cost=8.78..4,130.64 rows=667 width=170) (actual time=0.099..0.120 rows=6 loops=1)

  • Hash Cond: ((cr_revisions.mime_type)::text = (cr_mime_types.mime_type)::text)
6. 0.002 0.038 ↑ 111.2 6 1

Nested Loop Left Join (cost=0.00..4,117.25 rows=667 width=150) (actual time=0.018..0.038 rows=6 loops=1)

7. 0.007 0.018 ↑ 111.2 6 1

Nested Loop Left Join (cost=0.00..2,560.20 rows=667 width=115) (actual time=0.011..0.018 rows=6 loops=1)

8. 0.011 0.011 ↑ 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.010..0.011 rows=6 loops=1)

  • Index Cond: (parent_id = 134750901)
9. 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)
10. 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.002..0.003 rows=1 loops=6)

  • Index Cond: (cr_items.item_id = cr_folders.folder_id)
11. 0.042 0.077 ↑ 1.0 257 1

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

12. 0.035 0.035 ↑ 1.0 257 1

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

13. 0.036 0.036 ↑ 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.005..0.006 rows=1 loops=6)

  • Index Cond: (acs_objects.object_id = cr_items.item_id)
14. 0.774 1.505 ↑ 1.0 5,680 1

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

15. 0.731 0.731 ↑ 1.0 5,680 1

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

16. 10,007.511 29,921.773 ↑ 133.3 58,456,704 1

Hash (cost=3,377,177.49..3,377,177.49 rows=7,793,213,080 width=4) (actual time=29,921.773..29,921.773 rows=58,456,704 loops=1)

17. 5,365.069 19,914.262 ↑ 133.3 58,456,704 1

Nested Loop (cost=0.00..3,377,177.49 rows=7,793,213,080 width=4) (actual time=0.042..19,914.262 rows=58,456,704 loops=1)

18. 153.454 855.387 ↓ 2.1 652,086 1

Nested Loop (cost=0.00..46,286.72 rows=308,157 width=4) (actual time=0.033..855.387 rows=652,086 loops=1)

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

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

  • Index Cond: ((descendant)::text = 'read'::text)
20. 192.610 701.922 ↑ 1.7 970,590 2

Nested Loop (cost=0.00..2,658.15 rows=1,638,684 width=12) (actual time=0.022..350.961 rows=970,590 loops=2)

21. 0.112 0.112 ↑ 1.0 95 2

Index Scan using party_member_member_idx on party_approved_member_map pamm (cost=0.00..54.91 rows=95 width=4) (actual time=0.012..0.056 rows=95 loops=2)

  • Index Cond: (member_id = 133449130)
22. 509.200 509.200 ↓ 378.4 10,217 190

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

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

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

  • Index Cond: (c.ancestor_id = p.object_id)
24.          

SubPlan (for Hash Semi Join)

25. 0.114 0.198 ↑ 1.0 1 6

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

26. 0.084 0.084 ↓ 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.014..0.014 rows=0 loops=6)

  • Index Cond: ((tree_sortkey >= $0) AND (tree_sortkey <= tree_right($0)))
  • Filter: ((content_type)::text <> 'content_folder'::text)