explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nfAH

Settings
# exclusive inclusive rows x rows loops node
1. 47.211 1,092.541 ↓ 9.3 10,000 1

Gather (cost=59,865.48..65,999.30 rows=1,071 width=368) (actual time=946.629..1,092.541 rows=10,000 loops=1)

  • Output: shares.id, ba.display_name, files.path, files.name, files.directory, shared_resources.folder_id, bb.domain, bb.name, bc.type, bc.display_name, bc.name
  • Workers Planned: 2
  • Workers Launched: 2
2. 8.919 1,045.330 ↓ 7.5 3,333 3 / 3

Nested Loop (cost=58,865.48..64,892.20 rows=446 width=368) (actual time=956.265..1,045.330 rows=3,333 loops=3)

  • Output: shares.id, ba.display_name, files.path, files.name, files.directory, shared_resources.folder_id, bb.domain, bb.name, bc.type, bc.display_name, bc.name
  • Inner Unique: true
  • Join Filter: (snapshots.cloud_folder_id = bc.uid)
  • Worker 0: actual time=965.247..1031.246 rows=2,288 loops=1
  • Worker 1: actual time=957.768..1079.113 rows=4,605 loops=1
3. 9.413 1,029.744 ↓ 7.5 3,333 3 / 3

Nested Loop (cost=58,865.06..63,080.61 rows=446 width=292) (actual time=956.253..1,029.744 rows=3,333 loops=3)

  • Output: shares.id, shared_resources.folder_id, snapshots.cloud_folder_id, files.path, files.name, files.directory, ba.display_name, bb.domain, bb.name
  • Inner Unique: true
  • Worker 0: actual time=965.235..1019.924 rows=2,288 loops=1
  • Worker 1: actual time=957.756..1057.703 rows=4,605 loops=1
4. 10.670 1,013.664 ↓ 7.5 3,333 3 / 3

Nested Loop Left Join (cost=58,864.64..61,274.60 rows=446 width=261) (actual time=956.232..1,013.664 rows=3,333 loops=3)

  • Output: shares.id, shares.created_by_uid, shared_resources.folder_id, snapshots.cloud_folder_id, files.path, files.name, files.directory, bb.domain, bb.name
  • Worker 0: actual time=965.212..1008.373 rows=2,288 loops=1
  • Worker 1: actual time=957.730..1035.492 rows=4,605 loops=1
5. 13.479 976.327 ↓ 7.5 3,333 3 / 3

Merge Join (cost=58,863.44..58,944.64 rows=446 width=108) (actual time=956.169..976.327 rows=3,333 loops=3)

  • Output: shares.id, shares.created_by_uid, shared_resources.folder_id, shared_resources.guid_device, shared_resources.guid_version, snapshots.cloud_folder_id, snapshots.id, bb.domain, bb.name
  • Merge Cond: (shared_resources.folder_id = snapshots.cloud_folder_id)
  • Worker 0: actual time=965.136..981.310 rows=2,288 loops=1
  • Worker 1: actual time=957.659..982.928 rows=4,605 loops=1
6. 7.149 688.166 ↓ 3.0 3,333 3 / 3

Sort (cost=49,173.24..49,176.00 rows=1,106 width=92) (actual time=685.560..688.166 rows=3,333 loops=3)

  • Output: shares.id, shares.created_by_uid, shared_resources.folder_id, shared_resources.guid_device, shared_resources.guid_version, bb.domain, bb.name
  • Sort Key: shared_resources.folder_id
  • Sort Method: quicksort Memory: 533kB
  • Worker 0: actual time=682.420..684.214 rows=2,288 loops=1
  • Worker 1: actual time=684.949..688.537 rows=4,605 loops=1
7. 8.565 681.017 ↓ 3.0 3,333 3 / 3

Nested Loop (cost=1,425.37..49,117.32 rows=1,106 width=92) (actual time=47.694..681.017 rows=3,333 loops=3)

  • Output: shares.id, shares.created_by_uid, shared_resources.folder_id, shared_resources.guid_device, shared_resources.guid_version, bb.domain, bb.name
  • Inner Unique: true
  • Worker 0: actual time=57.874..678.649 rows=2,288 loops=1
  • Worker 1: actual time=37.827..680.004 rows=4,605 loops=1
