explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u8F

Settings
# exclusive inclusive rows x rows loops node
1. 0.030 1,591.034 ↓ 2.0 2 1

Sort (cost=18,468.01..18,468.02 rows=1 width=858) (actual time=1,591.034..1,591.034 rows=2 loops=1)

  • Output: document_files.id, document_files.anchor, document_files.time_created, document_files.created_by, document_files.time_deleted, document_files.parent_id, document_files.name, document_file_versions.id, document_file_versions.uploaded_by, document_file_versions.file_size_bytes, document_file_versions.version, document_file_versions.locked_by, document_file_versions.message, document_file_versions.filename, document_file_versions.replacement_count, document_file_versions.checkout_append, document_file_versions.deleted, document_file_version_drafts.id, document_file_version_drafts.created_by, document_file_version_drafts.time_created, document_file_version_drafts.time_updated, ((max(ARRAY[document_file_version_user_actions.id, document_file_version_user_actions.created_by]) FILTER (WHERE (document_file_version_user_actions.action = ANY ('{create,replace,rename,delete,update}'::text[]))))[2]), (bool_or((t1.is_direct_right AND t1.can_view))), (bool_or((t1.is_direct_right AND t1.can_edit))), (bool_or((t1.is_direct_right AND t1.can_manage)))
  • Sort Key: document_files.anchor DESC, document_files.name, document_file_versions.filename, document_files.time_deleted DESC, document_files.id, document_file_versions.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=245,849
2. 0.019 1,591.004 ↓ 2.0 2 1

Nested Loop Left Join (cost=19.29..18,468.00 rows=1 width=858) (actual time=1,223.130..1,591.004 rows=2 loops=1)

  • Output: document_files.id, document_files.anchor, document_files.time_created, document_files.created_by, document_files.time_deleted, document_files.parent_id, document_files.name, document_file_versions.id, document_file_versions.uploaded_by, document_file_versions.file_size_bytes, document_file_versions.version, document_file_versions.locked_by, document_file_versions.message, document_file_versions.filename, document_file_versions.replacement_count, document_file_versions.checkout_append, document_file_versions.deleted, document_file_version_drafts.id, document_file_version_drafts.created_by, document_file_version_drafts.time_created, document_file_version_drafts.time_updated, ((max(ARRAY[document_file_version_user_actions.id, document_file_version_user_actions.created_by]) FILTER (WHERE (document_file_version_user_actions.action = ANY ('{create,replace,rename,delete,update}'::text[]))))[2]), (bool_or((t1.is_direct_right AND t1.can_view))), (bool_or((t1.is_direct_right AND t1.can_edit))), (bool_or((t1.is_direct_right AND t1.can_manage)))
  • Join Filter: (document_file_version_drafts.document_file_version_id = document_file_versions.id)
  • Rows Removed by Join Filter: 74
  • Buffers: shared hit=245,849
3. 0.013 1,590.709 ↓ 2.0 2 1

Nested Loop (cost=19.29..18,442.86 rows=1 width=834) (actual time=1,223.002..1,590.709 rows=2 loops=1)

  • Output: document_files.id, document_files.anchor, document_files.time_created, document_files.created_by, document_files.time_deleted, document_files.parent_id, document_files.name, (bool_or((t1.is_direct_right AND t1.can_view))), (bool_or((t1.is_direct_right AND t1.can_edit))), (bool_or((t1.is_direct_right AND t1.can_manage))), document_file_versions.id, document_file_versions.uploaded_by, document_file_versions.file_size_bytes, document_file_versions.version, document_file_versions.locked_by, document_file_versions.message, document_file_versions.filename, document_file_versions.replacement_count, document_file_versions.checkout_append, document_file_versions.deleted, ((max(ARRAY[document_file_version_user_actions.id, document_file_version_user_actions.created_by]) FILTER (WHERE (document_file_version_user_actions.action = ANY ('{create,replace,rename,delete,update}'::text[]))))[2])
  • Inner Unique: true
  • Join Filter: (document_files.id = (t1.folder_path_ids[2]))
  • Rows Removed by Join Filter: 17
  • Buffers: shared hit=245,811
4. 0.047 1,590.426 ↓ 10.0 10 1

Nested Loop Left Join (cost=3.50..18,426.94 rows=1 width=831) (actual time=478.335..1,590.426 rows=10 loops=1)

  • Output: document_files.id, document_files.anchor, document_files.time_created, document_files.created_by, document_files.time_deleted, document_files.parent_id, document_files.name, document_file_versions.id, document_file_versions.uploaded_by, document_file_versions.file_size_bytes, document_file_versions.version, document_file_versions.locked_by, document_file_versions.message, document_file_versions.filename, document_file_versions.replacement_count, document_file_versions.checkout_append, document_file_versions.deleted, ((max(ARRAY[document_file_version_user_actions.id, document_file_version_user_actions.created_by]) FILTER (WHERE (document_file_version_user_actions.action = ANY ('{create,replace,rename,delete,update}'::text[]))))[2])
  • Buffers: shared hit=245,801
