explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SjQ2

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 570.107 ↓ 0.0 0 1

Nested Loop (cost=5,167.24..57,958.07 rows=47 width=1,151) (actual time=570.107..570.107 rows=0 loops=1)

2. 0.133 0.133 ↑ 1.0 1 1

Index Scan using pk_storages on storages st (cost=0.43..8.45 rows=1 width=16) (actual time=0.131..0.133 rows=1 loops=1)

  • Index Cond: (id = '1061560065'::bigint)
3. 0.001 569.966 ↓ 0.0 0 1

Nested Loop Left Join (cost=5,166.82..51,291.57 rows=47 width=424) (actual time=569.966..569.966 rows=0 loops=1)

4. 0.001 569.965 ↓ 0.0 0 1

Nested Loop (cost=5,166.40..51,205.72 rows=47 width=408) (actual time=569.965..569.965 rows=0 loops=1)

  • Join Filter: ((NOT lt.secured) OR (fi.owner_id <> 4864001) OR (COALESCE(lt.parent_twofactored_ids, '{}'::bigint[]) && '{}'::bigint[]))
5. 43.068 569.964 ↓ 0.0 0 1

Nested Loop (cost=5,166.15..51,204.27 rows=48 width=398) (actual time=569.964..569.964 rows=0 loops=1)

6. 285.840 346.690 ↓ 23.2 90,103 1

Bitmap Heap Scan on files fi (cost=5,165.72..20,263.93 rows=3,879 width=358) (actual time=63.525..346.690 rows=90,103 loops=1)

  • Recheck Cond: ((storage_id = '1061560065'::bigint) AND (owner_id = '4864001'::bigint))
  • Filter: ((NOT moved_to_trash) AND (filetype_id = '1'::smallint) AND (filestate_id = ANY ('{1,5,7,8}'::integer[])))
  • Heap Blocks: exact=11552
7. 2.090 60.850 ↓ 0.0 0 1

BitmapAnd (cost=5,165.72..5,165.72 rows=4,226 width=0) (actual time=60.850..60.850 rows=0 loops=1)

8. 19.598 19.598 ↓ 1.0 90,103 1

Bitmap Index Scan on ix_files_storage (cost=0.00..1,635.06 rows=88,351 width=0) (actual time=19.598..19.598 rows=90,103 loops=1)

  • Index Cond: (storage_id = '1061560065'::bigint)
9. 39.162 39.162 ↓ 1.0 198,057 1

Bitmap Index Scan on ix_files_owner (cost=0.00..3,528.46 rows=190,938 width=0) (actual time=39.162..39.162 rows=198,057 loops=1)

  • Index Cond: (owner_id = '4864001'::bigint)
10. 180.206 180.206 ↓ 0.0 0 90,103

Index Scan using pk_folders on folders f (cost=0.43..7.97 rows=1 width=48) (actual time=0.002..0.002 rows=0 loops=90,103)

  • Index Cond: (id = fi.folder_id)
  • Filter: trashed
  • Rows Removed by Filter: 1
11. 0.000 0.000 ↓ 0.0 0

Function Scan on __file_gettwofactored_info_by_parent lt (cost=0.25..0.26 rows=1 width=33) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_shares_object on shares s (cost=0.42..1.82 rows=1 width=24) (never executed)

  • Index Cond: (object_id = fi.id)
  • Filter: ((sharetype_id = 2) AND (owner_id = 4864001))
13.          

SubPlan (forNested Loop)

14. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_shares_object on shares (cost=0.42..8.44 rows=1 width=8) (never executed)

  • Index Cond: (object_id = fi.id)
  • Filter: ((sharetype_id = 3) AND (owner_id = 4864001))
15. 0.000 0.000 ↓ 0.0 0

Sort (cost=11.16..11.16 rows=1 width=8) (never executed)

  • Sort Key: shareusers.id
16. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_shareusers_share on shareusers (cost=0.42..11.15 rows=1 width=8) (never executed)

  • Index Cond: (share_id = s.id)
  • Filter: (COALESCE(end_date, '1543600449'::bigint) >= 1543600449)
17. 0.000 0.000 ↓ 0.0 0

Sort (cost=11.41..11.41 rows=1 width=40) (never executed)

  • Sort Key: shareusers_1.id
18. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_shareusers_share on shareusers shareusers_1 (cost=0.42..11.40 rows=1 width=40) (never executed)

  • Index Cond: (share_id = s.id)
  • Filter: (COALESCE(end_date, '1543600449'::bigint) >= 1543600449)
19. 0.000 0.000 ↓ 0.0 0

Sort (cost=19.60..19.60 rows=1 width=8) (never executed)

  • Sort Key: ((SubPlan 4))
20. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_shareusers_share on shareusers shareusers_2 (cost=0.42..19.59 rows=1 width=8) (never executed)

  • Index Cond: (share_id = s.id)
  • Filter: (COALESCE(end_date, '1543600449'::bigint) >= 1543600449)
21.          

SubPlan (forIndex Scan)

22. 0.000 0.000 ↓ 0.0 0

Index Scan using ux_contacts_owner_contactuser on contacts c (cost=0.42..8.44 rows=1 width=8) (never executed)

  • Index Cond: ((owner_id = s.owner_id) AND (contactuser_id = shareusers_2.user_id))
23. 0.000 0.000 ↓ 0.0 0

Sort (cost=19.60..19.60 rows=1 width=40) (never executed)

  • Sort Key: shareusers_3.id
24. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_shareusers_share on shareusers shareusers_3 (cost=0.42..19.59 rows=1 width=40) (never executed)

  • Index Cond: (share_id = s.id)
  • Filter: (COALESCE(end_date, '1543600449'::bigint) >= 1543600449)
25.          

SubPlan (forIndex Scan)

26. 0.000 0.000 ↓ 0.0 0

Index Scan using ux_contacts_owner_contactuser on contacts c_1 (cost=0.42..8.44 rows=1 width=19) (never executed)

  • Index Cond: ((owner_id = s.owner_id) AND (contactuser_id = shareusers_3.user_id))
27. 0.000 0.000 ↓ 0.0 0

Sort (cost=11.16..11.16 rows=1 width=12) (never executed)

  • Sort Key: shareusers_4.id
28. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_shareusers_share on shareusers shareusers_4 (cost=0.42..11.15 rows=1 width=12) (never executed)

  • Index Cond: (share_id = s.id)
  • Filter: (COALESCE(end_date, '1543600449'::bigint) >= 1543600449)
29. 0.000 0.000 ↓ 0.0 0

Sort (cost=11.41..11.41 rows=1 width=40) (never executed)

  • Sort Key: shareusers_5.id
30. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_shareusers_share on shareusers shareusers_5 (cost=0.42..11.40 rows=1 width=40) (never executed)

  • Index Cond: (share_id = s.id)
  • Filter: (COALESCE(end_date, '1543600449'::bigint) >= 1543600449)
31. 0.000 0.000 ↓ 0.0 0

Sort (cost=11.41..11.41 rows=1 width=40) (never executed)

  • Sort Key: shareusers_6.id
32. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_shareusers_share on shareusers shareusers_6 (cost=0.42..11.40 rows=1 width=40) (never executed)

  • Index Cond: (share_id = s.id)
  • Filter: (COALESCE(end_date, '1543600449'::bigint) >= 1543600449)
33. 0.000 0.000 ↓ 0.0 0

Sort (cost=11.41..11.41 rows=1 width=40) (never executed)

  • Sort Key: shareusers_7.id
34. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_shareusers_share on shareusers shareusers_7 (cost=0.42..11.40 rows=1 width=40) (never executed)

  • Index Cond: (share_id = s.id)
  • Filter: (COALESCE(end_date, '1543600449'::bigint) >= 1543600449)
35. 0.000 0.000 ↓ 0.0 0

Sort (cost=11.16..11.16 rows=1 width=16) (never executed)

  • Sort Key: shareusers_8.id
36. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_shareusers_share on shareusers shareusers_8 (cost=0.42..11.15 rows=1 width=16) (never executed)

  • Index Cond: (share_id = s.id)
  • Filter: (COALESCE(end_date, '1543600449'::bigint) >= 1543600449)
37. 0.000 0.000 ↓ 0.0 0

Seq Scan on dogtags (cost=0.00..2.90 rows=1 width=8) (never executed)

  • Filter: (object_id = fi.id)
38. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_objectrecentmodifications on objectrecentmodifications (cost=0.43..8.45 rows=1 width=8) (never executed)

  • Index Cond: (object_id = fi.id)