8. 8.307 659.119 ↓ 3.0 3,333 3 / 3

Nested Loop (cost=1,424.95..44,637.18 rows=1,106 width=47) (actual time=47.643..659.119 rows=3,333 loops=3)

  • Output: shares.id, shares.resource_id, shares.created_by_uid, bb.domain, bb.name
  • Inner Unique: true
  • Worker 0: actual time=57.810..660.494 rows=2,288 loops=1
  • Worker 1: actual time=37.760..655.549 rows=4,605 loops=1
9. 310.183 637.479 ↑ 1.3 3,333 3 / 3

Hash Semi Join (cost=1,424.52..27,763.58 rows=4,167 width=32) (actual time=47.591..637.479 rows=3,333 loops=3)

  • Output: shares.id, shares.resource_id, shares.created_by_uid, shares.invitee_uid
  • Hash Cond: (shares.id = shares_1.id)
  • Worker 0: actual time=57.737..641.884 rows=2,288 loops=1
  • Worker 1: actual time=37.702..631.855 rows=4,605 loops=1
10. 279.900 279.900 ↑ 1.2 356,100 3 / 3

Parallel Seq Scan on public.shares (cost=0.00..25,124.25 rows=445,125 width=32) (actual time=0.011..279.900 rows=356,100 loops=3)

  • Output: shares.id, shares.invitee_uid, shares.created_by_uid, shares.type, shares.external_invitee_email, shares.key, shares.access_mode, shares.protection_level, shares.phone_number, shares.resource_id, shares.expiration_date, shares.create_date, shares.portal_id, shares.message
  • Worker 0: actual time=0.015..278.746 rows=338,841 loops=1
  • Worker 1: actual time=0.014..283.922 rows=363,722 loops=1
11. 10.845 47.396 ↑ 1.0 10,000 3 / 3

Hash (cost=1,299.52..1,299.52 rows=10,000 width=8) (actual time=47.396..47.396 rows=10,000 loops=3)

  • Output: shares_1.id
  • Buckets: 16,384 Batches: 1 Memory Usage: 519kB
  • Worker 0: actual time=57.412..57.412 rows=10,000 loops=1
  • Worker 1: actual time=37.473..37.473 rows=10,000 loops=1
12. 17.583 36.551 ↑ 1.0 10,000 3 / 3

Limit (cost=0.00..1,199.52 rows=10,000 width=8) (actual time=0.023..36.551 rows=10,000 loops=3)

  • Output: shares_1.id
  • Worker 0: actual time=0.032..44.512 rows=10,000 loops=1
  • Worker 1: actual time=0.029..29.309 rows=10,000 loops=1
13. 18.968 18.968 ↑ 28.4 10,000 3 / 3

Seq Scan on public.shares shares_1 (cost=0.00..34,026.75 rows=283,669 width=8) (actual time=0.019..18.968 rows=10,000 loops=3)

  • Output: shares_1.id
  • Filter: ((shares_1.type)::text <> 'external'::text)
  • Rows Removed by Filter: 20,007
  • Worker 0: actual time=0.026..23.974 rows=10,000 loops=1
  • Worker 1: actual time=0.026..15.473 rows=10,000 loops=1
14. 13.333 13.333 ↑ 1.0 1 10,000 / 3

Index Scan using base_objects_pkey on public.base_objects bb (cost=0.42..4.05 rows=1 width=31) (actual time=0.004..0.004 rows=1 loops=10,000)

  • Output: bb.uid, bb.name, bb.domain, bb.display_name, bb.type, bb.portal_id, bb.owner_id, bb.xml_field, bb.create_date, bb.modified_date, bb.is_deleted, bb.comment
  • Index Cond: (bb.uid = shares.invitee_uid)
  • Worker 0: actual time=0.005..0.005 rows=1 loops=2,288
  • Worker 1: actual time=0.003..0.003 rows=1 loops=4,605
