explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 54eP

Settings
# exclusive inclusive rows x rows loops node
1. 1,966.272 161,934.170 ↓ 24.1 90,103 1

Sort (cost=584,260.92..584,270.27 rows=3,739 width=1,151) (actual time=161,736.508..161,934.170 rows=90,103 loops=1)

  • Sort Key: fi.name
  • Sort Method: external merge Disk: 54376kB
2. 102,108.994 159,967.898 ↓ 24.1 90,103 1

Nested Loop (cost=6,287.61..582,159.54 rows=3,739 width=1,151) (actual time=2,819.515..159,967.898 rows=90,103 loops=1)

3. 0.098 0.098 ↑ 1.0 1 1

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

  • Index Cond: (id = '1061560065'::bigint)
4. 162.311 24,340.490 ↓ 24.1 90,103 1

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

  • Hash Cond: (fi.id = s.object_id)
5. 251.132 23,257.285 ↓ 24.1 90,103 1

Nested Loop (cost=5,166.40..51,300.30 rows=3,739 width=408) (actual time=511.542..23,257.285 rows=90,103 loops=1)

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

Nested Loop (cost=5,166.15..51,204.27 rows=3,831 width=398) (actual time=470.935..21,113.990 rows=90,103 loops=1)

7. 12,520.954 12,977.021 ↓ 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=470.813..12,977.021 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. 2.784 456.067 ↓ 0.0 0 1

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

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

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

  • Index Cond: (owner_id = '4864001'::bigint)
11. 7,929.064 7,929.064 ↑ 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.087..0.088 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.495 920.894 ↑ 1.3 241 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
14. 901.571 920.399 ↑ 1.3 241 1

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

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

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

  • Index Cond: (owner_id = 4864001)
16.          

SubPlan (forNested Loop)

17. 2,252.575 2,252.575 ↓ 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.025..0.025 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 810.927 ↓ 0.0 0 90,103

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

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

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

Sort (cost=11.41..11.41 rows=1 width=40) (actual time=0.004..0.004 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.002..0.002 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. 0.000 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. 93.290 93.290 ↑ 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.909..4.910 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. 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_6.id
  • Sort Method: quicksort Memory: 25kB
35. 90.103 90.103 ↓ 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.001..0.001 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. 27,301.209 27,301.209 ↑ 1.0 1 90,103

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

  • Index Cond: (object_id = fi.id)