5. 0.049 0.049 ↓ 10.0 10 1

Index Scan using document_files_parent_id_index on public.document_files (cost=0.42..4.16 rows=1 width=51) (actual time=0.019..0.049 rows=10 loops=1)

  • Output: document_files.id, document_files.anchor, document_files.time_created, document_files.created_by, document_files.time_deleted, document_files.parent_id, document_files.name
  • Index Cond: (document_files.parent_id = 112,198)
  • Filter: (((document_files.time_deleted IS NULL) OR (document_files.time_deleted >= '2020-10-13 08:32:43.854319+00'::timestamp with time zone)) AND (document_files.organization_id = 2))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=14
6. 125.336 1,590.330 ↓ 0.0 0 10

Hash Right Join (cost=3.08..18,422.77 rows=1 width=784) (actual time=158.476..159.033 rows=0 loops=10)

  • Output: document_file_versions.id, document_file_versions.uploaded_by, document_file_versions.file_size_bytes, document_file_versions.version, document_file_versions.locked_by, document_file_versions.message, document_file_versions.filename, document_file_versions.replacement_count, document_file_versions.checkout_append, document_file_versions.deleted, document_file_versions.document_file_id, ((max(ARRAY[document_file_version_user_actions.id, document_file_version_user_actions.created_by]) FILTER (WHERE (document_file_version_user_actions.action = ANY ('{create,replace,rename,delete,update}'::text[]))))[2])
  • Inner Unique: true
  • Hash Cond: (document_file_version_user_actions.document_file_version_id = document_file_versions.id)
  • Buffers: shared hit=245,787
7. 1,074.560 1,464.884 ↓ 1.2 273,788 4

GroupAggregate (cost=0.42..15,545.68 rows=227,677 width=8) (actual time=0.023..366.221 rows=273,788 loops=4)

  • Output: document_file_version_user_actions.document_file_version_id, (max(ARRAY[document_file_version_user_actions.id, document_file_version_user_actions.created_by]) FILTER (WHERE (document_file_version_user_actions.action = ANY ('{create,replace,rename,delete,update}'::text[]))))[2]
  • Group Key: document_file_version_user_actions.document_file_version_id
  • Buffers: shared hit=245,748
8. 390.324 390.324 ↑ 1.1 300,600 4

Index Scan using document_file_version_user_actions_document_file_version_id_ind on public.document_file_version_user_actions (cost=0.42..9,617.52 rows=324,568 width=19) (actual time=0.011..97.581 rows=300,600 loops=4)

  • Output: document_file_version_user_actions.id, document_file_version_user_actions.document_file_version_id, document_file_version_user_actions.created_by, document_file_version_user_actions.time_created, document_file_version_user_actions.action, document_file_version_user_actions.value_changes, document_file_version_user_actions.ip_address, document_file_version_user_actions.user_agent
  • Buffers: shared hit=245,748
9. 0.020 0.110 ↓ 0.0 0 10

Hash (cost=2.64..2.64 rows=1 width=780) (actual time=0.011..0.011 rows=0 loops=10)

  • Output: document_file_versions.id, document_file_versions.uploaded_by, document_file_versions.file_size_bytes, document_file_versions.version, document_file_versions.locked_by, document_file_versions.message, document_file_versions.filename, document_file_versions.replacement_count, document_file_versions.checkout_append, document_file_versions.deleted, document_file_versions.document_file_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=39
10. 0.090 0.090 ↓ 0.0 0 10

Index Scan using document_file_versions_document_file_id_index on public.document_file_versions (cost=0.42..2.64 rows=1 width=780) (actual time=0.007..0.009 rows=0 loops=10)

  • Output: document_file_versions.id, document_file_versions.uploaded_by, document_file_versions.file_size_bytes, document_file_versions.version, document_file_versions.locked_by, document_file_versions.message, document_file_versions.filename, document_file_versions.replacement_count, document_file_versions.checkout_append, document_file_versions.deleted, document_file_versions.document_file_id
  • Index Cond: (document_file_versions.document_file_id = document_files.id)
  • Filter: (document_file_versions.latest_version IS TRUE)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=39
11. 0.030 0.270 ↑ 1.5 2 10

GroupAggregate (cost=15.79..15.86 rows=3 width=7) (actual time=0.026..0.027 rows=2 loops=10)

  • Output: bool_or((t1.is_direct_right AND t1.can_view)), bool_or((t1.is_direct_right AND t1.can_edit)), bool_or((t1.is_direct_right AND t1.can_manage)), (t1.folder_path_ids[2])
  • Group Key: (t1.folder_path_ids[2])
  • Buffers: shared hit=10
