explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NexP

Settings
# exclusive inclusive rows x rows loops node
1. 3,307.600 7,010.997 ↑ 1.0 54,024 1

Nested Loop Left Join (cost=2,694.45..8,954,357.30 rows=54,024 width=1,000) (actual time=736.374..7,010.997 rows=54,024 loops=1)

2. 0.380 898.157 ↑ 1.0 1 1

Nested Loop Left Join (cost=2,694.45..53,349.13 rows=1 width=853) (actual time=735.674..898.157 rows=1 loops=1)

  • Join Filter: (t_14.pid = t.id)
  • Rows Removed by Join Filter: 2,523
3. 0.006 885.639 ↑ 1.0 1 1

Nested Loop Left Join (cost=2,641.07..51,114.36 rows=1 width=718) (actual time=723.765..885.639 rows=1 loops=1)

4. 0.005 885.505 ↑ 1.0 1 1

Nested Loop Left Join (cost=2,626.19..51,093.68 rows=1 width=713) (actual time=723.635..885.505 rows=1 loops=1)

5. 0.742 885.492 ↑ 1.0 1 1

Nested Loop Left Join (cost=2,611.72..51,070.38 rows=1 width=713) (actual time=723.622..885.492 rows=1 loops=1)

  • Join Filter: (p.pid = t.id)
  • Rows Removed by Join Filter: 4,903
6. 9.733 857.413 ↑ 1.0 1 1

Nested Loop Left Join (cost=2,576.67..47,808.27 rows=1 width=636) (actual time=707.527..857.413 rows=1 loops=1)

  • Join Filter: (t_11.pid = t.id)
  • Rows Removed by Join Filter: 54,821
7. 8.533 480.632 ↑ 1.0 1 1

Nested Loop Left Join (cost=2,557.32..3,764.43 rows=1 width=286) (actual time=385.495..480.632 rows=1 loops=1)

  • Join Filter: (t_9.pid = t.id)
  • Rows Removed by Join Filter: 53,005
8. 0.003 0.496 ↑ 1.0 1 1

Nested Loop Left Join (cost=44.83..89.59 rows=1 width=222) (actual time=0.476..0.496 rows=1 loops=1)

9. 0.003 0.333 ↑ 1.0 1 1

Nested Loop Semi Join (cost=29.56..65.15 rows=1 width=204) (actual time=0.326..0.333 rows=1 loops=1)

10. 0.002 0.228 ↑ 1.0 1 1

Nested Loop (cost=15.22..50.77 rows=1 width=211) (actual time=0.222..0.228 rows=1 loops=1)

11. 0.025 0.222 ↑ 1.0 1 1

Nested Loop (cost=14.93..42.45 rows=1 width=1,851) (actual time=0.216..0.222 rows=1 loops=1)

12. 0.004 0.187 ↑ 1.0 1 1

Nested Loop Semi Join (cost=14.64..32.85 rows=1 width=136) (actual time=0.182..0.187 rows=1 loops=1)

13. 0.003 0.048 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.29..10.47 rows=1 width=143) (actual time=0.043..0.048 rows=1 loops=1)

  • Join Filter: (bg.id = pmc.bloodgroupe)
  • Rows Removed by Join Filter: 4
14. 0.004 0.040 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.29..9.38 rows=1 width=103) (actual time=0.036..0.040 rows=1 loops=1)

  • Join Filter: (r.rh_code = pmc.rhesus)
  • Rows Removed by Join Filter: 3
15. 0.026 0.026 ↑ 1.0 1 1

Index Scan using pk_d_persmedcard on d_persmedcard pmc (cost=0.29..8.31 rows=1 width=89) (actual time=0.022..0.026 rows=1 loops=1)

  • Index Cond: (id = '19100441'::numeric(17,0))
  • Filter: (lpu = '10903'::numeric(17,0))
16. 0.010 0.010 ↑ 1.0 3 1

Seq Scan on d_rhesus r (cost=0.00..1.03 rows=3 width=48) (actual time=0.009..0.010 rows=3 loops=1)

17. 0.005 0.005 ↑ 1.0 4 1

Seq Scan on d_bloodgroupe bg (cost=0.00..1.04 rows=4 width=76) (actual time=0.005..0.005 rows=4 loops=1)

18. 0.049 0.135 ↑ 1.0 1 1

Index Only Scan using uk_d_urprivs on d_urprivs t_8 (cost=14.35..22.38 rows=1 width=7) (actual time=0.135..0.135 rows=1 loops=1)

  • Index Cond: ((unitcode = 'PERSMEDCARD'::text) AND (catalog = pmc.cid))
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 14))
  • Heap Fetches: 1
19.          

SubPlan (for Index Only Scan)

20. 0.009 0.086 ↓ 7.0 35 1

Nested Loop (cost=4.32..13.92 rows=5 width=6) (actual time=0.044..0.086 rows=35 loops=1)

21. 0.045 0.045 ↑ 1.0 1 1

