explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8Meb

Settings
# exclusive inclusive rows x rows loops node
1. 0.287 23.827 ↓ 1,066.0 1,066 1

Group (cost=7,718.15..7,718.57 rows=1 width=4) (actual time=12.069..23.827 rows=1,066 loops=1)

  • Group Key: t_2.id
2.          

CTE permitted_territories

3. 0.032 0.791 ↓ 2.1 23 1

Recursive Union (cost=17.11..3,704.94 rows=11 width=56) (actual time=0.202..0.791 rows=23 loops=1)

4. 0.002 0.201 ↑ 1.0 1 1

Result (cost=17.11..33.96 rows=1 width=56) (actual time=0.199..0.201 rows=1 loops=1)

  • One-Time Filter: $5
5.          

Initplan (forResult)

6. 0.004 0.069 ↑ 1.0 1 1

Nested Loop (cost=0.00..9.03 rows=1 width=0) (actual time=0.069..0.069 rows=1 loops=1)

  • Join Filter: (t_8.roleid = t1_2.roleid)
7. 0.034 0.034 ↑ 1.0 1 1

Seq Scan on urprivs t_8 (cost=0.00..7.64 rows=1 width=5) (actual time=0.034..0.034 rows=1 loops=1)

  • Filter: ((lpu IS NULL) AND (version IS NULL) AND ((unitcode)::text = 'permisions'::text))
  • Rows Removed by Filter: 172
8. 0.031 0.031 ↑ 1.0 1 1

Seq Scan on userroles t1_2 (cost=0.00..1.38 rows=1 width=6) (actual time=0.031..0.031 rows=1 loops=1)

  • Filter: (sysuser = (f_sys_get_config('sysuser'::text))::numeric)
9. 0.004 0.130 ↑ 1.0 1 1

Nested Loop (cost=17.11..33.96 rows=1 width=56) (actual time=0.129..0.130 rows=1 loops=1)

10. 0.003 0.066 ↑ 1.0 1 1

HashAggregate (cost=7.93..7.94 rows=1 width=4) (actual time=0.066..0.066 rows=1 loops=1)

  • Group Key: (t_12.f_cat_id)::numeric
11. 0.011 0.063 ↑ 1.0 1 1

Hash Join (cost=6.58..7.93 rows=1 width=4) (actual time=0.061..0.063 rows=1 loops=1)

  • Hash Cond: (t1_5.id = (t_12.f_pid)::numeric)
12. 0.020 0.020 ↑ 1.0 23 1

Seq Scan on users t1_5 (cost=0.00..1.23 rows=23 width=5) (actual time=0.020..0.020 rows=23 loops=1)

13. 0.002 0.032 ↑ 1.0 1 1

Hash (cost=6.56..6.56 rows=1 width=8) (actual time=0.032..0.032 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.030 0.030 ↑ 1.0 1 1

Seq Scan on permisions t_12 (cost=0.00..6.56 rows=1 width=8) (actual time=0.028..0.030 rows=1 loops=1)

  • Filter: ((f_org_id IS NULL) AND (f_pid = 1352))
  • Rows Removed by Filter: 286
15. 0.015 0.060 ↑ 1.0 1 1

Index Scan using pk_catalogs on catalogs t_11 (cost=0.14..16.98 rows=1 width=56) (actual time=0.059..0.060 rows=1 loops=1)

  • Index Cond: (id = (t_12.f_cat_id)::numeric)
  • Filter: (((c_unitcode)::text ~~ '%orgreestr%'::text) AND (CASE WHEN (alternatives: SubPlan 1 or hashed SubPlan 2) THEN 1 ELSE NULL::integer END = 1))
16.          

SubPlan (forIndex Scan)

17. 0.002 0.045 ↑ 1.0 1 1

Nested Loop (cost=4.29..8.81 rows=1 width=0) (actual time=0.045..0.045 rows=1 loops=1)

  • Join Filter: (t_6.roleid = t1.roleid)
18. 0.003 0.010 ↑ 1.0 1 1

Bitmap Heap Scan on urprivs t_6 (cost=4.29..7.42 rows=1 width=5) (actual time=0.010..0.010 rows=1 loops=1)

  • Recheck Cond: (catalog = t_11.id)
  • Filter: ((unitcode)::text = (t_11.c_unitcode)::text)
  • Heap Blocks: exact=1
19. 0.007 0.007 ↑ 2.0 1 1

Bitmap Index Scan on i_urprivs_cat (cost=0.00..4.29 rows=2 width=0) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: (catalog = t_11.id)
20. 0.033 0.033 ↑ 1.0 1 1

Seq Scan on userroles t1 (cost=0.00..1.38 rows=1 width=6) (actual time=0.033..0.033 rows=1 loops=1)

  • Filter: (sysuser = (f_sys_get_config('sysuser'::text))::numeric)
21. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1.39..10.58 rows=109 width=37) (never executed)

  • Hash Cond: (t_7.roleid = t1_1.roleid)
