explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3AEI

Settings
# exclusive inclusive rows x rows loops node
1. 7.856 14,479.856 ↑ 1.0 1 1

Nested Loop Left Join (cost=2,714.52..101,853.78 rows=1 width=1,039) (actual time=5,734.393..14,479.856 rows=1 loops=1)

  • Join Filter: (t_16.pid = t.id)
  • Rows Removed by Join Filter: 2,523
2. 0.004 14,459.637 ↑ 1.0 1 1

Nested Loop Left Join (cost=2,661.14..99,454.30 rows=1 width=783) (actual time=5,714.806..14,459.637 rows=1 loops=1)

3. 0.004 14,459.538 ↑ 1.0 1 1

Nested Loop Left Join (cost=2,646.26..99,433.62 rows=1 width=778) (actual time=5,714.710..14,459.538 rows=1 loops=1)

4. 0.007 14,459.528 ↑ 1.0 1 1

Nested Loop Left Join (cost=2,631.79..99,410.33 rows=1 width=778) (actual time=5,714.702..14,459.528 rows=1 loops=1)

5. 7.934 14,459.345 ↑ 1.0 1 1

Nested Loop Left Join (cost=2,616.52..99,385.89 rows=1 width=760) (actual time=5,714.525..14,459.345 rows=1 loops=1)

  • Join Filter: (t_14.pid = t.id)
  • Rows Removed by Join Filter: 53,005
6. 15.344 14,085.754 ↑ 1.0 1 1

Nested Loop Left Join (cost=104.03..95,711.05 rows=1 width=696) (actual time=5,434.458..14,085.754 rows=1 loops=1)

  • Join Filter: (t_12.pid = t.id)
  • Rows Removed by Join Filter: 53,332
7. 0.717 389.614 ↑ 1.0 1 1

Nested Loop Left Join (cost=83.96..47,371.10 rows=1 width=625) (actual time=323.107..389.614 rows=1 loops=1)

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

Nested Loop Left Join (cost=48.91..44,108.99 rows=1 width=548) (actual time=307.453..362.031 rows=1 loops=1)

  • Join Filter: (t_9.pid = t.id)
  • Rows Removed by Join Filter: 54,821
9. 0.003 0.226 ↑ 1.0 1 1

Nested Loop Semi Join (cost=29.56..65.15 rows=1 width=198) (actual time=0.220..0.226 rows=1 loops=1)

10. 0.002 0.157 ↑ 1.0 1 1

Nested Loop (cost=15.22..50.77 rows=1 width=205) (actual time=0.152..0.157 rows=1 loops=1)

11. 0.017 0.152 ↑ 1.0 1 1

Nested Loop (cost=14.93..42.45 rows=1 width=1,863) (actual time=0.148..0.152 rows=1 loops=1)

12. 0.003 0.128 ↑ 1.0 1 1

Nested Loop Semi Join (cost=14.64..32.85 rows=1 width=130) (actual time=0.124..0.128 rows=1 loops=1)

13. 0.001 0.033 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.29..10.47 rows=1 width=137) (actual time=0.029..0.033 rows=1 loops=1)

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

Nested Loop Left Join (cost=0.29..9.38 rows=1 width=97) (actual time=0.024..0.028 rows=1 loops=1)

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

Index Scan using pk_d_persmedcard on d_persmedcard pmc (cost=0.29..8.31 rows=1 width=83) (actual time=0.016..0.018 rows=1 loops=1)

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

Seq Scan on d_rhesus r (cost=0.00..1.03 rows=3 width=48) (actual time=0.006..0.006 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.003..0.004 rows=4 loops=1)

18. 0.033 0.092 ↑ 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.092..0.092 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.007 0.059 ↓ 7.0 35 1

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

21. 0.029 0.029 ↑ 1.0 1 1

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

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

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

  • Recheck Cond: (sysuser = us_9.id)
  • Heap Blocks: exact=4
23. 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_9.id)
24. 0.007 0.007 ↑ 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.007..0.007 rows=1 loops=1)

  • Index Cond: (id = pmc.agent)
25. 0.003 0.003 ↑ 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.002..0.003 rows=1 loops=1)

  • Index Cond: (id = pmc.agent)
26. 0.022 0.066 ↑ 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.066..0.066 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.006 0.044 ↓ 7.0 35 1

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

29. 0.024 0.024 ↑ 1.0 1 1

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

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

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

  • Recheck Cond: (sysuser = us_5.id)
  • Heap Blocks: exact=4