Seq Scan on d_users us_9 (cost=0.00..4.20 rows=1 width=7) (actual time=0.020..0.045 rows=1 loops=1)

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
22. 0.018 0.032 ↓ 5.8 35 1

Bitmap Heap Scan on d_userroles ur_9 (cost=4.32..9.66 rows=6 width=13) (actual time=0.021..0.032 rows=35 loops=1)

  • Recheck Cond: (sysuser = us_9.id)
  • Heap Blocks: exact=4
23. 0.014 0.014 ↓ 5.8 35 1

Bitmap Index Scan on i_d_userroles_u (cost=0.00..4.32 rows=6 width=0) (actual time=0.014..0.014 rows=35 loops=1)

  • Index Cond: (sysuser = us_9.id)
24. 0.010 0.010 ↑ 1.0 1 1

Index Scan using pk_d_agents on d_agents ag (cost=0.29..8.31 rows=1 width=9) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: (id = pmc.agent)
25. 0.004 0.004 ↑ 1.0 1 1

Index Scan using pk_d_agents on d_agents t (cost=0.29..8.31 rows=1 width=166) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: (id = pmc.agent)
26. 0.035 0.102 ↑ 1.0 1 1

Index Only Scan using uk_d_urprivs on d_urprivs t_1 (cost=14.35..14.40 rows=1 width=7) (actual time=0.102..0.102 rows=1 loops=1)

  • Index Cond: ((unitcode = 'AGENTS'::text) AND (catalog = t.cid))
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 10))
  • Heap Fetches: 1
27.          

SubPlan (for Index Only Scan)

28. 0.007 0.067 ↓ 7.0 35 1

Nested Loop (cost=4.32..13.92 rows=5 width=6) (actual time=0.031..0.067 rows=35 loops=1)

29. 0.040 0.040 ↑ 1.0 1 1

Seq Scan on d_users us_5 (cost=0.00..4.20 rows=1 width=7) (actual time=0.016..0.040 rows=1 loops=1)

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
30. 0.010 0.020 ↓ 5.8 35 1

Bitmap Heap Scan on d_userroles ur_5 (cost=4.32..9.66 rows=6 width=13) (actual time=0.013..0.020 rows=35 loops=1)

  • Recheck Cond: (sysuser = us_5.id)
  • Heap Blocks: exact=4
31. 0.010 0.010 ↓ 5.8 35 1

Bitmap Index Scan on i_d_userroles_u (cost=0.00..4.32 rows=6 width=0) (actual time=0.010..0.010 rows=35 loops=1)

  • Index Cond: (sysuser = us_5.id)
32. 0.002 0.160 ↑ 1.0 1 1

Nested Loop Semi Join (cost=15.27..24.43 rows=1 width=36) (actual time=0.148..0.160 rows=1 loops=1)

33. 0.014 0.057 ↑ 1.0 1 1

Hash Join (cost=0.92..2.04 rows=1 width=54) (actual time=0.045..0.057 rows=1 loops=1)

  • Hash Cond: (t1.id = t_2.marital_state)
34. 0.011 0.011 ↑ 1.0 8 1

Seq Scan on d_maritalstates t1 (cost=0.00..1.08 rows=8 width=18) (actual time=0.005..0.011 rows=8 loops=1)

35. 0.004 0.032 ↑ 1.0 1 1

Hash (cost=0.91..0.91 rows=1 width=54) (actual time=0.031..0.032 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
36. 0.028 0.028 ↑ 1.0 1 1

Index Scan using i_d_agent_marital_states_pid on d_agent_marital_states t_2 (cost=0.13..0.91 rows=1 width=54) (actual time=0.027..0.028 rows=1 loops=1)

  • Index Cond: (pid = t.id)
  • Filter: ((begin_date <= (sysdate())::timestamp(0) without time zone) AND ((end_date >= sysdate()) OR (nullif2(end_date) IS NULL)))
37. 0.033 0.101 ↑ 1.0 1 1

Index Only Scan using uk_d_urprivs on d_urprivs t_3 (cost=14.35..22.38 rows=1 width=7) (actual time=0.101..0.101 rows=1 loops=1)

  • Index Cond: ((unitcode = 'AGENT_MARITAL_STATES'::text) AND (catalog = t_2.cid))
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 11))
  • Heap Fetches: 1
38.          

SubPlan (for Index Only Scan)

39. 0.007 0.068 ↓ 7.0 35 1

Nested Loop (cost=4.32..13.92 rows=5 width=6) (actual time=0.032..0.068 rows=35 loops=1)

40. 0.040 0.040 ↑ 1.0 1 1

Seq Scan on d_users us_6 (cost=0.00..4.20 rows=1 width=7) (actual time=0.016..0.040 rows=1 loops=1)

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
41. 0.011 0.021 ↓ 5.8 35 1

Bitmap Heap Scan on d_userroles ur_6 (cost=4.32..9.66 rows=6 width=13) (actual time=0.013..0.021 rows=35 loops=1)

  • Recheck Cond: (sysuser = us_6.id)
  • Heap Blocks: exact=4