22. 0.000 0.000 ↓ 0.0 0

Seq Scan on urprivs t_7 (cost=0.00..6.71 rows=371 width=21) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.38..1.38 rows=1 width=6) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Seq Scan on userroles t1_1 (cost=0.00..1.38 rows=1 width=6) (never executed)

  • Filter: (sysuser = (f_sys_get_config('sysuser'::text))::numeric)
25. 0.026 0.558 ↓ 7.0 7 3

Nested Loop (cost=0.14..367.08 rows=1 width=56) (actual time=0.034..0.186 rows=7 loops=3)

26. 0.003 0.003 ↑ 1.2 8 3

WorkTable Scan on permitted_territories d (cost=0.00..0.20 rows=10 width=18) (actual time=0.000..0.001 rows=8 loops=3)

27. 0.067 0.529 ↑ 1.0 1 23

Index Scan using i_catalogs_piid on catalogs t_13 (cost=0.14..36.68 rows=1 width=56) (actual time=0.004..0.023 rows=1 loops=23)

  • Index Cond: (pid = d.id)
  • Filter: (((c_unitcode)::text ~~ '%orgreestr%'::text) AND (CASE WHEN (alternatives: SubPlan 4 or hashed SubPlan 5) THEN 1 ELSE NULL::integer END = 1))
28.          

SubPlan (forIndex Scan)

29. 0.044 0.462 ↑ 1.0 1 22

Nested Loop (cost=4.29..8.81 rows=1 width=0) (actual time=0.021..0.021 rows=1 loops=22)

  • Join Filter: (t_9.roleid = t1_3.roleid)
30. 0.022 0.088 ↑ 1.0 1 22

Bitmap Heap Scan on urprivs t_9 (cost=4.29..7.42 rows=1 width=5) (actual time=0.004..0.004 rows=1 loops=22)

  • Recheck Cond: (catalog = t_13.id)
  • Filter: ((unitcode)::text = (t_13.c_unitcode)::text)
  • Heap Blocks: exact=22
31. 0.066 0.066 ↑ 2.0 1 22

Bitmap Index Scan on i_urprivs_cat (cost=0.00..4.29 rows=2 width=0) (actual time=0.003..0.003 rows=1 loops=22)

  • Index Cond: (catalog = t_13.id)
32. 0.330 0.330 ↑ 1.0 1 22

Seq Scan on userroles t1_3 (cost=0.00..1.38 rows=1 width=6) (actual time=0.015..0.015 rows=1 loops=22)

  • Filter: (sysuser = (f_sys_get_config('sysuser'::text))::numeric)
33. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1.39..10.58 rows=109 width=37) (never executed)

  • Hash Cond: (t_10.roleid = t1_4.roleid)
34. 0.000 0.000 ↓ 0.0 0

