explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lEJJ : no offset

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 4,353.286 ↑ 1.0 50 1

Limit (cost=108,483.59..2,908,783.92 rows=50 width=907) (actual time=4,352.391..4,353.286 rows=50 loops=1)

  • Index Cond: (cr_items.live_revision = revision_id)
2. 0.082 4,353.278 ↑ 6.3 50 1

Result (cost=108,483.59..17,750,375.68 rows=315 width=907) (actual time=4,352.390..4,353.278 rows=50 loops=1)

3. 40.780 4,352.346 ↑ 6.3 50 1

Sort (cost=108,483.59..108,484.38 rows=315 width=936) (actual time=4,352.339..4,352.346 rows=50 loops=1)

  • Sort Key: (CASE WHEN ((cr_items.content_type)::text = 'content_folder'::text) THEN 0 ELSE 1 END), (btrsort((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)::text))
  • Sort Method: top-N heapsort Memory: 92kB
4. 3,866.650 4,311.566 ↓ 72.7 22,911 1

Nested Loop Semi Join (cost=641.21..108,473.13 rows=315 width=936) (actual time=5.594..4,311.566 rows=22,911 loops=1)

  • Join Filter: (cr_items.item_id = c.object_id)
5. 10.559 330.361 ↓ 8.5 22,911 1

Hash Anti Join (cost=528.99..29,710.65 rows=2,694 width=1,249) (actual time=3.877..330.361 rows=22,911 loops=1)

  • Hash Cond: (cr_items.item_id = fz.file_id)
6. 10.324 319.790 ↓ 8.5 22,911 1

Nested Loop (cost=473.09..29,620.74 rows=2,694 width=1,249) (actual time=3.864..319.790 rows=22,911 loops=1)

7. 7.305 217.822 ↓ 8.5 22,911 1

Hash Left Join (cost=472.53..20,011.49 rows=2,694 width=1,237) (actual time=3.851..217.822 rows=22,911 loops=1)

  • Hash Cond: ((cr_revisions.mime_type)::text = (cr_mime_types.mime_type)::text)
8. 17.410 206.772 ↓ 8.5 22,911 1

Nested Loop Left Join (cost=38.11..19,543.06 rows=2,694 width=1,198) (actual time=0.083..206.772 rows=22,911 loops=1)

9. 25.231 189.362 ↓ 8.5 22,911 1

Nested Loop Left Join (cost=37.67..10,374.02 rows=2,694 width=1,160) (actual time=0.081..189.362 rows=22,911 loops=1)

10. 15.545 72.487 ↓ 8.5 22,911 1

Hash Left Join (cost=37.25..4,441.16 rows=2,694 width=1,139) (actual time=0.069..72.487 rows=22,911 loops=1)

  • Hash Cond: (cr_items.item_id = cr_extlinks.extlink_id)
11. 56.915 56.923 ↓ 8.5 22,911 1

Index Scan using cr_items_by_parent_id on cr_items (cost=35.94..4,405.83 rows=2,694 width=107) (actual time=0.044..56.923 rows=22,911 loops=1)

  • Index Cond: (parent_id = 58529700)
  • Filter: ((NOT deleted) AND ((content_type)::text <> 'd3c_page'::text) AND ((content_type)::text <> 'mdk_thumbnail_object'::text) AND (NOT (hashed SubPlan 2)))
12.          

SubPlan (for Index Scan)

13. 0.008 0.008 ↓ 0.0 0 1

Seq Scan on fs_incomplete_zips (cost=0.00..30.40 rows=2,040 width=4) (actual time=0.008..0.008 rows=0 loops=1)

14. 0.007 0.019 ↓ 2.1 29 1