42. 0.010 0.010 ↓ 5.8 35 1

Bitmap Index Scan on i_d_userroles_u (cost=0.00..4.32 rows=6 width=0) (actual time=0.010..0.010 rows=35 loops=1)

  • Index Cond: (sysuser = us_6.id)
43. 168.653 471.603 ↓ 70.0 53,006 1

HashAggregate (cost=2,512.49..3,657.81 rows=757 width=273) (actual time=349.836..471.603 rows=53,006 loops=1)

  • Group Key: t_9.pid, t_9.version
  • Filter: ((NULL::text >= sysdate()) OR (nullif2(NULL::text) IS NULL))
44. 7.130 302.950 ↓ 36.6 55,109 1

Result (cost=182.69..2,478.58 rows=1,507 width=54) (actual time=0.406..302.950 rows=55,109 loops=1)

  • One-Time Filter: (((trunc(sysdate(), 'DDD'::text))::date - 1) <= sysdate())
45. 6.503 295.820 ↓ 36.6 55,109 1

Nested Loop (cost=182.69..2,478.58 rows=1,507 width=54) (actual time=0.394..295.820 rows=55,109 loops=1)

46. 61.465 123.990 ↓ 36.6 55,109 1

Nested Loop (cost=181.89..1,821.20 rows=1,507 width=50) (actual time=0.384..123.990 rows=55,109 loops=1)

  • Join Filter: (ct.id = t_9.contact_type)
  • Rows Removed by Join Filter: 220,436
47. 0.017 0.375 ↓ 10.0 50 1

Nested Loop (cost=181.60..187.16 rows=5 width=49) (actual time=0.332..0.375 rows=50 loops=1)

48. 0.052 0.328 ↑ 6.2 10 1

HashAggregate (cost=181.60..182.22 rows=62 width=7) (actual time=0.320..0.328 rows=10 loops=1)

  • Group Key: t_10.catalog
49. 0.169 0.276 ↓ 1.7 120 1

Bitmap Heap Scan on d_urprivs t_10 (cost=19.21..181.43 rows=69 width=7) (actual time=0.133..0.276 rows=120 loops=1)

  • Recheck Cond: ((unitcode)::text = 'AGENT_CONTACTS'::text)
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 19))
  • Rows Removed by Filter: 10
  • Heap Blocks: exact=29
50. 0.024 0.024 ↑ 1.0 130 1

Bitmap Index Scan on i_d_urprivs_ulr (cost=0.00..5.26 rows=130 width=0) (actual time=0.024..0.024 rows=130 loops=1)

  • Index Cond: ((unitcode)::text = 'AGENT_CONTACTS'::text)
51.          

SubPlan (for Bitmap Heap Scan)

52. 0.007 0.083 ↓ 7.0 35 1

Nested Loop (cost=4.32..13.92 rows=5 width=6) (actual time=0.031..0.083 rows=35 loops=1)

53. 0.056 0.056 ↑ 1.0 1 1

Seq Scan on d_users us_10 (cost=0.00..4.20 rows=1 width=7) (actual time=0.016..0.056 rows=1 loops=1)

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
54. 0.010 0.020 ↓ 5.8 35 1

Bitmap Heap Scan on d_userroles ur_10 (cost=4.32..9.66 rows=6 width=13) (actual time=0.013..0.020 rows=35 loops=1)

  • Recheck Cond: (sysuser = us_10.id)
  • Heap Blocks: exact=4
55. 0.010 0.010 ↓ 5.8 35 1

Bitmap Index Scan on i_d_userroles_u (cost=0.00..4.32 rows=6 width=0) (actual time=0.010..0.010 rows=35 loops=1)

  • Index Cond: (sysuser = us_10.id)
56. 0.021 0.030 ↑ 1.0 5 10

Materialize (cost=0.00..1.07 rows=5 width=42) (actual time=0.001..0.003 rows=5 loops=10)

57. 0.009 0.009 ↑ 1.0 5 1

Seq Scan on d_contact_types ct (cost=0.00..1.05 rows=5 width=42) (actual time=0.008..0.009 rows=5 loops=1)

58. 62.150 62.150 ↑ 1.7 5,511 50

Index Scan using i_d_agent_contacts_cid on d_agent_contacts t_9 (cost=0.29..212.00 rows=9,185 width=40) (actual time=0.006..1.243 rows=5,511 loops=50)

  • Index Cond: (cid = t_10.catalog)
59. 165.327 165.327 ↑ 1.0 1 55,109

Index Scan using pk_d_agents on d_agents a (cost=0.29..0.43 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=55,109)

  • Index Cond: (id = t_9.pid)
60. 130.964 367.048 ↓ 199.4 54,822 1

Nested Loop Left Join (cost=19.35..44,037.65 rows=275 width=1,188) (actual time=0.277..367.048 rows=54,822 loops=1)

61. 62.386 181.262 ↓ 199.4 54,822 1

