explain.depesz.com

PostgreSQL's explain analyze made readable

Result: b8yh

Settings
# exclusive inclusive rows x rows loops node
1. 1,746.236 128,600.695 ↓ 24.1 90,103 1

Sort (cost=584,260.92..584,270.27 rows=3,739 width=1,151) (actual time=128,410.421..128,600.695 rows=90,103 loops=1)

  • Sort Key: fi.name
  • Sort Method: external merge Disk: 54376kB
2. 87,586.203 126,854.459 ↓ 24.1 90,103 1

Nested Loop (cost=6,287.61..582,159.54 rows=3,739 width=1,151) (actual time=371.929..126,854.459 rows=90,103 loops=1)

3. 27.469 27.469 ↑ 1.0 1 1

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

  • Index Cond: (id = '1061560065'::bigint)
4. 162.237 16,174.419 ↓ 24.1 90,103 1

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

  • Hash Cond: (fi.id = s.object_id)
5. 300.590 15,894.529 ↓ 24.1 90,103 1

Nested Loop (cost=5,166.40..51,300.30 rows=3,739 width=408) (actual time=214.870..15,894.529 rows=90,103 loops=1)

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

Nested Loop (cost=5,166.15..51,204.27 rows=3,831 width=398) (actual time=214.682..13,791.879 rows=90,103 loops=1)

7. 8,084.652 8,286.735 ↓ 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=214.614..8,286.735 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.198 202.083 ↓ 0.0 0 1

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

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

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

  • Index Cond: (owner_id = 4864001)
11. 5,316.077 5,316.077 ↑ 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.058..0.059 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.020..0.020 rows=1 loops=90,103)

13. 0.276 117.653 ↑ 1.3 241 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
14. 115.485 117.377 ↑ 1.3 241 1

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

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

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

  • Index Cond: (owner_id = 4864001)
16.          

SubPlan (forNested Loop)

17. 1,081.236 1,081.236 ↓ 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.012..0.012 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 901.030 ↓ 0.0 0 90,103

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

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

  • Index Cond: (share_id = s.id)
  • Filter: (COALESCE(end_date, '1543826440'::bigint) >= 1543826440)
20. 180.206 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. 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. 180.206 270.309 ↓ 0.0 0 90,103

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

  • Sort Key: ((SubPlan 4))
  • Sort Method: quicksort Memory: 25kB
23. 12.165 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. 77.938 77.938 ↑ 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=4.100..4.102 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.006..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. 18,020.600 18,020.600 ↑ 1.0 1 90,103

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

  • Index Cond: (object_id = fi.id)