explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FzY3

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 1,485.924 ↓ 2.0 2 1

Unique (cost=83,124.11..83,124.13 rows=1 width=936) (actual time=1,485.923..1,485.924 rows=2 loops=1)

2. 0.084 1,485.922 ↓ 2.0 2 1

Sort (cost=83,124.11..83,124.11 rows=1 width=936) (actual time=1,485.922..1,485.922 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
3. 0.006 1,485.838 ↓ 2.0 2 1

Nested Loop Left Join (cost=77,495.42..83,124.10 rows=1 width=936) (actual time=1,485.832..1,485.838 rows=2 loops=1)

  • Join Filter: ((document_file_versions.deleted IS TRUE) AND (document_file_version_user_actions.document_file_version_id = document_file_versions.id) AND (document_file_version_user_actions.action = 'delete'::text))
4. 0.006 1,485.824 ↓ 2.0 2 1

Nested Loop Left Join (cost=77,495.28..83,123.92 rows=1 width=932) (actual time=1,485.819..1,485.824 rows=2 loops=1)

5. 33.739 1,485.794 ↓ 2.0 2 1

Hash Right Join (cost=77,495.14..83,123.76 rows=1 width=921) (actual time=1,485.791..1,485.794 rows=2 loops=1)

  • Hash Cond: (document_files_1.id = document_files.id)
  • Join Filter: (document_files.anchor IS FALSE)
6. 47.017 1,451.619 ↑ 1.1 272,323 1

Unique (cost=77,443.93..78,944.89 rows=300,193 width=31) (actual time=1,319.890..1,451.619 rows=272,323 loops=1)

7. 363.244 1,404.602 ↑ 1.1 276,611 1

Sort (cost=77,443.93..78,194.41 rows=300,193 width=31) (actual time=1,319.888..1,404.602 rows=276,611 loops=1)

  • Sort Key: document_files_1.id, document_file_version_user_actions.id DESC
  • Sort Method: external merge Disk: 11,376kB
8. 574.983 1,041.358 ↑ 1.1 276,611 1

Hash Left Join (cost=12,677.88..45,924.42 rows=300,193 width=31) (actual time=120.610..1,041.358 rows=276,611 loops=1)

  • Hash Cond: (document_file_versions_1.document_file_id = document_files_1.id)
9. 111.945 357.880 ↑ 1.1 276,611 1

Merge Left Join (cost=0.84..27,108.37 rows=300,193 width=31) (actual time=0.021..357.880 rows=276,611 loops=1)

  • Merge Cond: (document_file_version_user_actions.document_file_version_id = document_file_versions_1.id)
10. 138.252 138.252 ↑ 1.1 276,611 1

Index Scan using document_file_version_user_actions_document_file_version_id_ind on document_file_version_user_actions (cost=0.42..11,646.07 rows=300,193 width=27) (actual time=0.010..138.252 rows=276,611 loops=1)

  • Filter: (action = ANY ('{create,replace,rename,delete,update}'::text[]))
  • Rows Removed by Filter: 23,971
11. 107.683 107.683 ↑ 1.0 273,782 1

Index Scan using document_file_versions_pkey on document_file_versions document_file_versions_1 (cost=0.42..11,011.99 rows=279,158 width=8) (actual time=0.008..107.683 rows=273,782 loops=1)

12. 71.819 108.495 ↓ 1.0 320,689 1

Hash (cost=7,450.35..7,450.35 rows=318,535 width=4) (actual time=108.495..108.495 rows=320,689 loops=1)

  • Buckets: 131,072 Batches: 8 Memory Usage: 2,440kB
13. 36.676 36.676 ↓ 1.0 320,689 1

Index Only Scan using document_files_pkey on document_files document_files_1 (cost=0.42..7,450.35 rows=318,535 width=4) (actual time=0.164..36.676 rows=320,689 loops=1)

  • Heap Fetches: 9,880
14. 0.004 0.436 ↓ 2.0 2 1

Hash (cost=51.19..51.19 rows=1 width=898) (actual time=0.436..0.436 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.002 0.432 ↓ 2.0 2 1

Nested Loop Left Join (cost=17.21..51.19 rows=1 width=898) (actual time=0.316..0.432 rows=2 loops=1)

16. 0.002 0.410 ↓ 2.0 2 1

Nested Loop Left Join (cost=17.06..50.60 rows=1 width=887) (actual time=0.312..0.410 rows=2 loops=1)

17. 0.000 0.404 ↓ 2.0 2 1

Nested Loop Left Join (cost=16.92..50.44 rows=1 width=876) (actual time=0.307..0.404 rows=2 loops=1)

18. 0.002 0.398 ↓ 2.0 2 1

Nested Loop Left Join (cost=16.78..50.28 rows=1 width=865) (actual time=0.301..0.398 rows=2 loops=1)

19. 0.004 0.386 ↓ 2.0 2 1

Nested Loop Left Join (cost=16.64..47.88 rows=1 width=854) (actual time=0.293..0.386 rows=2 loops=1)

  • Join Filter: (document_file_version_drafts.document_file_version_id = document_file_versions.id)
  • Rows Removed by Join Filter: 74
20. 0.002 0.186 ↓ 2.0 2 1

Nested Loop Left Join (cost=16.64..22.74 rows=1 width=830) (actual time=0.179..0.186 rows=2 loops=1)

21. 0.002 0.174 ↓ 2.0 2 1

Nested Loop (cost=16.21..20.09 rows=1 width=54) (actual time=0.171..0.174 rows=2 loops=1)

  • Join Filter: (document_files.id = (t1.folder_path_ids[2]))
  • Rows Removed by Join Filter: 10
22. 0.007 0.144 ↑ 1.5 2 1

GroupAggregate (cost=15.79..15.86 rows=3 width=7) (actual time=0.142..0.144 rows=2 loops=1)

  • Group Key: (t1.folder_path_ids[2])
23. 0.004 0.137 ↑ 1.5 2 1

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

  • Sort Key: (t1.folder_path_ids[2])
  • Sort Method: quicksort Memory: 25kB
24. 0.001 0.133 ↑ 1.5 2 1

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

25. 0.011 0.132 ↑ 1.5 2 1

HashAggregate (cost=15.71..15.74 rows=3 width=72) (actual time=0.131..0.132 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))
26. 0.000 0.121 ↑ 1.5 2 1

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

27. 0.002 0.118 ↑ 1.0 2 1

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

28. 0.011 0.116 ↑ 1.0 2 1

Sort (cost=13.14..13.14 rows=2 width=72) (actual time=0.116..0.116 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
29. 0.000 0.105 ↑ 1.0 2 1

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

30. 0.013 0.096 ↓ 2.0 2 1

Bitmap Heap Scan on document_rights (cost=4.42..7.75 rows=1 width=68) (actual time=0.090..0.096 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
31. 0.083 0.083 ↓ 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.083..0.083 rows=8 loops=1)

  • Index Cond: ('{112198}'::integer[] <@ folder_path_ids)
32. 0.001 0.009 ↓ 0.0 0 1

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

33. 0.000 0.008 ↓ 0.0 0 1

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

34. 0.008 0.008 ↓ 0.0 0 1

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

  • Filter: (user_id = 5)
  • Rows Removed by Filter: 40
35. 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
36. 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))
37. 0.000 0.003 ↓ 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.003..0.003 rows=0 loops=1)

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

  • Index Cond: (user_id = 5)