31. 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_5.id)
32. 124.101 352.548 ↓ 199.4 54,822 1

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

33. 59.801 173.625 ↓ 199.4 54,822 1

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

  • Join Filter: (t_9.cid = t_10.catalog)
  • Rows Removed by Join Filter: 222,157
34. 113.824 113.824 ↓ 199.4 54,822 1

Seq Scan on d_agent_persdocs t_9 (cost=0.00..43,532.99 rows=275 width=43) (actual time=0.018..113.824 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
35. 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)

36. 0.034 0.096 ↑ 9.9 7 1

Bitmap Heap Scan on d_urprivs t_10 (cost=19.21..181.43 rows=69 width=7) (actual time=0.078..0.096 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
37. 0.018 0.018 ↑ 1.0 130 1

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

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

SubPlan (for Bitmap Heap Scan)

39. 0.007 0.044 ↓ 7.0 35 1

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

40. 0.024 0.024 ↑ 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.024 rows=1 loops=1)

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

  • Recheck Cond: (sysuser = us_13.id)
  • Heap Blocks: exact=4
42. 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_13.id)
43. 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_9.pd_type)
44. 0.966 26.866 ↓ 188.6 4,904 1

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

45. 7.018 16.092 ↓ 188.6 4,904 1

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

  • Join Filter: (p.cid = t_11.catalog)
  • Rows Removed by Join Filter: 34,338
46. 1.766 9.074 ↓ 188.6 4,904 1

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

  • Hash Cond: (p.p_who = ic.id)
47. 7.182 7.182 ↓ 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.028..7.182 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
48. 0.057 0.126 ↑ 1.0 397 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
49. 0.069 0.069 ↑ 1.0 397 1

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

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

51. 0.036 0.126 ↑ 7.5 10 1

Bitmap Heap Scan on d_urprivs t_11 (cost=19.29..184.35 rows=75 width=7) (actual time=0.118..0.126 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
52. 0.031 0.031 ↑ 1.0 140 1

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

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

SubPlan (for Bitmap Heap Scan)

54. 0.008 0.059 ↓ 7.0 35 1

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

55. 0.030 0.030 ↑ 1.0 1 1

Seq Scan on d_users us_12 (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
56. 0.012 0.021 ↓ 5.8 35 1

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

  • Recheck Cond: (sysuser = us_12.id)
  • Heap Blocks: exact=4
57. 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_12.id)
58. 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)
59. 13,013.927 13,680.796 ↓ 202.0 53,333 1

Nested Loop Left Join (cost=20.07..48,334.00 rows=264 width=1,757) (actual time=0.610..13,680.796 rows=53,333 loops=1)

60. 51.660 453.537 ↓ 202.0 53,333 1

Nested Loop Semi Join (cost=19.64..45,266.68 rows=264 width=84) (actual time=0.157..453.537 rows=53,333 loops=1)

  • Join Filter: (t_12.cid = t_13.catalog)
  • Rows Removed by Join Filter: 106,844
61. 58.648 401.877 ↓ 202.0 53,333 1

Nested Loop Left Join (cost=0.43..44,896.02 rows=264 width=91) (actual time=0.045..401.877 rows=53,333 loops=1)

62. 183.230 183.230 ↓ 202.0 53,333 1

Seq Scan on d_agent_addrs t_12 (cost=0.00..42,702.54 rows=264 width=79) (actual time=0.031..183.230 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
63. 159.999 159.999 ↑ 1.0 1 53,333

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

  • Index Cond: (id = t_12.city)
64. 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)

65. 0.091 0.170 ↑ 1.2 58 1

Bitmap Heap Scan on d_urprivs t_13 (cost=19.21..181.43 rows=69 width=7) (actual time=0.104..0.170 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
66. 0.022 0.022 ↑ 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.022 rows=130 loops=1)

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

SubPlan (for Bitmap Heap Scan)

68. 0.007 0.057 ↓ 7.0 35 1

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

69. 0.030 0.030 ↑ 1.0 1 1

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

  • Filter: ((username)::text = ((USER)::character varying(30))::text)
  • Rows Removed by Filter: 109
70. 0.011 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.012..0.020 rows=35 loops=1)

  • Recheck Cond: (sysuser = us_10.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_10.id)
72. 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_12.street)
73. 143.503 365.657 ↓ 70.0 53,006 1

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

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

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

  • One-Time Filter: (((trunc(sysdate(), 'DDD'::text))::date - 1) <= sysdate())
75. 1.679 216.044 ↓ 36.6 55,109 1

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