15. 13.333 13.333 ↑ 1.0 1 10,000 / 3

Index Scan using shared_resources_pkey on public.shared_resources (cost=0.42..4.05 rows=1 width=61) (actual time=0.004..0.004 rows=1 loops=10,000)

  • Output: shared_resources.id, shared_resources.folder_id, shared_resources.guid_device, shared_resources.guid_version, shared_resources.sync_allowed, shared_resources.team_project, shared_resources.allow_reshare
  • Index Cond: (shared_resources.id = shares.resource_id)
  • Worker 0: actual time=0.005..0.005 rows=1 loops=2,288
  • Worker 1: actual time=0.003..0.003 rows=1 loops=4,605
16. 143.819 274.682 ↑ 1.3 11,355 3 / 3

Sort (cost=9,690.21..9,725.81 rows=14,242 width=16) (actual time=266.193..274.682 rows=11,355 loops=3)

  • Output: snapshots.cloud_folder_id, snapshots.id
  • Sort Key: snapshots.cloud_folder_id
  • Sort Method: external sort Disk: 2,688kB
  • Worker 0: actual time=278.263..286.033 rows=10,349 loops=1
  • Worker 1: actual time=268.259..277.796 rows=12,604 loops=1
17. 130.863 130.863 ↓ 6.4 91,322 3 / 3

Seq Scan on public.snapshots (cost=0.00..8,707.66 rows=14,242 width=16) (actual time=0.028..130.863 rows=91,322 loops=3)

  • Output: snapshots.cloud_folder_id, snapshots.id
  • Filter: (snapshots.current AND (NOT snapshots.is_temp))
  • Rows Removed by Filter: 394,479
  • Worker 0: actual time=0.044..140.718 rows=91,322 loops=1
  • Worker 1: actual time=0.030..133.776 rows=91,322 loops=1
18. 26.667 26.667 ↑ 1.0 1 10,000 / 3

Index Scan using guid on public.files (cost=1.20..5.21 rows=1 width=259) (actual time=0.007..0.008 rows=1 loops=10,000)

  • Output: files.path, files.name, files.directory, files.snapshot_id, files.guid_device, files.guid_version
  • Index Cond: ((snapshots.id = files.snapshot_id) AND ((shared_resources.guid_device)::text = (files.guid_device)::text) AND (shared_resources.guid_version = files.guid_version))
  • Worker 0: actual time=0.008..0.009 rows=1 loops=2,288
  • Worker 1: actual time=0.007..0.008 rows=1 loops=4,605
19. 6.667 6.667 ↑ 1.0 1 10,000 / 3

Index Scan using base_objects_pkey on public.base_objects ba (cost=0.42..4.05 rows=1 width=47) (actual time=0.002..0.002 rows=1 loops=10,000)

  • Output: ba.uid, ba.name, ba.domain, ba.display_name, ba.type, ba.portal_id, ba.owner_id, ba.xml_field, ba.create_date, ba.modified_date, ba.is_deleted, ba.comment
  • Index Cond: (ba.uid = shares.created_by_uid)
  • Worker 0: actual time=0.003..0.003 rows=1 loops=2,288
  • Worker 1: actual time=0.002..0.002 rows=1 loops=4,605
20. 6.667 6.667 ↑ 1.0 1 10,000 / 3

Index Scan using base_objects_pkey on public.base_objects bc (cost=0.42..4.05 rows=1 width=92) (actual time=0.002..0.002 rows=1 loops=10,000)

  • Output: bc.uid, bc.name, bc.domain, bc.display_name, bc.type, bc.portal_id, bc.owner_id, bc.xml_field, bc.create_date, bc.modified_date, bc.is_deleted, bc.comment
  • Index Cond: (bc.uid = shared_resources.folder_id)
  • Worker 0: actual time=0.002..0.002 rows=1 loops=2,288
  • Worker 1: actual time=0.002..0.002 rows=1 loops=4,605
Planning time : 3.426 ms
Execution time : 1,107.611 ms