explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TkKK

Settings
# exclusive inclusive rows x rows loops node
1. 0.277 2,549.024 ↑ 17.3 158 1

Sort (cost=5,689,883.57..5,689,890.39 rows=2,728 width=215) (actual time=2,549.008..2,549.024 rows=158 loops=1)

  • Sort Key: fielddetails.c_order
  • Sort Method: quicksort Memory: 551kB
2. 9.274 2,548.747 ↑ 17.3 158 1

WindowAgg (cost=80,549.55..5,689,727.89 rows=2,728 width=215) (actual time=2,488.648..2,548.747 rows=158 loops=1)

3. 0.177 2,442.847 ↑ 17.3 158 1

Sort (cost=80,549.55..80,556.37 rows=2,728 width=141) (actual time=2,442.809..2,442.847 rows=158 loops=1)

  • Sort Key: fieldschema.c_parentlabelid, fielddetails.c_order
  • Sort Method: quicksort Memory: 500kB
4. 5.295 2,442.670 ↑ 17.3 158 1

GroupAggregate (cost=80,305.21..80,393.87 rows=2,728 width=141) (actual time=2,435.311..2,442.670 rows=158 loops=1)

  • Group Key: fielddetails.c_order, fieldschema.c_fieldschemaid, t_sys_users.c_username
5. 14.320 2,437.375 ↓ 4.5 12,213 1

Sort (cost=80,305.21..80,312.03 rows=2,728 width=133) (actual time=2,435.151..2,437.375 rows=12,213 loops=1)

  • Sort Key: fielddetails.c_order, fieldschema.c_fieldschemaid, t_sys_users.c_username
  • Sort Method: external merge Disk: 1680kB
6. 3.742 2,423.055 ↓ 4.5 12,213 1

Hash Join (cost=76,739.21..80,149.52 rows=2,728 width=133) (actual time=17.003..2,423.055 rows=12,213 loops=1)

  • Hash Cond: (parentchildctedb.appfamilyid = fieldschema.c_fieldschemaid)
7. 2,402.877 2,402.877 ↑ 15.2 6,605 1

CTE Scan on parentchildctedb (cost=74,612.83..76,617.59 rows=100,238 width=306) (actual time=0.560..2,402.877 rows=6,605 loops=1)

8.          

CTE parentchildctedb

9. 4.424 2,400.033 ↑ 15.2 6,605 1

Recursive Union (cost=0.00..74,612.83 rows=100,238 width=48) (actual time=0.556..2,400.033 rows=6,605 loops=1)

10. 8.701 8.701 ↑ 28.4 158 1

