explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fWjZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.044 0.869 ↓ 2.0 2 1

Sort (cost=18,439.55..18,439.56 rows=1 width=858) (actual time=0.868..0.869 rows=2 loops=1)

  • 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
2. 0.003 0.825 ↓ 2.0 2 1

Merge Left Join (cost=48.30..18,439.54 rows=1 width=858) (actual time=0.824..0.825 rows=2 loops=1)

  • Merge Cond: (document_file_versions.id = document_file_version_user_actions.document_file_version_id)
3. 0.083 0.822 ↓ 2.0 2 1

Sort (cost=47.88..47.89 rows=1 width=854) (actual time=0.822..0.822 rows=2 loops=1)

  • Sort Key: document_file_versions.id
  • Sort Method: quicksort Memory: 25kB
4. 0.013 0.739 ↓ 2.0 2 1

Nested Loop Left Join (cost=16.64..47.87 rows=1 width=854) (actual time=0.481..0.739 rows=2 loops=1)

  • Join Filter: (document_file_version_drafts.document_file_version_id = document_file_versions.id)
  • Rows Removed by Join Filter: 74
5. 0.010 0.272 ↓ 2.0 2 1

Nested Loop (cost=16.64..22.73 rows=1 width=830) (actual time=0.215..0.272 rows=2 loops=1)

  • Join Filter: (document_files.id = (t1.folder_path_ids[2]))
  • Rows Removed by Join Filter: 17
6. 0.015 0.112 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.84..6.81 rows=1 width=827) (actual time=0.029..0.112 rows=10 loops=1)

7. 0.037 0.037 ↓ 10.0 10 1

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

  • Index Cond: (parent_id = 112,198)
  • Filter: (((time_deleted IS NULL) OR (time_deleted >= '2020-10-13 08:30:18.075623+00'::timestamp with time zone)) AND (organization_id = 2))
  • Rows Removed by Filter: 2
8. 0.060 0.060 ↓ 0.0 0 10

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

  • Index Cond: (document_file_id = document_files.id)
  • Filter: (latest_version IS TRUE)
  • Rows Removed by Filter: 1
9. 0.020 0.150 ↑ 1.5 2 10

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

  • Group Key: (t1.folder_path_ids[2])
10. 0.004 0.130 ↑ 1.5 2 10

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

  • Sort Key: (t1.folder_path_ids[2])
  • Sort Method: quicksort Memory: 25kB
11. 0.018 0.126 ↑ 1.5 2 1

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

12. 0.009 0.108 ↑ 1.5 2 1

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

  • 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))
13. 0.002 0.099 ↑ 1.5 2 1

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

14. 0.003 0.093 ↑ 1.0 2 1

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

15. 0.017 0.090 ↑ 1.0 2 1

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

  • 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
16. 0.000 0.073 ↑ 1.0 2 1

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

17. 0.015 0.060 ↓ 2.0 2 1

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

  • Recheck Cond: ('{112198}'::integer[] <@ folder_path_ids)
  • Filter: ((can_view OR can_manage) AND (team_id = 31))
  • Rows Removed by Filter: 5
  • Heap Blocks: exact=4
18. 0.045 0.045 ↓ 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.045..0.045 rows=8 loops=1)

  • Index Cond: ('{112198}'::integer[] <@ folder_path_ids)
19. 0.000 0.013 ↓ 0.0 0 1

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

20. 0.001 0.013 ↓ 0.0 0 1

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

21. 0.012 0.012 ↓ 0.0 0 1

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

  • Filter: (user_id = 5)
  • Rows Removed by Filter: 40
22. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Index Cond: (team_id = teams.id)
  • Filter: ((can_view OR can_manage) AND ('{112198}'::integer[] <@ folder_path_ids))
24. 0.000 0.004 ↓ 0.0 0 1

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

  • Recheck Cond: (user_id = 5)
  • Filter: ((can_view OR can_manage) AND ('{112198}'::integer[] <@ folder_path_ids))
25. 0.004 0.004 ↓ 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.004..0.004 rows=0 loops=1)

  • Index Cond: (user_id = 5)
26. 0.454 0.454 ↑ 1.1 37 2

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

  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 532
27. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=0.42..15,545.68 rows=227,677 width=8) (never executed)

  • Group Key: document_file_version_user_actions.document_file_version_id
28. 0.000 0.000 ↓ 0.0 0

Index Scan using document_file_version_user_actions_document_file_version_id_ind on document_file_version_user_actions (cost=0.42..9,617.52 rows=324,568 width=19) (never executed)

Planning time : 4.740 ms
Execution time : 1.395 ms