Seq Scan on urprivs t_10 (cost=0.00..6.71 rows=371 width=21) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.38..1.38 rows=1 width=6) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Seq Scan on userroles t1_4 (cost=0.00..1.38 rows=1 width=6) (never executed)

  • Filter: (sysuser = (f_sys_get_config('sysuser'::text))::numeric)
37.          

CTE permitted_organization

38. 0.001 0.096 ↓ 0.0 0 1

Result (cost=15.61..16.96 rows=1 width=12) (actual time=0.096..0.096 rows=0 loops=1)

  • One-Time Filter: $13
39.          

Initplan (forResult)

40. 0.002 0.050 ↑ 1.0 1 1

Nested Loop (cost=0.00..9.03 rows=1 width=0) (actual time=0.050..0.050 rows=1 loops=1)

  • Join Filter: (t_14.roleid = t1_6.roleid)
41. 0.029 0.029 ↑ 1.0 1 1

Seq Scan on urprivs t_14 (cost=0.00..7.64 rows=1 width=5) (actual time=0.029..0.029 rows=1 loops=1)

  • Filter: ((lpu IS NULL) AND (version IS NULL) AND ((unitcode)::text = 'permisions'::text))
  • Rows Removed by Filter: 172
42. 0.019 0.019 ↑ 1.0 1 1

Seq Scan on userroles t1_6 (cost=0.00..1.38 rows=1 width=6) (actual time=0.019..0.019 rows=1 loops=1)

  • Filter: (sysuser = (f_sys_get_config('sysuser'::text))::numeric)
43. 0.006 0.045 ↓ 0.0 0 1

Hash Join (cost=15.61..16.96 rows=1 width=12) (actual time=0.045..0.045 rows=0 loops=1)

  • Hash Cond: (t1_7.id = (t_15.f_pid)::numeric)
44. 0.006 0.006 ↑ 23.0 1 1

Seq Scan on users t1_7 (cost=0.00..1.23 rows=23 width=5) (actual time=0.006..0.006 rows=1 loops=1)

45. 0.000 0.033 ↓ 0.0 0 1

Hash (cost=6.56..6.56 rows=1 width=16) (actual time=0.033..0.033 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
46. 0.033 0.033 ↓ 0.0 0 1

Seq Scan on permisions t_15 (cost=0.00..6.56 rows=1 width=16) (actual time=0.033..0.033 rows=0 loops=1)

  • Filter: ((f_org_id IS NOT NULL) AND (f_pid = 1352))
  • Rows Removed by Filter: 287
47.          

CTE territories

48. 0.039 0.726 ↓ 2.1 23 1

Recursive Union (cost=0.17..3,693.39 rows=11 width=88) (actual time=0.060..0.726 rows=23 loops=1)

49. 0.017 0.057 ↑ 1.0 1 1

Index Scan using pk_catalogs on catalogs t_20 (cost=0.17..22.36 rows=1 width=88) (actual time=0.056..0.057 rows=1 loops=1)

  • Index Cond: (id = ANY ((('{1481}'::text[])::integer[])::numeric[]))
  • Filter: (((c_unitcode)::text ~~ '%orgreestr%'::text) AND (CASE WHEN (alternatives: SubPlan 9 or hashed SubPlan 10) THEN 1 ELSE NULL::integer END = 1))
50.          

SubPlan (forIndex Scan)

51. 0.004 0.040 ↑ 1.0 1 1

Nested Loop (cost=4.29..8.81 rows=1 width=0) (actual time=0.040..0.040 rows=1 loops=1)

  • Join Filter: (t_16.roleid = t1_8.roleid)
52. 0.003 0.006 ↑ 1.0 1 1

Bitmap Heap Scan on urprivs t_16 (cost=4.29..7.42 rows=1 width=5) (actual time=0.006..0.006 rows=1 loops=1)

  • Recheck Cond: (catalog = t_20.id)
  • Filter: ((unitcode)::text = (t_20.c_unitcode)::text)
  • Heap Blocks: exact=1
53. 0.003 0.003 ↑ 2.0 1 1

Bitmap Index Scan on i_urprivs_cat (cost=0.00..4.29 rows=2 width=0) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (catalog = t_20.id)
54. 0.030 0.030 ↑ 1.0 1 1

Seq Scan on userroles t1_8 (cost=0.00..1.38 rows=1 width=6) (actual time=0.030..0.030 rows=1 loops=1)

  • Filter: (sysuser = (f_sys_get_config('sysuser'::text))::numeric)
55. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1.39..10.58 rows=109 width=37) (never executed)

  • Hash Cond: (t_17.roleid = t1_9.roleid)