Nested Loop Semi Join (cost=19.21..43,912.77 rows=275 width=29) (actual time=0.234..181.262 rows=54,822 loops=1)

  • Join Filter: (t_11.cid = t_12.catalog)
  • Rows Removed by Join Filter: 222,157
62. 118.876 118.876 ↓ 199.4 54,822 1

Seq Scan on d_agent_persdocs t_11 (cost=0.00..43,532.99 rows=275 width=43) (actual time=0.033..118.876 rows=54,822 loops=1)

  • Filter: ((is_main = '1'::numeric(1,0)) AND (period_begin <= (sysdate())::timestamp(0) without time zone) AND ((period_end >= sysdate()) OR (nullif2(period_end) IS NULL)))
  • Rows Removed by Filter: 165
63. 0.000 0.000 ↑ 13.8 5 54,822

Materialize (cost=19.21..181.77 rows=69 width=7) (actual time=0.000..0.000 rows=5 loops=54,822)

64. 0.080 0.199 ↑ 9.9 7 1

Bitmap Heap Scan on d_urprivs t_12 (cost=19.21..181.43 rows=69 width=7) (actual time=0.171..0.199 rows=7 loops=1)

  • Recheck Cond: ((unitcode)::text = 'AGENT_PERSDOCS'::text)
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 21))
  • Heap Blocks: exact=5
65. 0.037 0.037 ↑ 1.0 130 1

Bitmap Index Scan on i_d_urprivs_ulr (cost=0.00..5.26 rows=130 width=0) (actual time=0.037..0.037 rows=130 loops=1)

  • Index Cond: ((unitcode)::text = 'AGENT_PERSDOCS'::text)
66.          

SubPlan (for Bitmap Heap Scan)

67. 0.010 0.082 ↓ 7.0 35 1

Nested Loop (cost=4.32..13.92 rows=5 width=6) (actual time=0.045..0.082 rows=35 loops=1)

68. 0.043 0.043 ↑ 1.0 1 1

Seq Scan on d_users us_12 (cost=0.00..4.20 rows=1 width=7) (actual time=0.021..0.043 rows=1 loops=1)

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
69. 0.016 0.029 ↓ 5.8 35 1

Bitmap Heap Scan on d_userroles ur_12 (cost=4.32..9.66 rows=6 width=13) (actual time=0.018..0.029 rows=35 loops=1)

  • Recheck Cond: (sysuser = us_12.id)
  • Heap Blocks: exact=4
70. 0.013 0.013 ↓ 5.8 35 1

Bitmap Index Scan on i_d_userroles_u (cost=0.00..4.32 rows=6 width=0) (actual time=0.013..0.013 rows=35 loops=1)

  • Index Cond: (sysuser = us_12.id)
71. 54.822 54.822 ↑ 1.0 1 54,822

Index Scan using pk_d_persdoctypes on d_persdoctypes t1_1 (cost=0.14..0.20 rows=1 width=356) (actual time=0.001..0.001 rows=1 loops=54,822)

  • Index Cond: (id = t_11.pd_type)
72. 1.142 27.337 ↓ 188.6 4,904 1

Nested Loop (cost=35.05..3,261.53 rows=26 width=913) (actual time=0.325..27.337 rows=4,904 loops=1)

73. 7.018 16.387 ↓ 188.6 4,904 1

Nested Loop Semi Join (cost=34.76..3,113.76 rows=26 width=34) (actual time=0.312..16.387 rows=4,904 loops=1)

  • Join Filter: (p.cid = t_13.catalog)
  • Rows Removed by Join Filter: 34,338
74. 1.817 9.369 ↓ 188.6 4,904 1

Hash Join (cost=15.47..2,910.06 rows=26 width=41) (actual time=0.181..9.369 rows=4,904 loops=1)

  • Hash Cond: (p.p_who = ic.id)
75. 7.414 7.414 ↓ 188.6 4,904 1

Index Scan using i_d_agent_polis_pid on d_agent_polis p (cost=0.54..2,895.06 rows=26 width=46) (actual time=0.031..7.414 rows=4,904 loops=1)

  • Index Cond: ((p_type = '0'::numeric(1,0)) AND (p_begin <= (sysdate())::timestamp(0) without time zone))
  • Filter: ((p_end >= sysdate()) OR (nullif2(p_end) IS NULL))
  • Rows Removed by Filter: 12
76. 0.057 0.138 ↑ 1.0 397 1

Hash (cost=9.97..9.97 rows=397 width=14) (actual time=0.138..0.138 rows=397 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
77. 0.081 0.081 ↑ 1.0 397 1

Seq Scan on d_insurance_companies ic (cost=0.00..9.97 rows=397 width=14) (actual time=0.013..0.081 rows=397 loops=1)

78. 0.000 0.000 ↑ 9.4 8 4,904

Materialize (cost=19.29..184.72 rows=75 width=7) (actual time=0.000..0.000 rows=8 loops=4,904)

79. 0.038 0.125 ↑ 7.5 10 1

Bitmap Heap Scan on d_urprivs t_13 (cost=19.29..184.35 rows=75 width=7) (actual time=0.116..0.125 rows=10 loops=1)

  • Recheck Cond: ((unitcode)::text = 'AGENT_POLIS'::text)
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 20))
  • Heap Blocks: exact=3
