explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xyKR

Settings
# exclusive inclusive rows x rows loops node
1. 1,765.471 141,177.068 ↓ 24.1 90,103 1

Sort (cost=584,260.92..584,270.27 rows=3,739 width=1,151) (actual time=140,986.716..141,177.068 rows=90,103 loops=1)

  • Sort Key: fi.name
  • Sort Method: external merge Disk: 54376kB
2. 96,454.432 139,411.597 ↓ 24.1 90,103 1

Nested Loop (cost=6,287.61..582,159.54 rows=3,739 width=1,151) (actual time=482.178..139,411.597 rows=90,103 loops=1)

3. 0.047 0.047 ↑ 1.0 1 1

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

  • Index Cond: (id = '1061560065'::bigint)
4. 160.888 17,548.072 ↓ 24.1 90,103 1

Hash Left Join (cost=6,287.18..52,481.85 rows=3,739 width=424) (actual time=42.060..17,548.072 rows=90,103 loops=1)

  • Hash Cond: (fi.id = s.object_id)
5. 247.115 17,385.523 ↓ 24.1 90,103 1

Nested Loop (cost=5,166.40..51,300.30 rows=3,739 width=408) (actual time=40.382..17,385.523 rows=90,103 loops=1)

  • Join Filter: ((NOT lt.secured) OR (fi.owner_id <> 4864001) OR (COALESCE(lt.parent_twofactored_ids, '{}'::bigint[]) && '{}'::bigint[]))
6. 185.724 15,246.245 ↓ 23.5 90,103 1

Nested Loop (cost=5,166.15..51,204.27 rows=3,831 width=398) (actual time=40.267..15,246.245 rows=90,103 loops=1)

7. 8,805.794 8,843.414 ↓ 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=40.238..8,843.414 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
8. 1.915 37.620 ↓ 0.0 0 1

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

9. 11.903 11.903 ↓ 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=11.903..11.903 rows=90,103 loops=1)

  • Index Cond: (storage_id = '1061560065'::bigint)
10. 23.802 23.802 ↓ 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=23.802..23.802 rows=198,057 loops=1)

  • Index Cond: (owner_id = '4864001'::bigint)
11. 6,217.107 6,217.107 ↑ 1.0 1 90,103

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

  • Index Cond: (id = fi.folder_id)
  • Filter: (NOT trashed)
12. 1,892.163 1,892.163 ↑ 1.0 1 90,103

Function Scan on __file_gettwofactored_info_by_parent lt (cost=0.25..0.26 rows=1 width=33) (actual time=0.020..0.021 rows=1 loops=90,103)

13. 0.051 1.661 ↑ 1.3 241 1

