explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W50p : Optimization for: Optimization for: Optimization for: plan #IfLK; plan #NO35; plan #pYyF

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 615.604 473,991.598 ↓ 0.0 0 1

Seq Scan on sungero_system_foldertag t (cost=0.00..646,437,947.09 rows=315,065 width=24) (actual time=473,991.598..473,991.598 rows=0 loops=1)

  • Filter: (SubPlan 1)
  • Rows Removed by Filter: 638,834
2.          

SubPlan (for Seq Scan)

3. 638.834 473,375.994 ↓ 0.0 0 638,834

GroupAggregate (cost=1.84..1,025.86 rows=1 width=28) (actual time=0.741..0.741 rows=0 loops=638,834)

  • Group Key: ft.entityid, ft.folderid, ft.owner
  • Filter: (sum(COALESCE(link.id, 0)) = 0)
  • Rows Removed by Filter: 1
4. 746.341 472,737.160 ↑ 1.0 1 638,834

Nested Loop Left Join (cost=1.84..1,025.84 rows=1 width=28) (actual time=0.561..0.740 rows=1 loops=638,834)

  • Join Filter: ((link.destinationid = items.id) AND (link.destinationtypeguid = items.discriminator) AND (link.folder = folder.id))
  • Rows Removed by Join Filter: 0
5. 606.000 470,181.824 ↑ 1.0 1 638,834

Nested Loop Left Join (cost=1.41..1,023.18 rows=1 width=48) (actual time=0.558..0.736 rows=1 loops=638,834)

  • Join Filter: (folder.author = ft.owner)
6. 1,746.974 5,749.506 ↑ 1.0 1 638,834

Nested Loop Left Join (cost=1.00..19.75 rows=1 width=44) (actual time=0.009..0.009 rows=1 loops=638,834)

  • Join Filter: (ft.entityid = items.id)
7. 2,555.336 2,555.336 ↑ 1.0 1 638,834

Index Only Scan using idx_foldertag on sungero_system_foldertag ft (cost=0.42..16.95 rows=1 width=24) (actual time=0.003..0.004 rows=1 loops=638,834)

  • Index Cond: ((folderid = ANY ('{c21fa108-1570-4155-8a0c-0d04957bc72b,0d61168d-1ae5-4570-a42e-bdd5f3b07b02,669ff039-2f05-4eb3-9d6a-049dafee948f,1ead1abe-1b7e-4833-9e01-ee7f8404537a,852ea6a2-304e-48d1-88e5-1bcbb1fb69a2,a353a121-f96e-4842-8b80-bf77d074a0c2,35d23c5e-9106-4068-bc3a-46b471928fa0,a584ebf5-f9b3-4e41-b7cf-15d8c2f96844,aee5f0a0-8a5f-422f-bc40-e98db38e94ca,ae3b3ff0-73ac-4590-961c-adf4579d59de,1031e40e-2c3a-4964-92ce-92036cdcb3b9}'::uuid[])) AND (folderid = t.folderid) AND (owner = t.owner) AND (entityid = t.entityid))
  • Heap Fetches: 170,381
8. 1,447.196 1,447.196 ↑ 1.0 1 361,799

Index Scan using sungero_wf_assignment_pk on sungero_wf_assignment items (cost=0.57..2.79 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=361,799)

  • Index Cond: (id = t.entityid)
9. 463,826.318 463,826.318 ↑ 1.0 1 361,799

Index Scan using idx_folder_discriminator_specialfoldertype_author on sungero_core_folder folder (cost=0.41..1,003.42 rows=1 width=8) (actual time=0.969..1.282 rows=1 loops=361,799)

  • Index Cond: (author = t.owner)
  • Filter: (isspecial AND (name = 'eb72a985-606c-4647-ba0c-1389928a4e17'::citext))
  • Rows Removed by Filter: 4
10. 1,808.995 1,808.995 ↑ 1.0 1 361,799

Index Scan using idx_destinationid_destinationtypeguid on sungero_core_link link (cost=0.43..2.65 rows=1 width=28) (actual time=0.005..0.005 rows=1 loops=361,799)

  • Index Cond: (destinationid = t.entityid)
Planning time : 1.232 ms
Execution time : 473,991.657 ms