12. 0.112 0.240 ↑ 1.5 2 10

Sort (cost=15.79..15.80 rows=3 width=8) (actual time=0.024..0.024 rows=2 loops=10)

  • Output: (t1.folder_path_ids[2]), t1.is_direct_right, t1.can_view, t1.can_edit, t1.can_manage
  • Sort Key: (t1.folder_path_ids[2])
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=10
13. 0.004 0.128 ↑ 1.5 2 1

Subquery Scan on t1 (cost=15.71..15.77 rows=3 width=8) (actual time=0.125..0.128 rows=2 loops=1)

  • Output: t1.folder_path_ids[2], t1.is_direct_right, t1.can_view, t1.can_edit, t1.can_manage
  • Buffers: shared hit=10
14. 0.013 0.124 ↑ 1.5 2 1

HashAggregate (cost=15.71..15.74 rows=3 width=72) (actual time=0.122..0.124 rows=2 loops=1)

  • Output: document_rights.id, document_rights.team_id, document_rights.user_id, document_rights.folder_path_ids, document_rights.can_view, document_rights.can_edit, document_rights.can_manage, document_rights.time_created, document_rights.created_by, document_rights.time_updated, document_rights.updated_by, ((array_length(document_rights.folder_path_ids, 1) = 2))
  • Group Key: document_rights.id, document_rights.team_id, document_rights.user_id, document_rights.folder_path_ids, document_rights.can_view, document_rights.can_edit, document_rights.can_manage, document_rights.time_created, document_rights.created_by, document_rights.time_updated, document_rights.updated_by, ((array_length(document_rights.folder_path_ids, 1) = 2))
  • Buffers: shared hit=10
15. 0.000 0.111 ↑ 1.5 2 1

Append (cost=13.14..15.62 rows=3 width=72) (actual time=0.102..0.111 rows=2 loops=1)

  • Buffers: shared hit=10
16. 0.004 0.104 ↑ 1.0 2 1

Unique (cost=13.14..13.20 rows=2 width=72) (actual time=0.102..0.104 rows=2 loops=1)

  • Output: document_rights.id, document_rights.team_id, document_rights.user_id, document_rights.folder_path_ids, document_rights.can_view, document_rights.can_edit, document_rights.can_manage, document_rights.time_created, document_rights.created_by, document_rights.time_updated, document_rights.updated_by, ((array_length(document_rights.folder_path_ids, 1) = 2))
  • Buffers: shared hit=9
17. 0.023 0.100 ↑ 1.0 2 1

Sort (cost=13.14..13.14 rows=2 width=72) (actual time=0.100..0.100 rows=2 loops=1)

  • Output: document_rights.id, document_rights.team_id, document_rights.user_id, document_rights.folder_path_ids, document_rights.can_view, document_rights.can_edit, document_rights.can_manage, document_rights.time_created, document_rights.created_by, document_rights.time_updated, document_rights.updated_by, ((array_length(document_rights.folder_path_ids, 1) = 2))
  • Sort Key: document_rights.id, document_rights.team_id, document_rights.user_id, document_rights.folder_path_ids, document_rights.can_view, document_rights.can_edit, document_rights.can_manage, document_rights.time_created, document_rights.created_by, document_rights.time_updated, document_rights.updated_by, ((array_length(document_rights.folder_path_ids, 1) = 2))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=9
18. 0.001 0.077 ↑ 1.0 2 1

Append (cost=4.42..13.13 rows=2 width=72) (actual time=0.045..0.077 rows=2 loops=1)

  • Buffers: shared hit=9
19. 0.017 0.052 ↓ 2.0 2 1

Bitmap Heap Scan on public.document_rights (cost=4.42..7.75 rows=1 width=68) (actual time=0.044..0.052 rows=2 loops=1)

  • Output: document_rights.id, document_rights.team_id, document_rights.user_id, document_rights.folder_path_ids, document_rights.can_view, document_rights.can_edit, document_rights.can_manage, document_rights.time_created, document_rights.created_by, document_rights.time_updated, document_rights.updated_by, (array_length(document_rights.folder_path_ids, 1) = 2)
  • Recheck Cond: ('{112198}'::integer[] <@ document_rights.folder_path_ids)
  • Filter: ((document_rights.can_view OR document_rights.can_manage) AND (document_rights.team_id = 31))
  • Rows Removed by Filter: 5
  • Heap Blocks: exact=4
  • Buffers: shared hit=8
20. 0.035 0.035 ↓ 2.7 8 1