56. 0.000 0.000 ↓ 0.0 0

Seq Scan on urprivs t_17 (cost=0.00..6.71 rows=371 width=21) (never executed)

57. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.38..1.38 rows=1 width=6) (never executed)

58. 0.000 0.000 ↓ 0.0 0

Seq Scan on userroles t1_9 (cost=0.00..1.38 rows=1 width=6) (never executed)

  • Filter: (sysuser = (f_sys_get_config('sysuser'::text))::numeric)
59. 0.052 0.630 ↓ 7.0 7 3

Nested Loop (cost=0.14..367.08 rows=1 width=88) (actual time=0.031..0.210 rows=7 loops=3)

60. 0.003 0.003 ↑ 1.2 8 3

WorkTable Scan on territories d_1 (cost=0.00..0.20 rows=10 width=50) (actual time=0.000..0.001 rows=8 loops=3)

61. 0.069 0.575 ↑ 1.0 1 23

Index Scan using i_catalogs_piid on catalogs t_21 (cost=0.14..36.68 rows=1 width=56) (actual time=0.004..0.025 rows=1 loops=23)

  • Index Cond: (pid = d_1.id)
  • Filter: (((c_unitcode)::text ~~ '%orgreestr%'::text) AND (CASE WHEN (alternatives: SubPlan 11 or hashed SubPlan 12) THEN 1 ELSE NULL::integer END = 1))
62.          

SubPlan (forIndex Scan)

63. 0.044 0.506 ↑ 1.0 1 22

Nested Loop (cost=4.29..8.81 rows=1 width=0) (actual time=0.023..0.023 rows=1 loops=22)

  • Join Filter: (t_18.roleid = t1_10.roleid)
64. 0.022 0.088 ↑ 1.0 1 22

Bitmap Heap Scan on urprivs t_18 (cost=4.29..7.42 rows=1 width=5) (actual time=0.004..0.004 rows=1 loops=22)

  • Recheck Cond: (catalog = t_21.id)
  • Filter: ((unitcode)::text = (t_21.c_unitcode)::text)
  • Heap Blocks: exact=22
65. 0.066 0.066 ↑ 2.0 1 22

Bitmap Index Scan on i_urprivs_cat (cost=0.00..4.29 rows=2 width=0) (actual time=0.003..0.003 rows=1 loops=22)

  • Index Cond: (catalog = t_21.id)
66. 0.374 0.374 ↑ 1.0 1 22

Seq Scan on userroles t1_10 (cost=0.00..1.38 rows=1 width=6) (actual time=0.017..0.017 rows=1 loops=22)

  • Filter: (sysuser = (f_sys_get_config('sysuser'::text))::numeric)
67. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1.39..10.58 rows=109 width=37) (never executed)

  • Hash Cond: (t_19.roleid = t1_11.roleid)
68. 0.000 0.000 ↓ 0.0 0

Seq Scan on urprivs t_19 (cost=0.00..6.71 rows=371 width=21) (never executed)

69. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.38..1.38 rows=1 width=6) (never executed)

70. 0.000 0.000 ↓ 0.0 0

