explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LLSd

Settings
# exclusive inclusive rows x rows loops node
1. 0.631 13,798.692 ↑ 1.0 1 1

Nested Loop Left Join (cost=2,714.52..101,853.81 rows=1 width=1,039) (actual time=13,625.336..13,798.692 rows=1 loops=1)

  • Join Filter: (t_16.pid = t.id)
  • Rows Removed by Join Filter: 2,523
2. 0.006 13,785.823 ↑ 1.0 1 1

Nested Loop Left Join (cost=2,661.14..99,454.33 rows=1 width=783) (actual time=13,613.077..13,785.823 rows=1 loops=1)

3. 0.006 13,785.670 ↑ 1.0 1 1

Nested Loop Left Join (cost=2,646.26..99,433.66 rows=1 width=778) (actual time=13,612.928..13,785.670 rows=1 loops=1)

4. 8.136 13,785.641 ↑ 1.0 1 1

Nested Loop Left Join (cost=2,631.79..99,410.36 rows=1 width=778) (actual time=13,612.899..13,785.641 rows=1 loops=1)

  • Join Filter: (t_14.pid = t.id)
  • Rows Removed by Join Filter: 53,005
5. 0.007 13,409.959 ↑ 1.0 1 1

Nested Loop Left Join (cost=119.29..95,735.51 rows=1 width=714) (actual time=13,334.027..13,409.959 rows=1 loops=1)

6. 9.008 13,409.783 ↑ 1.0 1 1

Nested Loop Left Join (cost=104.03..95,711.08 rows=1 width=696) (actual time=13,333.865..13,409.783 rows=1 loops=1)

  • Join Filter: (t_12.pid = t.id)
  • Rows Removed by Join Filter: 54,821
7. 0.759 13,083.566 ↑ 1.0 1 1

Nested Loop Left Join (cost=84.68..51,667.24 rows=1 width=346) (actual time=13,068.367..13,083.566 rows=1 loops=1)

  • Join Filter: (p.pid = t.id)
  • Rows Removed by Join Filter: 4,903
8. 14.098 13,052.618 ↑ 1.0 1 1

Nested Loop Left Join (cost=49.63..48,405.12 rows=1 width=269) (actual time=13,052.612..13,052.618 rows=1 loops=1)

  • Join Filter: (t.id = pmc.id)
  • Rows Removed by Join Filter: 53,333
9. 0.002 0.325 ↑ 1.0 1 1

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

10. 0.004 0.218 ↑ 1.0 1 1

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

11. 0.021 0.210 ↑ 1.0 1 1

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

12. 0.004 0.180 ↑ 1.0 1 1

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

13. 0.002 0.041 ↑ 1.0 1 1

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

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

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

  • Join Filter: (r.rh_code = pmc.rhesus)
  • Rows Removed by Join Filter: 3
15. 0.024 0.024 ↑ 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.024 rows=1 loops=1)

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

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

17. 0.004 0.004 ↑ 1.0 4 1

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

18. 0.046 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.089 ↓ 7.0 35 1

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

21. 0.049 0.049 ↑ 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.049 rows=1 loops=1)

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

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

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

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

  • Index Cond: (sysuser = us_9.id)
24. 0.009 0.009 ↑ 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.009..0.009 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.033 0.105 ↑ 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.105..0.105 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.008 0.072 ↓ 7.0 35 1

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

29. 0.043 0.043 ↑ 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.043 rows=1 loops=1)

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
30. 0.010 0.021 ↓ 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.021 rows=35 loops=1)

  • Recheck Cond: (sysuser = us_5.id)
  • Heap Blocks: exact=4
31. 0.011 0.011 ↓ 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.011 rows=35 loops=1)

  • Index Cond: (sysuser = us_5.id)
32. 12,399.733 13,038.195 ↓ 202.0 53,333 1

Nested Loop Left Join (cost=20.07..48,334.03 rows=264 width=1,769) (actual time=0.799..13,038.195 rows=53,333 loops=1)

33. 49.143 425.130 ↓ 202.0 53,333 1

Nested Loop Semi Join (cost=19.64..45,266.71 rows=264 width=76) (actual time=0.176..425.130 rows=53,333 loops=1)

  • Join Filter: (t_9.cid = t_10.catalog)
  • Rows Removed by Join Filter: 106,844
34. 44.728 375.987 ↓ 202.0 53,333 1

Nested Loop Left Join (cost=0.43..44,896.02 rows=264 width=83) (actual time=0.049..375.987 rows=53,333 loops=1)

35. 171.260 171.260 ↓ 202.0 53,333 1

Seq Scan on d_agent_addrs t_9 (cost=0.00..42,702.54 rows=264 width=71) (actual time=0.034..171.260 rows=53,333 loops=1)

  • Filter: ((is_main = '1'::numeric(1,0)) AND (is_reg = '1'::numeric(1,0)) AND (begin_date <= (sysdate())::timestamp(0) without time zone) AND ((end_date >= sysdate()) OR (nullif2_stb(end_date) IS NULL)))
  • Rows Removed by Filter: 691
