explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c6V0

Settings
# exclusive inclusive rows x rows loops node
1. 1,645.959 106,503.136 ↓ 24.1 90,103 1

Sort (cost=584,260.92..584,270.27 rows=3,739 width=1,151) (actual time=106,301.683..106,503.136 rows=90,103 loops=1)

  • Sort Key: fi.name
  • Sort Method: external merge Disk: 54376kB
2. 81,351.375 104,857.177 ↓ 24.1 90,103 1

Nested Loop (cost=6,287.61..582,159.54 rows=3,739 width=1,151) (actual time=275.927..104,857.177 rows=90,103 loops=1)

3. 27.043 27.043 ↑ 1.0 1 1

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

  • Index Cond: (id = '1061560065'::bigint)
4. 163.892 9,332.588 ↓ 24.1 90,103 1

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

  • Hash Cond: (fi.id = s.object_id)
5. 273.181 9,054.747 ↓ 24.1 90,103 1

Nested Loop (cost=5,166.40..51,300.30 rows=3,739 width=408) (actual time=132.602..9,054.747 rows=90,103 loops=1)

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

Nested Loop (cost=5,166.15..51,204.27 rows=3,831 width=398) (actual time=132.486..6,979.506 rows=90,103 loops=1)

7. 3,640.588 3,766.126 ↓ 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=132.445..3,766.126 rows=90,103 loops=1)

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

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

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

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

  • Index Cond: (owner_id = 4864001)
11. 2,973.399 2,973.399 ↑ 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.032..0.033 rows=1 loops=90,103)

  • Index Cond: (id = fi.folder_id)
  • Filter: (NOT trashed)
12. 1,802.060 1,802.060 ↑ 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.019..0.020 rows=1 loops=90,103)

13. 0.201 113.949 ↑ 1.3 241 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
14. 88.309 113.748 ↑ 1.3 241 1

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

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

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

  • Index Cond: (owner_id = 4864001)
16.          

SubPlan (forNested Loop)

17. 630.721 630.721 ↓ 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.007..0.007 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. 270.309 450.515 ↓ 0.0 0 90,103

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

  • Sort Key: shareusers.id
  • Sort Method: quicksort Memory: 25kB
19. 180.206 180.206 ↓ 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.002..0.002 rows=0 loops=90,103)

  • Index Cond: (share_id = s.id)
  • Filter: (COALESCE(end_date, '1543826440'::bigint) >= 1543826440)
20. 90.103 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_1.id
  • Sort Method: quicksort Memory: 25kB
21. 90.103 90.103 ↓ 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.001 rows=0 loops=90,103)

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

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

  • Sort Key: ((SubPlan 4))
  • Sort Method: quicksort Memory: 25kB
23. 53.186 90.103 ↓ 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.001..0.001 rows=0 loops=90,103)

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

SubPlan (forIndex Scan)

25. 36.917 36.917 ↑ 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=1.942..1.943 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, '1543826440'::bigint) >= 1543826440)
28.          

SubPlan (forIndex Scan)

29. 0.133 0.133 ↑ 1.0 1 19

Index Scan using ux_contacts_owner_contactuser on contacts c_1 (cost=0.42..8.44 rows=1 width=19) (actual time=0.007..0.007 rows=1 loops=19)

  • Index Cond: ((owner_id = s.owner_id) AND (contactuser_id = shareusers_3.user_id))
30. 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
31. 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, '1543826440'::bigint) >= 1543826440)
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_5.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_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, '1543826440'::bigint) >= 1543826440)
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_6.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_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, '1543826440'::bigint) >= 1543826440)
36. 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
37. 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, '1543826440'::bigint) >= 1543826440)
38. 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
39. 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, '1543826440'::bigint) >= 1543826440)
40. 1,441.648 1,441.648 ↓ 0.0 0 90,103

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

  • Filter: (object_id = fi.id)
  • Rows Removed by Filter: 72
41. 10,181.639 10,181.639 ↑ 1.0 1 90,103

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

  • Index Cond: (object_id = fi.id)