Seq Scan on userroles t1_11 (cost=0.00..1.38 rows=1 width=6) (never executed)

  • Filter: (sysuser = (f_sys_get_config('sysuser'::text))::numeric)
71. 5.725 23.540 ↓ 1,284.0 1,284 1

Nested Loop (cost=302.87..303.28 rows=1 width=4) (actual time=12.068..23.540 rows=1,284 loops=1)

  • Join Filter: (t._t && ((array_agg(DISTINCT t_3.cid))::integer[]))
  • Rows Removed by Join Filter: 23234
72. 2.520 14.617 ↓ 1,066.0 1,066 1

GroupAggregate (cost=302.87..302.91 rows=1 width=36) (actual time=11.927..14.617 rows=1,066 loops=1)

  • Group Key: t_2.id
73. 0.790 12.097 ↓ 1,996.0 1,996 1

Sort (cost=302.87..302.87 rows=1 width=12) (actual time=11.913..12.097 rows=1,996 loops=1)

  • Sort Key: t_2.id
  • Sort Method: quicksort Memory: 142kB
74. 0.485 11.307 ↓ 1,996.0 1,996 1

Nested Loop (cost=1.37..302.86 rows=1 width=12) (actual time=0.956..11.307 rows=1,996 loops=1)

75. 1.841 6.830 ↓ 1,996.0 1,996 1

Nested Loop (cost=1.09..302.42 rows=1 width=24) (actual time=0.950..6.830 rows=1,996 loops=1)

76. 1.881 3.232 ↓ 1,757.0 1,757 1

Hash Join (cost=0.81..301.48 rows=1 width=8) (actual time=0.945..3.232 rows=1,757 loops=1)

  • Hash Cond: ((t_1.cid)::numeric = t_4.id)
  • Join Filter: (COALESCE((NULL::bigint), t_1.id) = t_1.id)
77. 0.419 0.419 ↑ 1.0 1,757 1

Seq Scan on m_organization t_1 (cost=0.00..290.57 rows=1,757 width=24) (actual time=0.005..0.419 rows=1,757 loops=1)

78. 0.003 0.932 ↓ 1.9 23 1

Hash (cost=0.66..0.66 rows=12 width=40) (actual time=0.932..0.932 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
79. 0.020 0.929 ↓ 1.9 23 1

HashAggregate (cost=0.42..0.54 rows=12 width=40) (actual time=0.924..0.929 rows=23 loops=1)

  • Group Key: t_4.id, (NULL::bigint)
80. 0.004 0.909 ↓ 1.9 23 1

Append (cost=0.00..0.36 rows=12 width=40) (actual time=0.204..0.909 rows=23 loops=1)

81. 0.809 0.809 ↓ 2.1 23 1

CTE Scan on permitted_territories t_4 (cost=0.00..0.22 rows=11 width=26) (actual time=0.203..0.809 rows=23 loops=1)

82. 0.000 0.096 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.00..0.03 rows=1 width=40) (actual time=0.096..0.096 rows=0 loops=1)

83. 0.096 0.096 ↓ 0.0 0 1

CTE Scan on permitted_organization t_5 (cost=0.00..0.02 rows=1 width=12) (actual time=0.096..0.096 rows=0 loops=1)

84. 1.757 1.757 ↑ 1.0 1 1,757

Index Scan using m_organization_f_link2reestr_idx on m_organization t_3 (cost=0.28..0.93 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=1,757)

  • Index Cond: (f_link2reestr = t_1.f_link2reestr)
85. 3.992 3.992 ↑ 1.0 1 1,996

Index Only Scan using m_orgreestr_id_idx on m_orgreestr t_2 (cost=0.28..0.42 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1,996)

  • Index Cond: (id = t_1.f_link2reestr)
  • Heap Fetches: 1996
86. 3.198 3.198 ↓ 2.1 23 1,066

CTE Scan on territories t (cost=0.00..0.22 rows=11 width=32) (actual time=0.000..0.003 rows=23 loops=1,066)