39. 0.004 0.028 ↓ 6.0 6 2

Materialize (cost=0.42..4.16 rows=1 width=51) (actual time=0.008..0.014 rows=6 loops=2)

40. 0.024 0.024 ↓ 7.0 7 1

Index Scan using document_files_parent_id_index on document_files (cost=0.42..4.16 rows=1 width=51) (actual time=0.012..0.024 rows=7 loops=1)

  • Index Cond: (parent_id = 112,198)
  • Filter: (((time_deleted IS NULL) OR (time_deleted >= '2020-10-12 09:51:36.212874+00'::timestamp with time zone)) AND (organization_id = 2))
  • Rows Removed by Filter: 2
41. 0.010 0.010 ↓ 0.0 0 2

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.005 rows=0 loops=2)

  • Index Cond: (document_file_id = document_files.id)
  • Filter: (latest_version IS TRUE)
42. 0.196 0.196 ↑ 1.1 37 2

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

  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 529
43. 0.010 0.010 ↑ 1.0 1 2

Index Scan using users_pkey on users creators (cost=0.14..2.36 rows=1 width=15) (actual time=0.005..0.005 rows=1 loops=2)

  • Index Cond: (id = document_files.created_by)
44. 0.006 0.006 ↓ 0.0 0 2

Index Scan using users_pkey on users uploaders (cost=0.14..0.16 rows=1 width=15) (actual time=0.002..0.003 rows=0 loops=2)

  • Index Cond: (id = document_file_versions.uploaded_by)
45. 0.004 0.004 ↓ 0.0 0 2

Index Scan using users_pkey on users lockers (cost=0.14..0.16 rows=1 width=15) (actual time=0.002..0.002 rows=0 loops=2)

  • Index Cond: (id = document_file_versions.locked_by)
46. 0.020 0.020 ↓ 0.0 0 2

Index Scan using users_pkey on users drafters (cost=0.14..0.58 rows=1 width=15) (actual time=0.010..0.010 rows=0 loops=2)

  • Index Cond: (id = document_file_version_drafts.created_by)
47. 0.024 0.024 ↓ 0.0 0 2

Index Scan using users_pkey on users last_actors (cost=0.14..0.16 rows=1 width=15) (actual time=0.012..0.012 rows=0 loops=2)

  • Index Cond: (id = document_file_version_user_actions.created_by)
48. 0.008 0.008 ↓ 0.0 0 2

Index Scan using users_pkey on users deleters (cost=0.14..0.16 rows=1 width=15) (actual time=0.004..0.004 rows=0 loops=2)

  • Index Cond: (id = document_file_version_user_actions.created_by)