Hash (cost=1.14..1.14 rows=14 width=1,036) (actual time=0.019..0.019 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
15. 0.012 0.012 ↓ 2.1 29 1

Seq Scan on cr_extlinks (cost=0.00..1.14 rows=14 width=1,036) (actual time=0.007..0.012 rows=29 loops=1)

16. 91.644 91.644 ↑ 1.0 1 22,911

Index Only Scan using cr_folders_unique_name on cr_folders (cost=0.42..2.20 rows=1 width=25) (actual time=0.004..0.004 rows=1 loops=22,911)

  • Index Cond: (folder_id = cr_items.item_id)
  • Heap Fetches: 22911
17. 0.000 0.000 ↓ 0.0 0 22,911

Index Scan using cr_revisions_revision_id_pk on cr_revisions (cost=0.43..3.40 rows=1 width=42) (actual time=0.000..0.000 rows=0 loops=22,911)

  • Index Cond: (cr_items.live_revision = revision_id)
18. 1.965 3.745 ↓ 1.0 12,475 1

Hash (cost=278.63..278.63 rows=12,463 width=63) (actual time=3.745..3.745 rows=12,475 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1303kB
19. 1.780 1.780 ↓ 1.0 12,475 1

Seq Scan on cr_mime_types (cost=0.00..278.63 rows=12,463 width=63) (actual time=0.006..1.780 rows=12,475 loops=1)

20. 91.644 91.644 ↑ 1.0 1 22,911

Index Scan using mdk_objects_object_id_pk on mdk_objects (cost=0.56..3.57 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=22,911)

  • Index Cond: (object_id = cr_items.item_id)
21. 0.000 0.006 ↓ 0.0 0 1

Hash (cost=30.40..30.40 rows=2,040 width=4) (actual time=0.006..0.006 rows=0 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 16kB
22. 1.965 3.745 ↓ 1.0 12,475 1

Hash (cost=278.63..278.63 rows=12,463 width=63) (actual time=3.745..3.745 rows=12,475 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1303kB
23. 1.780 1.780 ↓ 1.0 12,475 1

Seq Scan on cr_mime_types (cost=0.00..278.63 rows=12,463 width=63) (actual time=0.006..1.780 rows=12,475 loops=1)

24. 91.644 91.644 ↑ 1.0 1 22,911

Index Scan using mdk_objects_object_id_pk on mdk_objects (cost=0.56..3.57 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=22,911)

  • Index Cond: (object_id = cr_items.item_id)
25. 0.000 0.006 ↓ 0.0 0 1

Hash (cost=30.40..30.40 rows=2,040 width=4) (actual time=0.006..0.006 rows=0 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 16kB
26. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on fs_incomplete_zips fz (cost=0.00..30.40 rows=2,040 width=4) (actual time=0.006..0.006 rows=0 loops=1)

27. 22.578 114.555 ↑ 2.0 1 22,911

Hash Join (cost=112.22..142.18 rows=2 width=4) (actual time=0.005..0.005 rows=1 loops=22,911)

  • Hash Cond: (c.ancestor_id = p.object_id)
28. 91.644 91.644 ↑ 188.0 1 22,911

Index Only Scan using mdk_object_context_index_pk on mdk_object_context_index c (cost=0.57..29.33 rows=188 width=8) (actual time=0.004..0.004 rows=1 loops=22,911)

  • Index Cond: (object_id = mdk_objects.object_id)
  • Heap Fetches: 22911
29. 0.010 0.333 ↓ 1.6 67 1

Hash (cost=111.13..111.13 rows=42 width=4) (actual time=0.333..0.333 rows=67 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
30. 0.043 0.323 ↓ 1.6 67 1

Hash Join (cost=4.63..111.13 rows=42 width=4) (actual time=0.041..0.323 rows=67 loops=1)

  • Hash Cond: ((p.privilege)::text = (pdm.privilege)::text)
31. 0.028 0.261 ↓ 1.5 282 1

Nested Loop (cost=0.57..105.46 rows=191 width=14) (actual time=0.017..0.261 rows=282 loops=1)

32. 0.013 0.013 ↓ 2.0 20 1

Index Scan using party_member_member_idx on party_approved_member_map pamm (cost=0.29..9.12 rows=10 width=4) (actual time=0.009..0.013 rows=20 loops=1)

  • Index Cond: (member_id = 4516781)
33. 0.220 0.220 ↓ 1.2 14 20

Index Scan using mdk_permissions_grantee_idx on mdk_permissions p (cost=0.29..9.51 rows=12 width=18) (actual time=0.002..0.011 rows=14 loops=20)

  • Index Cond: (grantee_id = pamm.party_id)
34. 0.001 0.019 ↑ 1.0 3 1

Hash (cost=4.02..4.02 rows=3 width=12) (actual time=0.019..0.019 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 0.009 0.018 ↑ 1.0 3 1

Bitmap Heap Scan on mdk_privilege_descendant_map pdm (cost=1.80..4.02 rows=3 width=12) (actual time=0.013..0.018 rows=3 loops=1)

  • Recheck Cond: ((descendant)::text = 'read'::text)
  • Heap Blocks: exact=3
36. 0.009 0.009 ↑ 1.0 3 1

Bitmap Index Scan on acs_priv_desc_map_idx (cost=0.00..1.79 rows=3 width=0) (actual time=0.009..0.009 rows=3 loops=1)

  • Index Cond: ((descendant)::text = 'read'::text)
37.          

SubPlan (for Result)

38. 0.000 0.850 ↑ 1.0 1 50

Aggregate (cost=56,005.69..56,005.70 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=50)

39. 0.250 0.850 ↑ 21,333.0 1 50

Result (cost=0.81..55,952.35 rows=21,333 width=0) (actual time=0.014..0.017 rows=1 loops=50)

  • One-Time Filter: ((cr_items.content_type)::text <> 'mdk_thumbnail_object'::text)
40. 0.600 0.600 ↑ 21,333.0 1 50

Index Scan using cr_items_tree_sortkey_un on cr_items ci2 (cost=0.81..55,952.35 rows=21,333 width=0) (actual time=0.010..0.012 rows=1 loops=50)

  • Index Cond: ((tree_sortkey >= cr_items.tree_sortkey) AND (tree_sortkey <= tree_right(cr_items.tree_sortkey)))
  • Filter: ((NOT deleted) AND ((content_type)::text <> 'content_folder'::text) AND ((content_type)::text <> 'd3c_page'::text))
  • Rows Removed by Filter: 4
Planning time : 4.690 ms
Execution time : 4,353.528 ms