36. 159.999 159.999 ↑ 1.0 1 53,333

Index Scan using pk_d_geografy on d_geografy t1_1 (cost=0.43..8.31 rows=1 width=25) (actual time=0.003..0.003 rows=1 loops=53,333)

  • Index Cond: (id = t_9.city)
37. 0.000 0.000 ↑ 23.0 3 53,333

Materialize (cost=19.21..181.77 rows=69 width=7) (actual time=0.000..0.000 rows=3 loops=53,333)

38. 0.092 0.187 ↑ 1.2 58 1

Bitmap Heap Scan on d_urprivs t_10 (cost=19.21..181.43 rows=69 width=7) (actual time=0.118..0.187 rows=58 loops=1)

  • Recheck Cond: ((unitcode)::text = 'AGENT_ADDRS'::text)
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 15))
  • Heap Blocks: exact=15
39. 0.025 0.025 ↑ 1.0 130 1

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

  • Index Cond: ((unitcode)::text = 'AGENT_ADDRS'::text)
40.          

SubPlan (for Bitmap Heap Scan)

41. 0.008 0.070 ↓ 7.0 35 1

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

42. 0.043 0.043 ↑ 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.043 rows=1 loops=1)

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

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

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

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

  • Index Cond: (sysuser = us_10.id)
45. 213.332 213.332 ↑ 1.0 1 53,333

Index Scan using pk_d_geografy on d_geografy t2 (cost=0.43..8.31 rows=1 width=25) (actual time=0.004..0.004 rows=1 loops=53,333)

  • Index Cond: (id = t_9.street)
46. 2.559 30.189 ↓ 188.6 4,904 1

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

47. 7.611 17.822 ↓ 188.6 4,904 1

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

  • Join Filter: (p.cid = t_11.catalog)
  • Rows Removed by Join Filter: 34,338
48. 2.005 10.211 ↓ 188.6 4,904 1

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

  • Hash Cond: (p.p_who = ic.id)
49. 8.085 8.085 ↓ 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.025..8.085 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
50. 0.056 0.121 ↑ 1.0 397 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
51. 0.065 0.065 ↑ 1.0 397 1

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

52. 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)

53. 0.034 0.124 ↑ 7.5 10 1

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

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

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

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

SubPlan (for Bitmap Heap Scan)

56. 0.008 0.068 ↓ 7.0 35 1

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

57. 0.037 0.037 ↑ 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.037 rows=1 loops=1)

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

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

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

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

  • Index Cond: (sysuser = us_12.id)
60. 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)
61. 105.028 317.209 ↓ 199.4 54,822 1

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

62. 54.660 157.359 ↓ 199.4 54,822 1

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

  • Join Filter: (t_12.cid = t_13.catalog)
  • Rows Removed by Join Filter: 222,157
63. 102.699 102.699 ↓ 199.4 54,822 1

Seq Scan on d_agent_persdocs t_12 (cost=0.00..43,532.99 rows=275 width=43) (actual time=0.015..102.699 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
64. 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)

65. 0.041 0.117 ↑ 9.9 7 1

Bitmap Heap Scan on d_urprivs t_13 (cost=19.21..181.43 rows=69 width=7) (actual time=0.099..0.117 rows=7 loops=1)

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

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

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

SubPlan (for Bitmap Heap Scan)

68. 0.008 0.055 ↓ 7.0 35 1

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

69. 0.028 0.028 ↑ 1.0 1 1

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

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

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

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

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

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

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

  • Index Cond: (id = t_12.pd_type)
73. 0.006 0.169 ↑ 1.0 1 1

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

74. 0.021 0.061 ↑ 1.0 1 1

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

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

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

76. 0.009 0.025 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
77. 0.016 0.016 ↑ 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.015..0.016 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)))
78. 0.041 0.102 ↑ 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.102..0.102 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
79.          

SubPlan (for Index Only Scan)

80. 0.009 0.061 ↓ 7.0 35 1

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

81. 0.029 0.029 ↑ 1.0 1 1

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

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

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

  • Recheck Cond: (sysuser = us_6.id)
  • Heap Blocks: exact=4
83. 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)
84. 146.274 367.546 ↓ 70.0 53,006 1

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

  • Group Key: t_14.pid, t_14.version
  • Filter: ((NULL::text >= sysdate()) OR (nullif2(NULL::text) IS NULL))
85. 6.051 221.272 ↓ 36.6 55,109 1

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

  • One-Time Filter: (((trunc(sysdate(), 'DDD'::text))::date - 1) <= sysdate())
86. 1.502 215.221 ↓ 36.6 55,109 1

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