80. 0.026 0.026 ↑ 1.0 140 1

Bitmap Index Scan on i_d_urprivs_ulr (cost=0.00..5.33 rows=140 width=0) (actual time=0.026..0.026 rows=140 loops=1)

  • Index Cond: ((unitcode)::text = 'AGENT_POLIS'::text)
81.          

SubPlan (for Bitmap Heap Scan)

82. 0.009 0.061 ↓ 7.0 35 1

Nested Loop (cost=4.32..13.92 rows=5 width=6) (actual time=0.032..0.061 rows=35 loops=1)

83. 0.030 0.030 ↑ 1.0 1 1

Seq Scan on d_users us_11 (cost=0.00..4.20 rows=1 width=7) (actual time=0.014..0.030 rows=1 loops=1)

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
84. 0.012 0.022 ↓ 5.8 35 1

Bitmap Heap Scan on d_userroles ur_11 (cost=4.32..9.66 rows=6 width=13) (actual time=0.014..0.022 rows=35 loops=1)

  • Recheck Cond: (sysuser = us_11.id)
  • Heap Blocks: exact=4
85. 0.010 0.010 ↓ 5.8 35 1

Bitmap Index Scan on i_d_userroles_u (cost=0.00..4.32 rows=6 width=0) (actual time=0.010..0.010 rows=35 loops=1)

  • Index Cond: (sysuser = us_11.id)
86. 9.808 9.808 ↑ 1.0 1 4,904

Index Scan using pk_d_agents on d_agents ag_1 (cost=0.29..5.43 rows=1 width=65) (actual time=0.002..0.002 rows=1 loops=4,904)

  • Index Cond: (id = ic.agent)
87. 0.002 0.008 ↓ 0.0 0 1

Nested Loop Semi Join (cost=14.47..23.29 rows=1 width=18) (actual time=0.008..0.008 rows=0 loops=1)

88. 0.006 0.006 ↓ 0.0 0 1

Index Scan using uk_d_agent_inabilities on d_agent_inabilities t_6 (cost=0.12..0.90 rows=1 width=82) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (pid = t.id)
  • Filter: ((begin_date <= (sysdate())::timestamp(0) without time zone) AND ((end_date >= sysdate()) OR (nullif2(end_date) IS NULL)))
89. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uk_d_urprivs on d_urprivs t_7 (cost=14.35..22.38 rows=1 width=7) (never executed)

  • Index Cond: ((unitcode = 'AGENT_INABILITIES'::text) AND (catalog = t_6.cid))
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 13))
  • Heap Fetches: 0
90.          

SubPlan (for Index Only Scan)

91. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.32..13.92 rows=5 width=6) (never executed)

92. 0.000 0.000 ↓ 0.0 0

Seq Scan on d_users us_8 (cost=0.00..4.20 rows=1 width=7) (never executed)

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
93. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on d_userroles ur_8 (cost=4.32..9.66 rows=6 width=13) (never executed)

  • Recheck Cond: (sysuser = us_8.id)
94. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on i_d_userroles_u (cost=0.00..4.32 rows=6 width=0) (never executed)

  • Index Cond: (sysuser = us_8.id)
95. 0.009 0.128 ↑ 1.0 1 1

Nested Loop Semi Join (cost=14.89..20.67 rows=1 width=11) (actual time=0.126..0.128 rows=1 loops=1)

96. 0.018 0.018 ↑ 1.0 1 1

Index Scan using uk_d_agent_social_states on d_agent_social_states t_4 (cost=0.54..1.10 rows=1 width=22) (actual time=0.016..0.018 rows=1 loops=1)

  • Index Cond: ((pid = t.id) AND (begin_date <= (sysdate())::timestamp(0) without time zone))
  • Filter: ((end_date >= sysdate()) OR (nullif2(end_date) IS NULL))
  • Rows Removed by Filter: 1
97. 0.039 0.101 ↑ 1.0 1 1

Index Only Scan using uk_d_urprivs on d_urprivs t_5 (cost=14.35..19.56 rows=1 width=7) (actual time=0.101..0.101 rows=1 loops=1)

  • Index Cond: ((unitcode = 'AGENT_SOCIAL_STATES'::text) AND (catalog = t_4.cid))
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 12))
  • Heap Fetches: 1
98.          

SubPlan (for Index Only Scan)

99. 0.009 0.062 ↓ 7.0 35 1

Nested Loop (cost=4.32..13.92 rows=5 width=6) (actual time=0.037..0.062 rows=35 loops=1)

100. 0.032 0.032 ↑ 1.0 1 1

Seq Scan on d_users us_7 (cost=0.00..4.20 rows=1 width=7) (actual time=0.017..0.032 rows=1 loops=1)

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
101. 0.011 0.021 ↓ 5.8 35 1

