explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HPkJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.344 2,581.659 ↑ 16.8 159 1

Sort (cost=5,574,213.30..5,574,219.98 rows=2,671 width=215) (actual time=2,581.644..2,581.659 rows=159 loops=1)

  • Sort Key: fielddetails.c_order
  • Sort Method: quicksort Memory: 551kB
2. 9.562 2,581.315 ↑ 16.8 159 1

WindowAgg (cost=80,153.94..5,574,061.28 rows=2,671 width=215) (actual time=2,521.486..2,581.315 rows=159 loops=1)

3. 0.151 2,477.979 ↑ 16.8 159 1

Sort (cost=80,153.94..80,160.62 rows=2,671 width=141) (actual time=2,477.948..2,477.979 rows=159 loops=1)

  • Sort Key: fieldschema.c_parentlabelid, fielddetails.c_order
  • Sort Method: quicksort Memory: 500kB
4. 5.310 2,477.828 ↑ 16.8 159 1

GroupAggregate (cost=79,915.11..80,001.92 rows=2,671 width=141) (actual time=2,470.526..2,477.828 rows=159 loops=1)

  • Group Key: fielddetails.c_order, fieldschema.c_fieldschemaid, t_sys_users.c_username
5. 14.194 2,472.518 ↓ 4.6 12,214 1

Sort (cost=79,915.11..79,921.79 rows=2,671 width=133) (actual time=2,470.348..2,472.518 rows=12,214 loops=1)

  • Sort Key: fielddetails.c_order, fieldschema.c_fieldschemaid, t_sys_users.c_username
  • Sort Method: external merge Disk: 1680kB
6. 3.727 2,458.324 ↓ 4.6 12,214 1

Hash Join (cost=76,394.99..79,763.09 rows=2,671 width=133) (actual time=14.464..2,458.324 rows=12,214 loops=1)

  • Hash Cond: (parentchildctedb.appfamilyid = fieldschema.c_fieldschemaid)
7. 2,440.747 2,440.747 ↑ 15.0 6,606 1

CTE Scan on parentchildctedb (cost=74,268.31..76,248.39 rows=99,004 width=306) (actual time=0.607..2,440.747 rows=6,606 loops=1)

8.          

CTE parentchildctedb

9. 2.391 2,437.888 ↑ 15.0 6,606 1

Recursive Union (cost=0.00..74,268.31 rows=99,004 width=48) (actual time=0.604..2,437.888 rows=6,606 loops=1)

10. 8.557 8.557 ↑ 28.1 159 1