Hash (cost=1,116.91..1,116.91 rows=310 width=24) (actual time=1.661..1.661 rows=241 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
14. 1.398 1.610 ↑ 1.3 241 1

Bitmap Heap Scan on shares s (cost=24.83..1,116.91 rows=310 width=24) (actual time=0.305..1.610 rows=241 loops=1)

  • Recheck Cond: (owner_id = 4864001)
  • Filter: (sharetype_id = 2)
  • Rows Removed by Filter: 831
  • Heap Blocks: exact=569
15. 0.212 0.212 ↑ 1.0 1,076 1

Bitmap Index Scan on ix_shares_owner (cost=0.00..24.75 rows=1,111 width=0) (actual time=0.212..0.212 rows=1,076 loops=1)

  • Index Cond: (owner_id = 4864001)
16.          

SubPlan (forNested Loop)

17. 1,261.442 1,261.442 ↓ 0.0 0 90,103

Index Scan using ix_shares_object on shares (cost=0.42..8.44 rows=1 width=8) (actual time=0.014..0.014 rows=0 loops=90,103)

  • Index Cond: (object_id = fi.id)
  • Filter: ((sharetype_id = 3) AND (owner_id = 4864001))
  • Rows Removed by Filter: 0
18. 360.412 630.721 ↓ 0.0 0 90,103

Sort (cost=11.16..11.16 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=90,103)

  • Sort Key: shareusers.id
  • Sort Method: quicksort Memory: 25kB
19. 270.309 270.309 ↓ 0.0 0 90,103

Index Scan using ix_shareusers_share on shareusers (cost=0.42..11.15 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=90,103)

  • Index Cond: (share_id = s.id)
  • Filter: (COALESCE(end_date, '1543600212'::bigint) >= 1543600212)
20. 90.103 270.309 ↓ 0.0 0 90,103

Sort (cost=11.41..11.41 rows=1 width=40) (actual time=0.003..0.003 rows=0 loops=90,103)

  • Sort Key: shareusers_1.id
  • Sort Method: quicksort Memory: 25kB
21. 180.206 180.206 ↓ 0.0 0 90,103

Index Scan using ix_shareusers_share on shareusers shareusers_1 (cost=0.42..11.40 rows=1 width=40) (actual time=0.001..0.002 rows=0 loops=90,103)

  • Index Cond: (share_id = s.id)
  • Filter: (COALESCE(end_date, '1543600212'::bigint) >= 1543600212)
22. 180.206 360.412 ↓ 0.0 0 90,103

Sort (cost=19.60..19.60 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=90,103)

  • Sort Key: ((SubPlan 4))
  • Sort Method: quicksort Memory: 25kB
23. 37.022 180.206 ↓ 0.0 0 90,103

Index Scan using ix_shareusers_share on shareusers shareusers_2 (cost=0.42..19.59 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=90,103)

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

SubPlan (forIndex Scan)

25. 143.184 143.184 ↑ 1.0 1 19

Index Scan using ux_contacts_owner_contactuser on contacts c (cost=0.42..8.44 rows=1 width=8) (actual time=7.534..7.536 rows=1 loops=19)

  • Index Cond: ((owner_id = s.owner_id) AND (contactuser_id = shareusers_2.user_id))
26. 180.206 180.206 ↓ 0.0 0 90,103

Sort (cost=19.60..19.60 rows=1 width=40) (actual time=0.002..0.002 rows=0 loops=90,103)

  • Sort Key: shareusers_3.id
  • Sort Method: quicksort Memory: 25kB
27. 0.000 0.000 ↓ 0.0 0 90,103

Index Scan using ix_shareusers_share on shareusers shareusers_3 (cost=0.42..19.59 rows=1 width=40) (actual time=0.000..0.000 rows=0 loops=90,103)

  • Index Cond: (share_id = s.id)
  • Filter: (COALESCE(end_date, '1543600212'::bigint) >= 1543600212)
28. 180.206 180.206 ↓ 0.0 0 90,103

Sort (cost=11.16..11.16 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=90,103)

  • Sort Key: shareusers_4.id
  • Sort Method: quicksort Memory: 25kB
29. 0.000 0.000 ↓ 0.0 0 90,103

Index Scan using ix_shareusers_share on shareusers shareusers_4 (cost=0.42..11.15 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=90,103)

  • Index Cond: (share_id = s.id)
  • Filter: (COALESCE(end_date, '1543600212'::bigint) >= 1543600212)
30. 180.206 180.206 ↓ 0.0 0 90,103

Sort (cost=11.41..11.41 rows=1 width=40) (actual time=0.002..0.002 rows=0 loops=90,103)

  • Sort Key: shareusers_5.id
  • Sort Method: quicksort Memory: 25kB
31. 0.000 0.000 ↓ 0.0 0 90,103

Index Scan using ix_shareusers_share on shareusers shareusers_5 (cost=0.42..11.40 rows=1 width=40) (actual time=0.000..0.000 rows=0 loops=90,103)

  • Index Cond: (share_id = s.id)
  • Filter: (COALESCE(end_date, '1543600212'::bigint) >= 1543600212)
32. 180.206 180.206 ↓ 0.0 0 90,103

Sort (cost=11.41..11.41 rows=1 width=40) (actual time=0.002..0.002 rows=0 loops=90,103)

  • Sort Key: shareusers_6.id
  • Sort Method: quicksort Memory: 25kB
33. 0.000 0.000 ↓ 0.0 0 90,103

Index Scan using ix_shareusers_share on shareusers shareusers_6 (cost=0.42..11.40 rows=1 width=40) (actual time=0.000..0.000 rows=0 loops=90,103)

  • Index Cond: (share_id = s.id)
  • Filter: (COALESCE(end_date, '1543600212'::bigint) >= 1543600212)
34. 180.206 180.206 ↓ 0.0 0 90,103

Sort (cost=11.41..11.41 rows=1 width=40) (actual time=0.002..0.002 rows=0 loops=90,103)

  • Sort Key: shareusers_7.id
  • Sort Method: quicksort Memory: 25kB
35. 0.000 0.000 ↓ 0.0 0 90,103

Index Scan using ix_shareusers_share on shareusers shareusers_7 (cost=0.42..11.40 rows=1 width=40) (actual time=0.000..0.000 rows=0 loops=90,103)

  • Index Cond: (share_id = s.id)
  • Filter: (COALESCE(end_date, '1543600212'::bigint) >= 1543600212)
36. 180.206 180.206 ↓ 0.0 0 90,103

Sort (cost=11.16..11.16 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=90,103)

  • Sort Key: shareusers_8.id
  • Sort Method: quicksort Memory: 25kB
37. 0.000 0.000 ↓ 0.0 0 90,103

Index Scan using ix_shareusers_share on shareusers shareusers_8 (cost=0.42..11.15 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=90,103)

  • Index Cond: (share_id = s.id)
  • Filter: (COALESCE(end_date, '1543600212'::bigint) >= 1543600212)
38. 1,531.751 1,531.751 ↓ 0.0 0 90,103

Seq Scan on dogtags (cost=0.00..2.90 rows=1 width=8) (actual time=0.017..0.017 rows=0 loops=90,103)

  • Filter: (object_id = fi.id)
  • Rows Removed by Filter: 72
39. 20,273.175 20,273.175 ↑ 1.0 1 90,103

Index Scan using pk_objectrecentmodifications on objectrecentmodifications (cost=0.43..8.45 rows=1 width=8) (actual time=0.224..0.225 rows=1 loops=90,103)

  • Index Cond: (object_id = fi.id)