87. 49.348 103.501 ↓ 36.6 55,109 1

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

  • Join Filter: (ct.id = t_14.contact_type)
  • Rows Removed by Join Filter: 220,436
88. 0.015 0.253 ↓ 10.0 50 1

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

89. 0.039 0.218 ↑ 6.2 10 1

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

  • Group Key: t_15.catalog
90. 0.119 0.179 ↓ 1.7 120 1

Bitmap Heap Scan on d_urprivs t_15 (cost=19.21..181.43 rows=69 width=7) (actual time=0.080..0.179 rows=120 loops=1)

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

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

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

SubPlan (for Bitmap Heap Scan)

93. 0.006 0.043 ↓ 7.0 35 1

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

94. 0.024 0.024 ↑ 1.0 1 1

Seq Scan on d_users us_11 (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
95. 0.006 0.013 ↓ 5.8 35 1

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

  • Recheck Cond: (sysuser = us_11.id)
  • Heap Blocks: exact=4
96. 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_11.id)
97. 0.013 0.020 ↑ 1.0 5 10

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

98. 0.007 0.007 ↑ 1.0 5 1

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

99. 53.900 53.900 ↑ 1.7 5,511 50

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

  • Index Cond: (cid = t_15.catalog)
100. 110.218 110.218 ↑ 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.002..0.002 rows=1 loops=55,109)

  • Index Cond: (id = t_14.pid)
101. 0.001 0.023 ↓ 0.0 0 1

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

102. 0.022 0.022 ↓ 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.022..0.022 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)))
103. 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
104.          

SubPlan (for Index Only Scan)

105. 0.000 0.000 ↓ 0.0 0

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

106. 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)
107. 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)
108. 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)
109. 0.008 0.147 ↑ 1.0 1 1

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

110. 0.027 0.027 ↑ 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.025..0.027 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
111. 0.042 0.112 ↑ 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.112..0.112 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
112.          

SubPlan (for Index Only Scan)

113. 0.009 0.070 ↓ 7.0 35 1

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

114. 0.035 0.035 ↑ 1.0 1 1

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

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

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

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

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

  • Index Cond: (sysuser = us_7.id)
117. 5.408 11.839 ↓ 194.2 2,524 1

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

118. 0.462 6.431 ↓ 194.2 2,524 1

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

  • Hash Cond: (t_16.jobtitle = t6.id)
119. 0.673 5.775 ↓ 194.2 2,524 1

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

  • Merge Cond: (t_16.cid = t_17.catalog)
120. 5.015 5.015 ↓ 194.2 2,524 1

Index Scan using i_d_agent_work_places_cid on d_agent_work_places t_16 (cost=0.28..2,046.82 rows=13 width=115) (actual time=0.026..5.015 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
121. 0.042 0.087 ↑ 2.2 13 1

Index Only Scan using uk_d_urprivs on d_urprivs t_17 (cost=14.35..206.97 rows=29 width=7) (actual time=0.071..0.087 rows=13 loops=1)

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

SubPlan (for Index Only Scan)

123. 0.007 0.045 ↓ 7.0 35 1

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

124. 0.024 0.024 ↑ 1.0 1 1

Seq Scan on d_users us_14 (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
125. 0.007 0.014 ↓ 5.8 35 1

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

  • Recheck Cond: (sysuser = us_14.id)
  • Heap Blocks: exact=4
126. 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_14.id)
127. 0.100 0.194 ↑ 1.0 630 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 51kB
128. 0.094 0.094 ↑ 1.0 630 1

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

129. 0.000 0.000 ↓ 0.0 0 2,524

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

  • Index Cond: (id = t_16.work_place)
130.          

SubPlan (for Nested Loop Left Join)

131. 0.009 0.110 ↑ 1.0 1 1

Nested Loop Semi Join (cost=14.35..31.82 rows=1 width=32) (actual time=0.109..0.110 rows=1 loops=1)

132. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on d_maritalstates t_18 (cost=0.00..1.10 rows=1 width=432) (actual time=0.008..0.010 rows=1 loops=1)

  • Filter: (id = t_2.marital_state)
  • Rows Removed by Filter: 7
133. 0.034 0.091 ↑ 1.0 1 1

Index Only Scan using uk_d_urprivs on d_urprivs t_19 (cost=14.35..22.41 rows=1 width=7) (actual time=0.091..0.091 rows=1 loops=1)

  • Index Cond: ((unitcode = 'MARITALSTATES'::text) AND (version = t_18.version))
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 1))
  • Heap Fetches: 1
134.          

SubPlan (for Index Only Scan)

135. 0.009 0.057 ↓ 7.0 35 1

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

136. 0.028 0.028 ↑ 1.0 1 1

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

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

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

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

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

  • Index Cond: (sysuser = us.id)
139. 0.008 0.083 ↑ 1.0 1 1