Seq Scan on t_sys_fieldschema fieldschema_1 (cost=0.00..1,337.29 rows=4,488 width=48) (actual time=0.553..8.701 rows=158 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: 36684
11. 1,416.132 2,386.908 ↑ 9,575.0 1 5,004

Merge Join (cost=6,759.05..7,127.08 rows=9,575 width=48) (actual time=0.471..0.477 rows=1 loops=5,004)

  • Merge Cond: (parentchildctedb_1.c_fieldschemaid = fieldschema_2.c_parentlabelid)
12. 5.004 5.004 ↑ 44,880.0 1 5,004

Sort (cost=4,994.78..5,106.98 rows=44,880 width=32) (actual time=0.001..0.001 rows=1 loops=5,004)

  • Sort Key: parentchildctedb_1.c_fieldschemaid
  • Sort Method: quicksort Memory: 25kB
13. 0.000 0.000 ↑ 44,880.0 1 5,004

WorkTable Scan on parentchildctedb parentchildctedb_1 (cost=0.00..897.60 rows=44,880 width=32) (actual time=0.000..0.000 rows=1 loops=5,004)

14. 957.032 965.772 ↑ 2.0 3,306 5,004

Sort (cost=1,764.27..1,781.07 rows=6,717 width=48) (actual time=0.002..0.193 rows=3,306 loops=5,004)

  • Sort Key: fieldschema_2.c_parentlabelid
  • Sort Method: quicksort Memory: 744kB
15. 8.740 8.740 ↑ 1.0 6,671 1

Seq Scan on t_sys_fieldschema fieldschema_2 (cost=0.00..1,337.29 rows=6,717 width=48) (actual time=0.136..8.740 rows=6,671 loops=1)

  • Filter: (c_tablegroupsid = 'e4308ac6-c388-11e7-8c54-9b97e6527505'::uuid)
  • Rows Removed by Filter: 30171
16. 0.151 16.436 ↑ 1.8 569 1

Hash (cost=2,113.86..2,113.86 rows=1,002 width=101) (actual time=16.436..16.436 rows=569 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 77kB
17. 0.155 16.285 ↑ 1.8 569 1

Hash Left Join (cost=1,748.97..2,113.86 rows=1,002 width=101) (actual time=13.330..16.285 rows=569 loops=1)

  • Hash Cond: (fieldschema.c_createdby = t_sys_users.c_userid)
18. 1.602 16.084 ↑ 1.8 569 1

Hash Right Join (cost=1,746.24..2,108.38 rows=1,002 width=83) (actual time=13.270..16.084 rows=569 loops=1)

  • Hash Cond: (fielddetails.c_fieldschemaid = fieldschema.c_fieldschemaid)
19. 1.234 1.234 ↓ 1.0 8,783 1

Seq Scan on t_e20so1_fielddetails fielddetails (cost=0.00..326.82 rows=8,782 width=24) (actual time=0.014..1.234 rows=8,783 loops=1)

20. 0.168 13.248 ↑ 1.8 566 1

Hash (cost=1,733.72..1,733.72 rows=1,002 width=75) (actual time=13.247..13.248 rows=566 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 62kB
21. 1.022 13.080 ↑ 1.8 566 1

Hash Right Join (cost=1,442.16..1,733.72 rows=1,002 width=75) (actual time=10.265..13.080 rows=566 loops=1)

  • Hash Cond: (fieldpermission.c_fieldschemaid = fieldschema.c_fieldschemaid)
22. 1.869 1.869 ↓ 1.0 8,759 1

Index Only Scan using "UQ_8cf37391-dff4-4d53-975c-0e8ddc9a64fe" on t_sys_fieldpermission fieldpermission (cost=0.29..268.90 rows=8,741 width=16) (actual time=0.050..1.869 rows=8,759 loops=1)

  • Heap Fetches: 287
23. 0.060 10.189 ↑ 6.3 158 1

Hash (cost=1,429.35..1,429.35 rows=1,002 width=75) (actual time=10.189..10.189 rows=158 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
24. 10.129 10.129 ↑ 6.3 158 1

Seq Scan on t_sys_fieldschema fieldschema (cost=0.00..1,429.35 rows=1,002 width=75) (actual time=0.675..10.129 rows=158 loops=1)

  • Filter: ((c_parentlabelid IS NULL) AND (c_tablegroupsid = 'e4308ac6-c388-11e7-8c54-9b97e6527505'::uuid) AND (c_parentid = '1813812c-cdc0-11e7-afc9-5f7d878464cc'::uuid))
  • Rows Removed by Filter: 36684
25. 0.022 0.046 ↑ 1.0 77 1

Hash (cost=1.77..1.77 rows=77 width=34) (actual time=0.046..0.046 rows=77 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
26. 0.024 0.024 ↑ 1.0 77 1

Seq Scan on t_sys_users (cost=0.00..1.77 rows=77 width=34) (actual time=0.005..0.024 rows=77 loops=1)

27.          

SubPlan (for WindowAgg)

28. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.57..1,542.08 rows=1 width=0) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.29..1,540.67 rows=1 width=16) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Seq Scan on t_sys_fieldschema f (cost=0.00..1,337.29 rows=1 width=16) (never executed)

  • Filter: (c_parentlabelid = fieldschema.c_fieldschemaid)
31. 0.000 0.000 ↓ 0.0 0

Index Only Scan using "UQ_8cf37391-dff4-4d53-975c-0e8ddc9a64fe" on t_sys_fieldpermission fp (cost=0.29..203.36 rows=2 width=16) (never executed)

  • Index Cond: (c_fieldschemaid = f.c_fieldschemaid)
  • Heap Fetches: 0
32. 0.000 0.000 ↓ 0.0 0

Index Only Scan using t_e20so1_fielddetails_c_fieldschemaid_idx on t_e20so1_fielddetails fd (cost=0.29..1.40 rows=1 width=16) (never executed)

  • Index Cond: (c_fieldschemaid = f.c_fieldschemaid)
  • Heap Fetches: 0
33. 8.781 35.849 ↓ 1.1 41,590 1

Hash Left Join (cost=717.85..3,196.97 rows=36,823 width=16) (actual time=7.691..35.849 rows=41,590 loops=1)

  • Hash Cond: (f_1.c_fieldschemaid = fd_1.c_fieldschemaid)
34. 14.974 22.449 ↓ 1.1 41,570 1

Hash Left Join (cost=378.16..2,539.32 rows=36,823 width=32) (actual time=3.052..22.449 rows=41,570 loops=1)

  • Hash Cond: (f_1.c_fieldschemaid = fp_1.c_fieldschemaid)
35. 4.470 4.470 ↓ 1.0 36,842 1

Seq Scan on t_sys_fieldschema f_1 (cost=0.00..1,245.23 rows=36,823 width=32) (actual time=0.018..4.470 rows=36,842 loops=1)

36. 1.233 3.005 ↓ 1.0 8,759 1

Hash (cost=268.90..268.90 rows=8,741 width=16) (actual time=3.005..3.005 rows=8,759 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 539kB
37. 1.772 1.772 ↓ 1.0 8,759 1

Index Only Scan using "UQ_8cf37391-dff4-4d53-975c-0e8ddc9a64fe" on t_sys_fieldpermission fp_1 (cost=0.29..268.90 rows=8,741 width=16) (actual time=0.026..1.772 rows=8,759 loops=1)

  • Heap Fetches: 287
38. 1.358 4.619 ↓ 1.0 8,783 1

Hash (cost=229.91..229.91 rows=8,782 width=16) (actual time=4.619..4.619 rows=8,783 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 540kB
39. 3.261 3.261 ↓ 1.0 8,783 1

Index Only Scan using t_e20so1_fielddetails_c_fieldschemaid_idx on t_e20so1_fielddetails fd_1 (cost=0.29..229.91 rows=8,782 width=16) (actual time=1.763..3.261 rows=8,783 loops=1)

  • Heap Fetches: 84
40. 0.790 30.336 ↓ 1.5 3 158

Nested Loop Left Join (cost=0.29..207.21 rows=2 width=32) (actual time=0.061..0.192 rows=3 loops=158)

  • Join Filter: (r.c_roleid = permission.c_roleid)
  • Rows Removed by Join Filter: 18
41. 29.546 29.546 ↓ 1.5 3 158

Index Scan using "UQ_8cf37391-dff4-4d53-975c-0e8ddc9a64fe" on t_sys_fieldpermission permission (cost=0.29..205.56 rows=2 width=12) (actual time=0.060..0.187 rows=3 loops=158)

  • Index Cond: (c_fieldschemaid = fieldschema.c_fieldschemaid)
42. 0.000 0.000 ↑ 2.5 6 546

Materialize (cost=0.00..1.22 rows=15 width=18) (actual time=0.000..0.000 rows=6 loops=546)

43. 0.017 0.017 ↑ 1.0 15 1

Seq Scan on t_sys_roles r (cost=0.00..1.15 rows=15 width=18) (actual time=0.012..0.017 rows=15 loops=1)

44. 0.632 29.072 ↓ 1.5 3 158

Nested Loop Left Join (cost=0.29..205.00 rows=2 width=10) (actual time=0.058..0.184 rows=3 loops=158)

  • Join Filter: (r_1.c_roleid = permission_1.c_roleid)
  • Rows Removed by Join Filter: 18
45. 28.440 28.440 ↓ 1.5 3 158

Index Only Scan using "UQ_8cf37391-dff4-4d53-975c-0e8ddc9a64fe" on t_sys_fieldpermission permission_1 (cost=0.29..203.36 rows=2 width=8) (actual time=0.058..0.180 rows=3 loops=158)

  • Index Cond: (c_fieldschemaid = fieldschema.c_fieldschemaid)
  • Heap Fetches: 52
46. 0.000 0.000 ↑ 2.5 6 546

Materialize (cost=0.00..1.22 rows=15 width=18) (actual time=0.000..0.000 rows=6 loops=546)

47. 0.007 0.007 ↑ 1.0 15 1

Seq Scan on t_sys_roles r_1 (cost=0.00..1.15 rows=15 width=18) (actual time=0.004..0.007 rows=15 loops=1)

48. 0.000 0.000 ↓ 0.0 0

Index Only Scan using "UQ_8cf37391-dff4-4d53-975c-0e8ddc9a64fe" on t_sys_fieldpermission permission_2 (cost=0.29..203.36 rows=2 width=0) (never executed)

  • Index Cond: (c_fieldschemaid = fieldschema.c_fieldschemaid)
  • Heap Fetches: 0
49. 1.369 1.369 ↓ 1.0 8,759 1

Index Only Scan using "UQ_8cf37391-dff4-4d53-975c-0e8ddc9a64fe" on t_sys_fieldpermission permission_3 (cost=0.29..268.90 rows=8,741 width=16) (actual time=0.011..1.369 rows=8,759 loops=1)

  • Heap Fetches: 287