Bitmap Heap Scan on d_userroles ur_7 (cost=4.32..9.66 rows=6 width=13) (actual time=0.014..0.021 rows=35 loops=1)

  • Recheck Cond: (sysuser = us_7.id)
  • Heap Blocks: exact=4
102. 0.010 0.010 ↓ 5.8 35 1

Bitmap Index Scan on i_d_userroles_u (cost=0.00..4.32 rows=6 width=0) (actual time=0.010..0.010 rows=35 loops=1)

  • Index Cond: (sysuser = us_7.id)
103. 5.577 12.138 ↓ 194.2 2,524 1

Nested Loop Left Join (cost=53.38..2,234.47 rows=13 width=1,238) (actual time=0.330..12.138 rows=2,524 loops=1)

104. 0.434 6.561 ↓ 194.2 2,524 1

Hash Left Join (cost=53.09..2,113.09 rows=13 width=98) (actual time=0.307..6.561 rows=2,524 loops=1)

  • Hash Cond: (t_14.jobtitle = t6.id)
105. 0.650 5.934 ↓ 194.2 2,524 1

Merge Semi Join (cost=27.92..2,087.88 rows=13 width=76) (actual time=0.106..5.934 rows=2,524 loops=1)

  • Merge Cond: (t_14.cid = t_15.catalog)
106. 5.190 5.190 ↓ 194.2 2,524 1

Index Scan using i_d_agent_work_places_cid on d_agent_work_places t_14 (cost=0.28..2,046.82 rows=13 width=115) (actual time=0.017..5.190 rows=2,524 loops=1)

  • Filter: ((is_work = '0'::numeric(1,0)) AND (is_main = '1'::numeric(1,0)) AND (begin_date <= (sysdate())::timestamp(0) without time zone) AND ((end_date >= sysdate()) OR (nullif2(end_date) IS NULL)))
  • Rows Removed by Filter: 2
107. 0.049 0.094 ↑ 2.2 13 1

Index Only Scan using uk_d_urprivs on d_urprivs t_15 (cost=14.35..206.97 rows=29 width=7) (actual time=0.078..0.094 rows=13 loops=1)

  • Index Cond: (unitcode = 'AGENT_WORK_PLACES'::text)
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 22))
  • Rows Removed by Filter: 2
  • Heap Fetches: 15
108.          

SubPlan (for Index Only Scan)

109. 0.006 0.045 ↓ 7.0 35 1

Nested Loop (cost=4.32..13.92 rows=5 width=6) (actual time=0.022..0.045 rows=35 loops=1)

110. 0.025 0.025 ↑ 1.0 1 1

Seq Scan on d_users us_13 (cost=0.00..4.20 rows=1 width=7) (actual time=0.010..0.025 rows=1 loops=1)

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
111. 0.007 0.014 ↓ 5.8 35 1

Bitmap Heap Scan on d_userroles ur_13 (cost=4.32..9.66 rows=6 width=13) (actual time=0.009..0.014 rows=35 loops=1)

  • Recheck Cond: (sysuser = us_13.id)
  • Heap Blocks: exact=4
112. 0.007 0.007 ↓ 5.8 35 1

Bitmap Index Scan on i_d_userroles_u (cost=0.00..4.32 rows=6 width=0) (actual time=0.007..0.007 rows=35 loops=1)

  • Index Cond: (sysuser = us_13.id)
113. 0.100 0.193 ↑ 1.0 630 1

Hash (cost=17.30..17.30 rows=630 width=36) (actual time=0.193..0.193 rows=630 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 51kB
114. 0.093 0.093 ↑ 1.0 630 1

Seq Scan on d_jobtitles t6 (cost=0.00..17.30 rows=630 width=36) (actual time=0.007..0.093 rows=630 loops=1)

115. 0.000 0.000 ↓ 0.0 0 2,524

Index Scan using pk_d_agents on d_agents t2 (cost=0.29..8.31 rows=1 width=65) (actual time=0.000..0.000 rows=0 loops=2,524)

  • Index Cond: (id = t_14.work_place)
116. 50.016 50.016 ↑ 1.0 54,024 1

Seq Scan on d_agent_addrs t_16 (cost=0.00..1,509.24 rows=54,024 width=966) (actual time=0.014..50.016 rows=54,024 loops=1)

117.          

SubPlan (for Nested Loop Left Join)

118. 162.072 594.264 ↑ 1.0 1 54,024

Nested Loop Semi Join (cost=14.35..31.82 rows=1 width=32) (actual time=0.010..0.011 rows=1 loops=54,024)

119. 108.048 108.048 ↑ 1.0 1 54,024

Seq Scan on d_maritalstates t_17 (cost=0.00..1.10 rows=1 width=432) (actual time=0.001..0.002 rows=1 loops=54,024)

  • Filter: (id = t_2.marital_state)
  • Rows Removed by Filter: 7
120. 324.075 324.144 ↑ 1.0 1 54,024

Index Only Scan using uk_d_urprivs on d_urprivs t_18 (cost=14.35..22.41 rows=1 width=7) (actual time=0.006..0.006 rows=1 loops=54,024)

  • Index Cond: ((unitcode = 'MARITALSTATES'::text) AND (version = t_17.version))
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 1))
  • Heap Fetches: 54,024