Bitmap Index Scan on idx_folder_path_ids_gin (cost=0.00..4.42 rows=3 width=0) (actual time=0.034..0.035 rows=8 loops=1)

  • Index Cond: ('{112198}'::integer[] <@ document_rights.folder_path_ids)
  • Buffers: shared hit=4
21. 0.000 0.024 ↓ 0.0 0 1

Nested Loop (cost=0.54..5.36 rows=1 width=68) (actual time=0.024..0.024 rows=0 loops=1)

  • Output: document_rights_1.id, document_rights_1.team_id, document_rights_1.user_id, document_rights_1.folder_path_ids, document_rights_1.can_view, document_rights_1.can_edit, document_rights_1.can_manage, document_rights_1.time_created, document_rights_1.created_by, document_rights_1.time_updated, document_rights_1.updated_by, (array_length(document_rights_1.folder_path_ids, 1) = 2)
  • Buffers: shared hit=1
22. 0.001 0.024 ↓ 0.0 0 1

Nested Loop (cost=0.15..3.87 rows=1 width=8) (actual time=0.024..0.024 rows=0 loops=1)

  • Output: teams.id, team_users.team_id
  • Inner Unique: true
  • Buffers: shared hit=1
23. 0.023 0.023 ↓ 0.0 0 1

Seq Scan on public.team_users (cost=0.00..1.50 rows=1 width=4) (actual time=0.023..0.023 rows=0 loops=1)

  • Output: team_users.id, team_users.team_id, team_users.user_id, team_users.is_manager, team_users.time_created, team_users.created_by, team_users.time_updated
  • Filter: (team_users.user_id = 5)
  • Rows Removed by Filter: 40
  • Buffers: shared hit=1
24. 0.000 0.000 ↓ 0.0 0

Index Only Scan using teams_pkey on public.teams (cost=0.15..2.37 rows=1 width=4) (never executed)

  • Output: teams.id
  • Index Cond: (teams.id = team_users.team_id)
  • Heap Fetches: 0
25. 0.000 0.000 ↓ 0.0 0

Index Scan using document_rights_team_id_md5_idx on public.document_rights document_rights_1 (cost=0.39..1.47 rows=1 width=67) (never executed)

  • Output: document_rights_1.id, document_rights_1.team_id, document_rights_1.user_id, document_rights_1.folder_path_ids, document_rights_1.can_view, document_rights_1.can_edit, document_rights_1.can_manage, document_rights_1.time_created, document_rights_1.created_by, document_rights_1.time_updated, document_rights_1.updated_by
  • Index Cond: (document_rights_1.team_id = teams.id)
  • Filter: ((document_rights_1.can_view OR document_rights_1.can_manage) AND ('{112198}'::integer[] <@ document_rights_1.folder_path_ids))
26. 0.001 0.007 ↓ 0.0 0 1

Bitmap Heap Scan on public.document_rights document_rights_2 (cost=1.25..2.37 rows=1 width=68) (actual time=0.007..0.007 rows=0 loops=1)

  • Output: document_rights_2.id, document_rights_2.team_id, document_rights_2.user_id, document_rights_2.folder_path_ids, document_rights_2.can_view, document_rights_2.can_edit, document_rights_2.can_manage, document_rights_2.time_created, document_rights_2.created_by, document_rights_2.time_updated, document_rights_2.updated_by, (array_length(document_rights_2.folder_path_ids, 1) = 2)
  • Recheck Cond: (document_rights_2.user_id = 5)
  • Filter: ((document_rights_2.can_view OR document_rights_2.can_manage) AND ('{112198}'::integer[] <@ document_rights_2.folder_path_ids))
  • Buffers: shared hit=1
27. 0.006 0.006 ↓ 0.0 0 1

Bitmap Index Scan on document_rights_user_id_md5_idx (cost=0.00..1.25 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (document_rights_2.user_id = 5)
  • Buffers: shared hit=1
28. 0.276 0.276 ↑ 1.1 37 2

Seq Scan on public.document_file_version_drafts (cost=0.00..24.65 rows=39 width=28) (actual time=0.014..0.138 rows=37 loops=2)

  • Output: document_file_version_drafts.id, document_file_version_drafts.document_file_version_id, document_file_version_drafts.file_size_bytes, document_file_version_drafts.file_content_type, document_file_version_drafts.created_by, document_file_version_drafts.participant_ids, document_file_version_drafts.time_created, document_file_version_drafts.time_updated, document_file_version_drafts.filename, document_file_version_drafts.deleted, document_file_version_drafts.time_deleted, document_file_version_drafts.uuid
  • Filter: (document_file_version_drafts.deleted IS FALSE)
  • Rows Removed by Filter: 532
  • Buffers: shared hit=38
Planning time : 2.887 ms
Execution time : 1,591.575 ms