explain.depesz.com

PostgreSQL's explain analyze made readable

Result: K6rD

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

GroupAggregate (cost=66,132.61..100,140.85 rows=31 width=161) (actual time=1,499.952..4,710.750 rows=72 loops=1)

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

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

  • Sort Key: fielddetails.c_order, fieldschema.c_fieldschemaid, fielddetails.c_isprivate
  • Sort Method: external merge Disk: 4976kB
3. 26.143 1,455.171 ↓ 1,192.5 36,969 1

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

4. 7.176 1,392.059 ↓ 1,192.5 36,969 1

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

  • Merge Cond: (fieldschema.c_fieldschemaid = parentchildctefolder.appfamilyid)
5. 15.904 15.904 ↑ 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.726..15.904 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: 19118
6. 20.435 1,368.979 ↓ 21.8 36,969 1

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

  • Sort Key: parentchildctefolder.appfamilyid
  • Sort Method: external sort Disk: 1672kB
7. 4.176 1,348.544 ↓ 21.8 36,969 1

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

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

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

9.          

CTE parentchildcte

10. 11.167 1,326.540 ↓ 21.8 36,969 1

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

11. 0.304 1.904 ↑ 4.4 72 1

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

  • Hash Cond: (fielddetails_1.c_fieldschemaid = fieldschema_1.c_fieldschemaid)
12. 0.305 0.305 ↓ 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.014..0.305 rows=2,212 loops=1)

13. 0.021 1.295 ↑ 4.4 72 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
14. 1.274 1.274 ↑ 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.777..1.274 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. 779.034 1,313.469 ↓ 9.9 1,367 27

Nested Loop Left Join (cost=103.44..6,334.01 rows=138 width=49) (actual time=1.261..48.647 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 = 71939)))
  • Rows Removed by Join Filter: 324371
16. 7.533 45.387 ↓ 1.1 147 27

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

  • Hash Cond: (fieldschema_2.c_parentlabelid = parentchildcte_1.c_fieldschemaid)
17. 23.409 23.409 ↓ 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.137..0.867 rows=543 loops=27)

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 33kB
19. 5.859 5.859 ↑ 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.217 rows=1,369 loops=27)

20. 488.692 489.048 ↓ 1.0 2,212 3,976

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

21. 0.356 0.356 ↓ 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.356 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)
23.          

SubPlan (for GroupAggregate)

24. 0.504 273.240 ↑ 1.0 1 72

Aggregate (cost=1,096.74..1,096.75 rows=1 width=32) (actual time=3.795..3.795 rows=1 loops=72)

25. 272.736 272.736 ↑ 2.0 1 72

Seq Scan on t_sys_fieldschema pr (cost=0.00..1,096.72 rows=2 width=16) (actual time=2.717..3.788 rows=1 loops=72)

  • Filter: (c_parentlabelid = fieldschema.c_fieldschemaid)
  • Rows Removed by Filter: 19189
Planning time : 0.670 ms