121.          

SubPlan (for Index Only Scan)

122. 0.009 0.069 ↓ 7.0 35 1

Nested Loop (cost=4.32..13.92 rows=5 width=6) (actual time=0.039..0.069 rows=35 loops=1)

123. 0.032 0.032 ↑ 1.0 1 1

Seq Scan on d_users us (cost=0.00..4.20 rows=1 width=7) (actual time=0.016..0.032 rows=1 loops=1)

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
124. 0.016 0.028 ↓ 5.8 35 1

Bitmap Heap Scan on d_userroles ur (cost=4.32..9.66 rows=6 width=13) (actual time=0.018..0.028 rows=35 loops=1)

  • Recheck Cond: (sysuser = us.id)
  • Heap Blocks: exact=4
125. 0.012 0.012 ↓ 5.8 35 1

Bitmap Index Scan on i_d_userroles_u (cost=0.00..4.32 rows=6 width=0) (actual time=0.012..0.012 rows=35 loops=1)

  • Index Cond: (sysuser = us.id)
126. 108.048 486.216 ↑ 1.0 1 54,024

Nested Loop Semi Join (cost=14.35..31.80 rows=1 width=32) (actual time=0.008..0.009 rows=1 loops=54,024)

127. 108.048 108.048 ↑ 1.0 1 54,024

Seq Scan on d_education_types t_19 (cost=0.00..1.07 rows=1 width=414) (actual time=0.001..0.002 rows=1 loops=54,024)

  • Filter: (id = t.education)
  • Rows Removed by Filter: 5
128. 270.075 270.120 ↑ 1.0 1 54,024

Index Only Scan using uk_d_urprivs on d_urprivs t_20 (cost=14.35..22.41 rows=1 width=7) (actual time=0.005..0.005 rows=1 loops=54,024)

  • Index Cond: ((unitcode = 'EDUCATION_TYPES'::text) AND (version = t_19.version))
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 3))
  • Heap Fetches: 54,024
129.          

SubPlan (for Index Only Scan)

130. 0.007 0.045 ↓ 7.0 35 1

Nested Loop (cost=4.32..13.92 rows=5 width=6) (actual time=0.022..0.045 rows=35 loops=1)

131. 0.024 0.024 ↑ 1.0 1 1

Seq Scan on d_users us_1 (cost=0.00..4.20 rows=1 width=7) (actual time=0.010..0.024 rows=1 loops=1)

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
132. 0.007 0.014 ↓ 5.8 35 1

Bitmap Heap Scan on d_userroles ur_1 (cost=4.32..9.66 rows=6 width=13) (actual time=0.009..0.014 rows=35 loops=1)

  • Recheck Cond: (sysuser = us_1.id)
  • Heap Blocks: exact=4
133. 0.007 0.007 ↓ 5.8 35 1

Bitmap Index Scan on i_d_userroles_u (cost=0.00..4.32 rows=6 width=0) (actual time=0.007..0.007 rows=35 loops=1)

  • Index Cond: (sysuser = us_1.id)
134. 0.000 108.048 ↓ 0.0 0 54,024

Nested Loop Semi Join (cost=14.49..32.43 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=54,024)

135. 54.024 108.048 ↓ 0.0 0 54,024

Nested Loop Left Join (cost=0.14..10.02 rows=1 width=46) (actual time=0.002..0.002 rows=0 loops=54,024)

136. 54.024 54.024 ↓ 0.0 0 54,024

Seq Scan on d_agent_inabilities t_21 (cost=0.00..1.77 rows=1 width=72) (actual time=0.001..0.001 rows=0 loops=54,024)

  • Filter: ((pid = t.id) AND (begin_date <= (sysdate())::timestamp(0) without time zone) AND ((end_date >= sysdate()) OR (nullif2_stb(end_date) IS NULL)))
  • Rows Removed by Filter: 1
137. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_d_inability_statuses on d_inability_statuses t2_1 (cost=0.14..8.16 rows=1 width=28) (never executed)

  • Index Cond: (id = t_21.inability_status)
138. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uk_d_urprivs on d_urprivs t_22 (cost=14.35..22.38 rows=1 width=7) (never executed)

  • Index Cond: ((unitcode = 'AGENT_INABILITIES'::text) AND (catalog = t_21.cid))
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 5))
  • Heap Fetches: 0
139.          

SubPlan (for Index Only Scan)

140. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.32..13.92 rows=5 width=6) (never executed)

141. 0.000 0.000 ↓ 0.0 0

Seq Scan on d_users us_2 (cost=0.00..4.20 rows=1 width=7) (never executed)

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
142. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on d_userroles ur_2 (cost=4.32..9.66 rows=6 width=13) (never executed)

  • Recheck Cond: (sysuser = us_2.id)
143. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on i_d_userroles_u (cost=0.00..4.32 rows=6 width=0) (never executed)

  • Index Cond: (sysuser = us_2.id)
144. 162.072 1,566.696 ↑ 1.0 1 54,024

Nested Loop Semi Join (cost=28.97..66.37 rows=1 width=32) (actual time=0.028..0.029 rows=1 loops=54,024)

145. 108.048 1,134.504 ↑ 1.0 1 54,024

Nested Loop (cost=14.62..43.70 rows=1 width=54) (actual time=0.019..0.021 rows=1 loops=54,024)

146. 162.072 918.408 ↑ 1.0 1 54,024

Nested Loop Semi Join (cost=14.35..33.79 rows=1 width=1,094) (actual time=0.016..0.017 rows=1 loops=54,024)

147. 108.048 486.216 ↑ 1.0 1 54,024

Nested Loop (cost=0.00..2.57 rows=1 width=52) (actual time=0.008..0.009 rows=1 loops=54,024)

  • Join Filter: (t1_2.sc_code = t_25.social_category)
  • Rows Removed by Join Filter: 1
148. 324.144 324.144 ↑ 1.0 1 54,024

Seq Scan on d_socialstates t_25 (cost=0.00..1.50 rows=1 width=62) (actual time=0.005..0.006 rows=1 loops=54,024)

  • Filter: (id = t_4.social_state)
  • Rows Removed by Filter: 39
149. 54.024 54.024 ↑ 1.5 2 54,024

Seq Scan on d_socialcategories t1_2 (cost=0.00..1.03 rows=3 width=10) (actual time=0.000..0.001 rows=2 loops=54,024)

150. 270.076 270.120 ↑ 1.0 1 54,024

Index Only Scan using uk_d_urprivs on d_urprivs t_26 (cost=14.35..22.41 rows=1 width=7) (actual time=0.005..0.005 rows=1 loops=54,024)

  • Index Cond: ((unitcode = 'SOCIALSTATES'::text) AND (version = t_25.version))
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 8))
  • Heap Fetches: 54,024
151.          

SubPlan (for Index Only Scan)

152. 0.007 0.044 ↓ 7.0 35 1

Nested Loop (cost=4.32..13.92 rows=5 width=6) (actual time=0.021..0.044 rows=35 loops=1)

153. 0.023 0.023 ↑ 1.0 1 1

Seq Scan on d_users us_4 (cost=0.00..4.20 rows=1 width=7) (actual time=0.009..0.023 rows=1 loops=1)

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
154. 0.007 0.014 ↓ 5.8 35 1

Bitmap Heap Scan on d_userroles ur_4 (cost=4.32..9.66 rows=6 width=13) (actual time=0.009..0.014 rows=35 loops=1)

  • Recheck Cond: (sysuser = us_4.id)
  • Heap Blocks: exact=4
155. 0.007 0.007 ↓ 5.8 35 1

Bitmap Index Scan on i_d_userroles_u (cost=0.00..4.32 rows=6 width=0) (actual time=0.007..0.007 rows=35 loops=1)

  • Index Cond: (sysuser = us_4.id)
156. 108.048 108.048 ↑ 1.0 1 54,024

Index Scan using pk_d_directories_fn_data on d_directories_fn_data t_23 (cost=0.28..8.29 rows=1 width=61) (actual time=0.002..0.002 rows=1 loops=54,024)

  • Index Cond: (id = t_25.soc_fcode)
  • Filter: (dir = '41'::numeric(3,0))
157. 270.071 270.120 ↑ 1.0 1 54,024

Index Only Scan using uk_d_urprivs on d_urprivs t_24 (cost=14.35..22.41 rows=1 width=7) (actual time=0.005..0.005 rows=1 loops=54,024)

  • Index Cond: ((unitcode = 'DIRECTORIES_FN_DATA'::text) AND (version = t_23.version))
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 7))
  • Heap Fetches: 54,024
158.          

SubPlan (for Index Only Scan)

159. 0.008 0.049 ↓ 7.0 35 1

Nested Loop (cost=4.32..13.92 rows=5 width=6) (actual time=0.025..0.049 rows=35 loops=1)

160. 0.027 0.027 ↑ 1.0 1 1

Seq Scan on d_users us_3 (cost=0.00..4.20 rows=1 width=7) (actual time=0.013..0.027 rows=1 loops=1)

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
161. 0.007 0.014 ↓ 5.8 35 1

Bitmap Heap Scan on d_userroles ur_3 (cost=4.32..9.66 rows=6 width=13) (actual time=0.009..0.014 rows=35 loops=1)

  • Recheck Cond: (sysuser = us_3.id)
  • Heap Blocks: exact=4
162. 0.007 0.007 ↓ 5.8 35 1

Bitmap Index Scan on i_d_userroles_u (cost=0.00..4.32 rows=6 width=0) (actual time=0.007..0.007 rows=35 loops=1)

  • Index Cond: (sysuser = us_3.id)
Planning time : 86.244 ms
Execution time : 7,017.039 ms