76. 49.810 104.147 ↓ 36.6 55,109 1

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

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

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

78. 0.038 0.252 ↑ 6.2 10 1

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

  • Group Key: t_15.catalog
79. 0.135 0.214 ↓ 1.7 120 1

Bitmap Heap Scan on d_urprivs t_15 (cost=19.21..181.43 rows=69 width=7) (actual time=0.105..0.214 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
80. 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_CONTACTS'::text)
81.          

SubPlan (for Bitmap Heap Scan)

82. 0.008 0.058 ↓ 7.0 35 1

Nested Loop (cost=4.32..13.92 rows=5 width=6) (actual time=0.030..0.058 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.011 0.020 ↓ 5.8 35 1

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

  • Recheck Cond: (sysuser = us_11.id)
  • Heap Blocks: exact=4
85. 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_11.id)
86. 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)

87. 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.007..0.007 rows=5 loops=1)

88. 54.050 54.050 ↑ 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.005..1.081 rows=5,511 loops=50)

  • Index Cond: (cid = t_15.catalog)
89. 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)
90. 0.005 0.176 ↑ 1.0 1 1

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

91. 0.016 0.060 ↑ 1.0 1 1

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

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

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

93. 0.004 0.028 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
94. 0.024 0.024 ↑ 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.023..0.024 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)))
95. 0.047 0.111 ↑ 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.110..0.111 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
96.          

SubPlan (for Index Only Scan)

97. 0.009 0.064 ↓ 7.0 35 1

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

98. 0.030 0.030 ↑ 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.030 rows=1 loops=1)

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

  • Recheck Cond: (sysuser = us_6.id)
  • Heap Blocks: exact=4
100. 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_6.id)
101. 0.002 0.006 ↓ 0.0 0 1

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

102. 0.004 0.004 ↓ 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.004..0.004 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.007 0.095 ↑ 1.0 1 1

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

110. 0.020 0.020 ↑ 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.018..0.020 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.023 0.068 ↑ 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.068..0.068 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.007 0.045 ↓ 7.0 35 1

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

114. 0.025 0.025 ↑ 1.0 1 1

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

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

  • Recheck Cond: (sysuser = us_7.id)
  • Heap Blocks: exact=4
116. 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_7.id)
117. 5.493 11.960 ↓ 194.2 2,524 1

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

118. 0.458 6.467 ↓ 194.2 2,524 1

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

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

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

  • Merge Cond: (t_16.cid = t_17.catalog)
120. 5.059 5.059 ↓ 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.024..5.059 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.039 0.082 ↑ 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.067..0.082 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.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)

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.013 ↓ 5.8 35 1

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

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

Hash (cost=17.30..17.30 rows=630 width=36) (actual time=0.191..0.191 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.016 0.120 ↑ 1.0 1 1

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

132. 0.011 0.011 ↑ 1.0 1 1

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

  • Filter: (id = t_2.marital_state)
  • Rows Removed by Filter: 7
133. 0.038 0.093 ↑ 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.093..0.093 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.008 0.055 ↓ 7.0 35 1

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

136. 0.027 0.027 ↑ 1.0 1 1

Seq Scan on d_users us (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
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.009 0.084 ↑ 1.0 1 1

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

140. 0.005 0.005 ↑ 1.0 1 1

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

  • Filter: (id = t.education)
  • Rows Removed by Filter: 5
141. 0.023 0.070 ↑ 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.070..0.070 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.007 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)

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.010 0.016 ↓ 5.8 35 1

Bitmap Heap Scan on d_userroles ur_1 (cost=4.32..9.66 rows=6 width=13) (actual time=0.011..0.016 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.008 ↓ 0.0 0 1

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

148. 0.000 0.006 ↓ 0.0 0 1

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

149. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on d_agent_inabilities t_22 (cost=0.00..1.77 rows=1 width=72) (actual time=0.006..0.006 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.017 0.191 ↑ 1.0 1 1

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

158. 0.004 0.108 ↑ 1.0 1 1

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

159. 0.011 0.096 ↑ 1.0 1 1

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

160. 0.002 0.017 ↑ 1.0 1 1

Nested Loop (cost=0.00..2.57 rows=1 width=52) (actual time=0.016..0.017 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.068 ↑ 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.068..0.068 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.007 0.043 ↓ 7.0 35 1

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

166. 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
167. 0.006 0.013 ↓ 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.013 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.023 0.066 ↑ 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.066..0.066 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.005 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)

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.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
175. 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 : 61.084 ms
Execution time : 14,481.209 ms