Seq Scan on t_sys_fieldschema fieldschema_1 (cost=0.00..1,337.54 rows=4,464 width=48) (actual time=0.602..8.557 rows=159 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,446.156 2,426.940 ↑ 9,454.0 1 5,004

Merge Join (cost=6,730.06..7,095.07 rows=9,454 width=48) (actual time=0.479..0.485 rows=1 loops=5,004)

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

Sort (cost=4,967.66..5,079.26 rows=44,640 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,640.0 1 5,004

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

14. 967.169 975.780 ↑ 2.0 3,306 5,004

Sort (cost=1,762.40..1,779.12 rows=6,687 width=48) (actual time=0.002..0.195 rows=3,306 loops=5,004)

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

Seq Scan on t_sys_fieldschema fieldschema_2 (cost=0.00..1,337.54 rows=6,687 width=48) (actual time=0.155..8.611 rows=6,672 loops=1)

  • Filter: (c_tablegroupsid = 'e4308ac6-c388-11e7-8c54-9b97e6527505'::uuid)
  • Rows Removed by Filter: 30171
16. 0.143 13.850 ↑ 1.7 570 1

Hash (cost=2,114.26..2,114.26 rows=994 width=101) (actual time=13.850..13.850 rows=570 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 77kB
17. 0.142 13.707 ↑ 1.7 570 1

Hash Left Join (cost=1,749.39..2,114.26 rows=994 width=101) (actual time=11.178..13.707 rows=570 loops=1)

  • Hash Cond: (fieldschema.c_createdby = t_sys_users.c_userid)
18. 1.431 13.532 ↑ 1.7 570 1

Hash Right Join (cost=1,746.66..2,108.81 rows=994 width=83) (actual time=11.135..13.532 rows=570 loops=1)

  • Hash Cond: (fielddetails.c_fieldschemaid = fieldschema.c_fieldschemaid)
19. 0.984 0.984 ↑ 1.0 8,784 1

Seq Scan on t_e20so1_fielddetails fielddetails (cost=0.00..326.84 rows=8,784 width=24) (actual time=0.010..0.984 rows=8,784 loops=1)

20. 0.151 11.117 ↑ 1.8 567 1

Hash (cost=1,734.23..1,734.23 rows=994 width=75) (actual time=11.117..11.117 rows=567 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 62kB
21. 0.950 10.966 ↑ 1.8 567 1

Hash Right Join (cost=1,442.36..1,734.23 rows=994 width=75) (actual time=8.619..10.966 rows=567 loops=1)

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

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

  • Heap Fetches: 0
23. 0.063 8.583 ↑ 6.3 159 1

Hash (cost=1,429.64..1,429.64 rows=994 width=75) (actual time=8.583..8.583 rows=159 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
24. 8.520 8.520 ↑ 6.3 159 1

Seq Scan on t_sys_fieldschema fieldschema (cost=0.00..1,429.64 rows=994 width=75) (actual time=0.640..8.520 rows=159 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.014 0.033 ↑ 1.0 77 1

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

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

Seq Scan on t_sys_users (cost=0.00..1.77 rows=77 width=34) (actual time=0.006..0.019 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.47 rows=1 width=0) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.29..1,541.06 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.54 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.50 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.524 33.273 ↓ 1.1 41,591 1

Hash Left Join (cost=718.40..3,198.50 rows=36,843 width=16) (actual time=5.720..33.273 rows=41,591 loops=1)

  • Hash Cond: (f_1.c_fieldschemaid = fd_1.c_fieldschemaid)
34. 14.817 21.992 ↓ 1.1 41,571 1

Hash Left Join (cost=378.66..2,540.65 rows=36,843 width=32) (actual time=2.948..21.992 rows=41,571 loops=1)

  • Hash Cond: (f_1.c_fieldschemaid = fp_1.c_fieldschemaid)
35. 4.264 4.264 ↑ 1.0 36,843 1

Seq Scan on t_sys_fieldschema f_1 (cost=0.00..1,245.43 rows=36,843 width=32) (actual time=0.013..4.264 rows=36,843 loops=1)

36. 1.242 2.911 ↑ 1.0 8,759 1

Hash (cost=269.17..269.17 rows=8,759 width=16) (actual time=2.911..2.911 rows=8,759 loops=1)

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

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

  • Heap Fetches: 0
38. 1.377 2.757 ↑ 1.0 8,784 1

Hash (cost=229.94..229.94 rows=8,784 width=16) (actual time=2.757..2.757 rows=8,784 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 540kB
39. 1.380 1.380 ↑ 1.0 8,784 1

Index Only Scan using t_e20so1_fielddetails_c_fieldschemaid_idx on t_e20so1_fielddetails fd_1 (cost=0.29..229.94 rows=8,784 width=16) (actual time=0.011..1.380 rows=8,784 loops=1)

  • Heap Fetches: 0
40. 0.795 30.210 ↓ 1.5 3 159

Nested Loop Left Join (cost=0.29..207.35 rows=2 width=32) (actual time=0.061..0.190 rows=3 loops=159)

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

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

  • 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.014 0.014 ↑ 1.0 15 1

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

44. 0.636 28.938 ↓ 1.5 3 159

Nested Loop Left Join (cost=0.29..205.13 rows=2 width=10) (actual time=0.059..0.182 rows=3 loops=159)

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

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

  • Index Cond: (c_fieldschemaid = fieldschema.c_fieldschemaid)
  • Heap Fetches: 0
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.006 0.006 ↑ 1.0 15 1

Seq Scan on t_sys_roles r_1 (cost=0.00..1.15 rows=15 width=18) (actual time=0.003..0.006 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.50 rows=2 width=0) (never executed)

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

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

  • Heap Fetches: 0