Nested Loop Semi Join (cost=14.35..31.80 rows=1 width=32) (actual time=0.082..0.083 rows=1 loops=1)

140. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on d_education_types t_20 (cost=0.00..1.07 rows=1 width=414) (actual time=0.005..0.006 rows=1 loops=1)

  • Filter: (id = t.education)
  • Rows Removed by Filter: 5
141. 0.026 0.069 ↑ 1.0 1 1

Index Only Scan using uk_d_urprivs on d_urprivs t_21 (cost=14.35..22.41 rows=1 width=7) (actual time=0.069..0.069 rows=1 loops=1)

  • Index Cond: ((unitcode = 'EDUCATION_TYPES'::text) AND (version = t_20.version))
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 3))
  • Heap Fetches: 1
142.          

SubPlan (for Index Only Scan)

143. 0.006 0.043 ↓ 7.0 35 1

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

144. 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
145. 0.007 0.013 ↓ 5.8 35 1

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

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

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

  • Index Cond: (sysuser = us_1.id)
147. 0.002 0.007 ↓ 0.0 0 1

Nested Loop Semi Join (cost=14.49..32.43 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=1)

148. 0.000 0.005 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.14..10.02 rows=1 width=46) (actual time=0.005..0.005 rows=0 loops=1)

149. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on d_agent_inabilities t_22 (cost=0.00..1.77 rows=1 width=72) (actual time=0.005..0.005 rows=0 loops=1)

  • 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
150. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = t_22.inability_status)
151. 0.000 0.000 ↓ 0.0 0

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

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

SubPlan (for Index Only Scan)

153. 0.000 0.000 ↓ 0.0 0

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

154. 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)
155. 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)
156. 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)
157. 0.020 0.199 ↑ 1.0 1 1

Nested Loop Semi Join (cost=28.97..66.37 rows=1 width=32) (actual time=0.198..0.199 rows=1 loops=1)

158. 0.003 0.114 ↑ 1.0 1 1

Nested Loop (cost=14.62..43.70 rows=1 width=54) (actual time=0.113..0.114 rows=1 loops=1)

159. 0.013 0.103 ↑ 1.0 1 1

Nested Loop Semi Join (cost=14.35..33.79 rows=1 width=1,094) (actual time=0.101..0.103 rows=1 loops=1)

160. 0.003 0.018 ↑ 1.0 1 1

Nested Loop (cost=0.00..2.57 rows=1 width=52) (actual time=0.017..0.018 rows=1 loops=1)

  • Join Filter: (t1_3.sc_code = t_26.social_category)
  • Rows Removed by Join Filter: 1
161. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on d_socialstates t_26 (cost=0.00..1.50 rows=1 width=62) (actual time=0.010..0.011 rows=1 loops=1)

  • Filter: (id = t_4.social_state)
  • Rows Removed by Filter: 39
162. 0.004 0.004 ↑ 1.5 2 1

Seq Scan on d_socialcategories t1_3 (cost=0.00..1.03 rows=3 width=10) (actual time=0.004..0.004 rows=2 loops=1)

163. 0.025 0.072 ↑ 1.0 1 1

Index Only Scan using uk_d_urprivs on d_urprivs t_27 (cost=14.35..22.41 rows=1 width=7) (actual time=0.072..0.072 rows=1 loops=1)

  • Index Cond: ((unitcode = 'SOCIALSTATES'::text) AND (version = t_26.version))
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 8))
  • Heap Fetches: 1
164.          

SubPlan (for Index Only Scan)

165. 0.006 0.047 ↓ 7.0 35 1

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

166. 0.027 0.027 ↑ 1.0 1 1

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

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
167. 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
168. 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)
169. 0.008 0.008 ↑ 1.0 1 1

Index Scan using pk_d_directories_fn_data on d_directories_fn_data t_24 (cost=0.28..8.29 rows=1 width=61) (actual time=0.008..0.008 rows=1 loops=1)

  • Index Cond: (id = t_26.soc_fcode)
  • Filter: (dir = '41'::numeric(3,0))
170. 0.021 0.065 ↑ 1.0 1 1

Index Only Scan using uk_d_urprivs on d_urprivs t_25 (cost=14.35..22.41 rows=1 width=7) (actual time=0.065..0.065 rows=1 loops=1)

  • Index Cond: ((unitcode = 'DIRECTORIES_FN_DATA'::text) AND (version = t_24.version))
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 7))
  • Heap Fetches: 1
171.          

SubPlan (for Index Only Scan)

172. 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)

173. 0.024 0.024 ↑ 1.0 1 1

Seq Scan on d_users us_3 (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
174. 0.007 0.013 ↓ 5.8 35 1

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

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

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

  • Index Cond: (sysuser = us_3.id)
Planning time : 77.323 ms
Execution time : 13,800.356 ms