explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mCLe

Settings
# exclusive inclusive rows x rows loops node
1. 2,963.777 4,428.531 ↓ 2.3 72 1

GroupAggregate (cost=66,132.61..66,141.53 rows=31 width=129) (actual time=1,466.948..4,428.531 rows=72 loops=1)

  • Group Key: fielddetails.c_order, fieldschema.c_fieldschemaid, fielddetails.c_isprivate
2. 37.461 1,464.754 ↓ 1,192.5 36,969 1

Sort (cost=66,132.61..66,132.69 rows=31 width=125) (actual time=1,455.196..1,464.754 rows=36,969 loops=1)

  • Sort Key: fielddetails.c_order, fieldschema.c_fieldschemaid, fielddetails.c_isprivate
  • Sort Method: external merge Disk: 4,976kB
3. 25.096 1,427.293 ↓ 1,192.5 36,969 1

Nested Loop Left Join (cost=64,616.07..66,131.84 rows=31 width=125) (actual time=1,337.497..1,427.293 rows=36,969 loops=1)

4. 7.369 1,365.228 ↓ 1,192.5 36,969 1

Merge Join (cost=64,615.79..66,117.69 rows=31 width=116) (actual time=1,337.474..1,365.228 rows=36,969 loops=1)

  • Merge Cond: (fieldschema.c_fieldschemaid = parentchildctefolder.appfamilyid)
5. 16.471 16.471 ↑ 4.4 72 1

Index Scan using pk_t_fieldschema on t_sys_fieldschema fieldschema (cost=0.29..1,492.60 rows=317 width=84) (actual time=0.763..16.471 rows=72 loops=1)

  • Filter: ((c_parentid IS NOT NULL) AND (c_parentlabelid IS NULL) AND (c_tablegroupsid = 'e4308ac6-c388-11e7-8c54-9b97e6527505'::uuid))
  • Rows Removed by Filter: 19,118
6. 20.721 1,341.388 ↓ 21.8 36,969 1

Sort (cost=64,615.50..64,619.74 rows=1,697 width=32) (actual time=1,336.703..1,341.388 rows=36,969 loops=1)

  • Sort Key: parentchildctefolder.appfamilyid
  • Sort Method: external sort Disk: 1,672kB
7. 4.428 1,320.667 ↓ 21.8 36,969 1

Subquery Scan on parentchildctefolder (cost=64,473.55..64,524.46 rows=1,697 width=32) (actual time=1.366..1,320.667 rows=36,969 loops=1)

8. 1,316.239 1,316.239 ↓ 21.8 36,969 1

CTE Scan on parentchildcte (cost=64,473.55..64,507.49 rows=1,697 width=306) (actual time=1.366..1,316.239 rows=36,969 loops=1)

9.          

CTE parentchildcte

10. 11.397 1,298.619 ↓ 21.8 36,969 1

Recursive Union (cost=1,041.02..64,473.55 rows=1,697 width=49) (actual time=1.363..1,298.619 rows=36,969 loops=1)

11. 0.297 1.860 ↑ 4.4 72 1

Hash Right Join (cost=1,041.02..1,099.47 rows=317 width=49) (actual time=1.361..1.860 rows=72 loops=1)

  • Hash Cond: (fielddetails_1.c_fieldschemaid = fieldschema_1.c_fieldschemaid)
12. 0.313 0.313 ↓ 1.0 2,212 1

Seq Scan on t_e20so1_fielddetails fielddetails_1 (cost=0.00..52.74 rows=2,174 width=16) (actual time=0.012..0.313 rows=2,212 loops=1)

13. 0.019 1.250 ↑ 4.4 72 1

Hash (cost=1,037.06..1,037.06 rows=317 width=33) (actual time=1.250..1.250 rows=72 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
14. 1.231 1.231 ↑ 4.4 72 1

Index Scan using "IX_fieldschema" on t_sys_fieldschema fieldschema_1 (cost=0.41..1,037.06 rows=317 width=33) (actual time=0.762..1.231 rows=72 loops=1)

  • Index Cond: (c_tablegroupsid = 'e4308ac6-c388-11e7-8c54-9b97e6527505'::uuid)
  • Filter: ((c_parentid IS NOT NULL) AND (c_parentlabelid IS NULL))
  • Rows Removed by Filter: 471
15. 758.582 1,285.362 ↓ 9.9 1,367 27

Nested Loop Left Join (cost=103.44..6,334.01 rows=138 width=49) (actual time=1.274..47.606 rows=1,367 loops=27)

  • Join Filter: ((fieldschema_2.c_fieldschemaid = fielddetails_2.c_fieldschemaid) OR (fielddetails_2.c_isprivate AND (fieldschema_2.c_createdby = 71,939)))
  • Rows Removed by Join Filter: 324,371
16. 7.506 45.684 ↓ 1.1 147 27

Hash Join (cost=103.44..1,329.99 rows=130 width=57) (actual time=1.198..1.692 rows=147 loops=27)

  • Hash Cond: (fieldschema_2.c_parentlabelid = parentchildcte_1.c_fieldschemaid)
17. 23.436 23.436 ↓ 1.1 543 27

Index Scan using "IX_fieldschema" on t_sys_fieldschema fieldschema_2 (cost=0.41..1,037.06 rows=473 width=57) (actual time=0.141..0.868 rows=543 loops=27)

  • Index Cond: (c_tablegroupsid = 'e4308ac6-c388-11e7-8c54-9b97e6527505'::uuid)
18. 8.829 14.742 ↑ 2.3 1,369 27

Hash (cost=63.40..63.40 rows=3,170 width=32) (actual time=0.546..0.546 rows=1,369 loops=27)

  • Buckets: 4,096 Batches: 1 Memory Usage: 33kB
19. 5.913 5.913 ↑ 2.3 1,369 27

WorkTable Scan on parentchildcte parentchildcte_1 (cost=0.00..63.40 rows=3,170 width=32) (actual time=0.001..0.219 rows=1,369 loops=27)

20. 480.746 481.096 ↓ 1.0 2,212 3,976

Materialize (cost=0.00..63.61 rows=2,174 width=17) (actual time=0.000..0.121 rows=2,212 loops=3,976)

21. 0.350 0.350 ↓ 1.0 2,212 1

Seq Scan on t_e20so1_fielddetails fielddetails_2 (cost=0.00..52.74 rows=2,174 width=17) (actual time=0.007..0.350 rows=2,212 loops=1)

22. 36.969 36.969 ↑ 1.0 1 36,969

Index Scan using t_e20so1_fielddetails_c_fieldschemaid_idx on t_e20so1_fielddetails fielddetails (cost=0.28..0.45 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=36,969)

  • Index Cond: (fieldschema.c_fieldschemaid = c_fieldschemaid)