explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1XkA

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 45,381.501 ↓ 0.0 0 1

Nested Loop (cost=77,957.35..2,201,742.20 rows=5 width=4,935) (actual time=45,381.501..45,381.501 rows=0 loops=1)

  • Join Filter: (t1.research = r.rs_id)
2.          

CTE research

3. 5.186 5.186 ↓ 0.0 0 1

Function Scan on table_expression (cost=0.50..10.50 rows=1,000 width=32) (actual time=5.186..5.186 rows=0 loops=1)

4. 0.017 45,376.310 ↓ 3.0 3 1

Nested Loop Semi Join (cost=77,946.85..2,201,697.91 rows=1 width=4,871) (actual time=12,417.532..45,376.310 rows=3 loops=1)

5. 0.020 45,374.760 ↓ 3.0 3 1

Nested Loop Semi Join (cost=77,895.59..2,201,646.49 rows=1 width=4,871) (actual time=12,416.033..45,374.760 rows=3 loops=1)

6. 0.016 45,373.069 ↓ 3.0 3 1

Nested Loop (cost=77,844.32..2,201,595.07 rows=1 width=4,871) (actual time=12,414.459..45,373.069 rows=3 loops=1)

7. 0.020 45,373.002 ↓ 3.0 3 1

Nested Loop (cost=77,843.89..2,201,594.56 rows=1 width=3,398) (actual time=12,414.434..45,373.002 rows=3 loops=1)

8. 658.208 45,372.862 ↓ 3.0 3 1

Nested Loop (cost=77,843.46..2,201,586.11 rows=1 width=2,505) (actual time=12,414.374..45,372.862 rows=3 loops=1)

  • Join Filter: (t_3.id = t_2.patient)
  • Rows Removed by Join Filter: 3,119,211
9. 0.610 372.725 ↓ 3.0 3 1

Nested Loop Semi Join (cost=64.97..43,253.92 rows=1 width=1,680) (actual time=2.260..372.725 rows=3 loops=1)

10. 0.015 0.891 ↓ 3.0 3 1

Nested Loop Left Join (cost=13.70..101.96 rows=1 width=1,983) (actual time=0.375..0.891 rows=3 loops=1)

  • Join Filter: (t11.id = t_2.locus)
  • Rows Removed by Join Filter: 30
11. 0.011 0.849 ↓ 3.0 3 1

Nested Loop Left Join (cost=13.70..100.73 rows=1 width=1,765) (actual time=0.362..0.849 rows=3 loops=1)

12. 0.008 0.811 ↓ 3.0 3 1

Nested Loop Left Join (cost=13.55..92.51 rows=1 width=1,726) (actual time=0.350..0.811 rows=3 loops=1)

13. 0.008 0.794 ↓ 3.0 3 1

Nested Loop Left Join (cost=13.12..85.03 rows=1 width=1,684) (actual time=0.345..0.794 rows=3 loops=1)

14. 0.009 0.780 ↓ 3.0 3 1

Nested Loop Left Join (cost=12.83..79.83 rows=1 width=1,676) (actual time=0.339..0.780 rows=3 loops=1)

15. 0.008 0.762 ↓ 3.0 3 1

Nested Loop Left Join (cost=12.40..72.35 rows=1 width=1,634) (actual time=0.332..0.762 rows=3 loops=1)

16. 0.010 0.736 ↓ 3.0 3 1

Nested Loop Left Join (cost=12.12..67.15 rows=1 width=1,626) (actual time=0.322..0.736 rows=3 loops=1)

17. 0.011 0.693 ↓ 3.0 3 1

Nested Loop Left Join (cost=11.68..59.67 rows=1 width=1,584) (actual time=0.310..0.693 rows=3 loops=1)

18. 0.008 0.643 ↓ 3.0 3 1

Nested Loop Left Join (cost=11.40..55.85 rows=1 width=1,576) (actual time=0.294..0.643 rows=3 loops=1)

19. 0.008 0.599 ↓ 3.0 3 1

Nested Loop (cost=11.26..55.69 rows=1 width=1,238) (actual time=0.278..0.599 rows=3 loops=1)

20. 0.008 0.540 ↓ 3.0 3 1

Nested Loop (cost=10.98..51.32 rows=1 width=1,238) (actual time=0.259..0.540 rows=3 loops=1)

21. 0.008 0.481 ↓ 3.0 3 1

Nested Loop (cost=10.70..46.93 rows=1 width=1,231) (actual time=0.242..0.481 rows=3 loops=1)

22. 0.011 0.437 ↓ 3.0 3 1

Nested Loop (cost=10.56..46.75 rows=1 width=803) (actual time=0.224..0.437 rows=3 loops=1)

23. 0.009 0.390 ↓ 3.0 3 1

Nested Loop (cost=10.28..38.43 rows=1 width=701) (actual time=0.213..0.390 rows=3 loops=1)

24. 0.008 0.351 ↓ 3.0 3 1

Nested Loop (cost=9.85..34.24 rows=1 width=694) (actual time=0.197..0.351 rows=3 loops=1)

25. 0.008 0.298 ↓ 3.0 3 1

Nested Loop (cost=9.42..29.97 rows=1 width=652) (actual time=0.177..0.298 rows=3 loops=1)

26. 0.017 0.224 ↓ 3.0 3 1

Nested Loop (cost=8.99..21.53 rows=1 width=644) (actual time=0.156..0.224 rows=3 loops=1)

27. 0.038 0.162 ↓ 3.0 3 1

Hash Join (cost=8.56..13.08 rows=1 width=613) (actual time=0.140..0.162 rows=3 loops=1)

  • Hash Cond: (t1.patjour = t_2.id)
28. 0.029 0.029 ↓ 1.0 124 1

Seq Scan on d_labmed_rsrch_jour t1 (cost=0.00..4.20 rows=120 width=460) (actual time=0.007..0.029 rows=124 loops=1)

29. 0.005 0.095 ↓ 2.0 2 1

Hash (cost=8.55..8.55 rows=1 width=160) (actual time=0.095..0.095 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
30. 0.090 0.090 ↓ 2.0 2 1

Index Scan using i_d_labmed_patjour_lpu on d_labmed_patjour t_2 (cost=0.53..8.55 rows=1 width=160) (actual time=0.083..0.090 rows=2 loops=1)

  • Index Cond: ((lpu = '75864378'::numeric(17,0)) AND (pick_date >= (to_timestamp_simple('29.06.2020'::text, 'dd.mm.yyyy'::text))::timestamp(0) without time zone))
  • Filter: (is_picked = '1'::numeric(17,0))
31. 0.045 0.045 ↑ 1.0 1 3

Index Scan using pk_d_direction_services on d_direction_services t2 (cost=0.43..8.45 rows=1 width=39) (actual time=0.015..0.015 rows=1 loops=3)

  • Index Cond: (id = t_2.direction_service)
32. 0.066 0.066 ↑ 1.0 1 3

Index Scan using pk_d_persmedcard on d_persmedcard t3 (cost=0.42..8.44 rows=1 width=16) (actual time=0.021..0.022 rows=1 loops=3)

  • Index Cond: (id = t_2.patient)
33. 0.045 0.045 ↑ 1.0 1 3

Index Scan using pk_d_agents on d_agents t3a (cost=0.43..4.27 rows=1 width=58) (actual time=0.015..0.015 rows=1 loops=3)

  • Index Cond: (id = t3.agent)
34. 0.030 0.030 ↑ 1.0 1 3

Index Scan using pk_d_directions on d_directions t5 (cost=0.43..4.19 rows=1 width=23) (actual time=0.010..0.010 rows=1 loops=3)

  • Index Cond: (id = t2.pid)
35. 0.036 0.036 ↑ 1.0 1 3

Index Scan using pk_d_labmed_analyze on d_labmed_analyze t6 (cost=0.28..8.29 rows=1 width=110) (actual time=0.012..0.012 rows=1 loops=3)

  • Index Cond: (id = t_2.""analyse"")
36. 0.036 0.036 ↑ 1.0 1 3

Index Scan using pk_d_labmed_analyz_types on d_labmed_analyz_types t6t (cost=0.14..0.18 rows=1 width=446) (actual time=0.012..0.012 rows=1 loops=3)

  • Index Cond: (id = t6.analyz_type)
37. 0.051 0.051 ↑ 1.0 1 3

Index Scan using pk_d_lpu_services on d_lpu_services t6l (cost=0.29..4.39 rows=1 width=15) (actual time=0.016..0.017 rows=1 loops=3)

  • Index Cond: (id = t6.lpu_service)
38. 0.051 0.051 ↑ 1.0 1 3

Index Scan using pk_d_labmed_research on d_labmed_research t7 (cost=0.28..4.37 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=3)

  • Index Cond: (id = t1.research)
  • Filter: (is_inlab = '1'::numeric(1,0))
39. 0.036 0.036 ↑ 1.0 1 3

Index Scan using pk_d_payment_kind on d_payment_kind t4 (cost=0.14..0.15 rows=1 width=356) (actual time=0.012..0.012 rows=1 loops=3)

  • Index Cond: (id = t2.payment_kind)
40. 0.039 0.039 ↑ 1.0 1 3

Index Only Scan using i_d_employers_ia on d_employers t5e (cost=0.29..3.83 rows=1 width=16) (actual time=0.013..0.013 rows=1 loops=3)

  • Index Cond: (id = t5.reg_employer)
  • Heap Fetches: 3
41. 0.033 0.033 ↑ 1.0 1 3

Index Scan using pk_d_agents on d_agents t5a (cost=0.43..7.48 rows=1 width=58) (actual time=0.011..0.011 rows=1 loops=3)

  • Index Cond: (id = t5e.agent)
42. 0.018 0.018 ↑ 1.0 1 3

Index Scan using pk_d_employers on d_employers t8 (cost=0.29..5.20 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=3)

  • Index Cond: (id = t1.research_emp)
43. 0.009 0.009 ↑ 1.0 1 3

Index Scan using pk_d_agents on d_agents t8a (cost=0.43..7.48 rows=1 width=58) (actual time=0.003..0.003 rows=1 loops=3)

  • Index Cond: (id = t8.agent)
44. 0.006 0.006 ↑ 1.0 1 3

Index Scan using pk_d_employers on d_employers t9 (cost=0.29..5.20 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=3)

  • Index Cond: (id = t1.confirm_emp)
45. 0.009 0.009 ↑ 1.0 1 3

Index Scan using pk_d_agents on d_agents t9a (cost=0.43..7.48 rows=1 width=58) (actual time=0.003..0.003 rows=1 loops=3)

  • Index Cond: (id = t9.agent)
46. 0.027 0.027 ↑ 1.0 1 3

Index Scan using pk_d_labmed_biomaterial on d_labmed_biomaterial t10 (cost=0.15..8.17 rows=1 width=47) (actual time=0.009..0.009 rows=1 loops=3)

  • Index Cond: (id = t_2.biomaterial)
47. 0.027 0.027 ↑ 1.0 10 3

Seq Scan on d_labmed_locus t11 (cost=0.00..1.10 rows=10 width=236) (actual time=0.008..0.009 rows=10 loops=3)

48. 0.889 1.611 ↑ 1.0 1 3

Index Scan using i_d_urprivs_lpu_uncd on d_urprivs up_2 (cost=51.27..65.41 rows=1 width=7) (actual time=0.537..0.537 rows=1 loops=3)

  • Index Cond: ((lpu = '75864378'::numeric(17,0)) AND ((unitcode)::text = 'LABMED_PATJOUR'::text))
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 10))
  • Rows Removed by Filter: 1
49.          

SubPlan (for Index Scan)

50. 0.217 0.722 ↓ 423.3 2,540 1

Nested Loop (cost=0.72..50.69 rows=6 width=8) (actual time=0.037..0.722 rows=2,540 loops=1)

51. 0.011 0.011 ↑ 1.0 1 1

Index Scan using uk_d_users on d_users us_4 (cost=0.30..8.31 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=1)

  • Index Cond: ((username)::text = ((USER)::character varying(30))::text)
52. 0.494 0.494 ↓ 158.8 2,540 1

Index Scan using i_d_userroles_u on d_userroles ur_4 (cost=0.42..42.22 rows=16 width=16) (actual time=0.026..0.494 rows=2,540 loops=1)

  • Index Cond: (sysuser = us_4.id)
53.          

SubPlan (for Nested Loop Semi Join)

54. 0.002 0.032 ↑ 1.0 1 1

Limit (cost=11.82..11.83 rows=1 width=8) (actual time=0.031..0.032 rows=1 loops=1)

55. 0.003 0.030 ↑ 1.0 1 1

Aggregate (cost=11.82..11.83 rows=1 width=8) (actual time=0.029..0.030 rows=1 loops=1)

56. 0.027 0.027 ↓ 0.0 0 1

Index Scan using i_d_labmed_rsrch_joursp_pid on d_labmed_rsrch_joursp sp (cost=0.15..11.81 rows=3 width=0) (actual time=0.027..0.027 rows=0 loops=1)

  • Index Cond: (pid = t1.id)
  • Filter: ((nullif2(str_value) IS NOT NULL) OR (nullif2(num_value) IS NOT NULL))
  • Rows Removed by Filter: 2
57. 0.000 0.014 ↑ 1.0 1 1

Limit (cost=11.82..11.83 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1)

58. 0.001 0.014 ↑ 1.0 1 1

Aggregate (cost=11.82..11.83 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1)

59. 0.013 0.013 ↓ 0.0 0 1

Index Scan using i_d_labmed_rsrch_joursp_pid on d_labmed_rsrch_joursp sp_1 (cost=0.15..11.81 rows=3 width=0) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: (pid = t1.id)
  • Filter: ((nullif2(str_value) IS NOT NULL) OR (nullif2(num_value) IS NOT NULL))
  • Rows Removed by Filter: 2
60. 0.280 369.555 ↑ 1.0 1 3

Hash Join (cost=17,510.75..43,043.09 rows=1 width=6) (actual time=105.438..123.185 rows=1 loops=3)

  • Hash Cond: (t_4.pid = h1.id)
61. 2.266 369.194 ↑ 29.4 551 2

Hash Join (cost=17,502.30..42,829.99 rows=16,210 width=8,738) (actual time=57.419..184.597 rows=551 loops=2)

  • Hash Cond: (t_4.lpu = up_4.lpu)
62. 0.279 364.860 ↑ 29.4 551 2

Hash Left Join (cost=13,237.28..30,074.99 rows=16,210 width=1,115) (actual time=56.370..182.430 rows=551 loops=2)

  • Hash Cond: (t_4.hosp_outcome = t14.id)
63. 0.261 364.560 ↑ 29.4 551 2

Hash Left Join (cost=13,235.90..30,020.73 rows=16,210 width=575) (actual time=56.355..182.280 rows=551 loops=2)

  • Hash Cond: (t_4.hosp_result = t5_2.id)
64. 0.379 364.274 ↑ 29.4 551 2

Hash Join (cost=13,234.54..29,965.84 rows=16,210 width=35) (actual time=56.335..182.137 rows=551 loops=2)

  • Hash Cond: (t_4.payment_kind = t4_2.id)
65. 0.403 363.884 ↑ 29.4 551 2

Hash Join (cost=13,233.29..29,905.64 rows=16,210 width=42) (actual time=56.322..181.942 rows=551 loops=2)

  • Hash Cond: (t1_2.dp_type = t3_1.id)
66. 0.559 363.474 ↑ 29.4 551 2

Hash Join (cost=13,232.18..29,825.91 rows=16,210 width=49) (actual time=56.315..181.737 rows=551 loops=2)

  • Hash Cond: (t_4.dep = t1_2.id)
67. 1.500 362.360 ↑ 29.4 551 2

Hash Join (cost=13,177.13..29,728.17 rows=16,210 width=43) (actual time=56.033..181.180 rows=551 loops=2)

  • Hash Cond: (t_4.pid = t12.id)
68. 250.244 274.528 ↑ 29.4 551 2

Bitmap Heap Scan on d_hosp_history_deps t_4 (cost=4,667.82..21,176.30 rows=16,210 width=118) (actual time=12.646..137.264 rows=551 loops=2)

  • Recheck Cond: (date_in <= t_2.reg_time)
  • Filter: ((date_out > t_2.reg_time) OR (nullif2(date_out) IS NULL))
  • Rows Removed by Filter: 143,893
  • Heap Blocks: exact=7,498
69. 24.284 24.284 ↓ 3.0 144,444 2

Bitmap Index Scan on uk_d_hosp_history_deps (cost=0.00..4,663.77 rows=48,149 width=0) (actual time=12.142..12.142 rows=144,444 loops=2)

  • Index Cond: (date_in <= t_2.reg_time)
70. 30.665 86.332 ↑ 1.0 108,414 1

Hash (cost=7,154.14..7,154.14 rows=108,414 width=29) (actual time=86.332..86.332 rows=108,414 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 7,457kB
71. 55.667 55.667 ↑ 1.0 108,414 1

Seq Scan on d_hosp_histories t12 (cost=0.00..7,154.14 rows=108,414 width=29) (actual time=0.016..55.667 rows=108,414 loops=1)

72. 0.250 0.555 ↑ 1.0 1,291 1

Hash (cost=38.91..38.91 rows=1,291 width=21) (actual time=0.555..0.555 rows=1,291 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 85kB
73. 0.305 0.305 ↑ 1.0 1,291 1

Seq Scan on d_deps t1_2 (cost=0.00..38.91 rows=1,291 width=21) (actual time=0.011..0.305 rows=1,291 loops=1)

74. 0.002 0.007 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=18) (actual time=0.007..0.007 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
75. 0.005 0.005 ↑ 1.0 5 1

Seq Scan on d_deps_types t3_1 (cost=0.00..1.05 rows=5 width=18) (actual time=0.004..0.005 rows=5 loops=1)

76. 0.004 0.011 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=18) (actual time=0.011..0.011 rows=11 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
77. 0.007 0.007 ↑ 1.0 11 1

Seq Scan on d_payment_kind t4_2 (cost=0.00..1.11 rows=11 width=18) (actual time=0.006..0.007 rows=11 loops=1)

78. 0.011 0.025 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=558) (actual time=0.025..0.025 rows=16 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
79. 0.014 0.014 ↑ 1.0 16 1

Seq Scan on d_hosp_results t5_2 (cost=0.00..1.16 rows=16 width=558) (actual time=0.011..0.014 rows=16 loops=1)

80. 0.009 0.021 ↑ 1.0 17 1

Hash (cost=1.17..1.17 rows=17 width=558) (actual time=0.021..0.021 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
81. 0.012 0.012 ↑ 1.0 17 1

Seq Scan on d_hosp_outcomes t14 (cost=0.00..1.17 rows=17 width=558) (actual time=0.010..0.012 rows=17 loops=1)

82. 0.006 2.068 ↑ 1.0 4 1

Hash (cost=4,264.97..4,264.97 rows=4 width=7) (actual time=2.068..2.068 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
83. 0.039 2.062 ↑ 1.0 4 1

HashAggregate (cost=4,264.93..4,264.97 rows=4 width=7) (actual time=2.061..2.062 rows=4 loops=1)

  • Group Key: up_4.lpu
84. 1.241 2.023 ↑ 3.9 146 1

Bitmap Heap Scan on d_urprivs up_4 (cost=87.92..4,263.50 rows=569 width=7) (actual time=1.514..2.023 rows=146 loops=1)

  • Recheck Cond: ((unitcode)::text = 'HOSP_HISTORY_DEPS'::text)
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 7))
  • Rows Removed by Filter: 35
  • Heap Blocks: exact=45
85. 0.045 0.045 ↑ 6.3 181 1

Bitmap Index Scan on i_d_urprivs_ur (cost=0.00..37.07 rows=1,135 width=0) (actual time=0.045..0.045 rows=181 loops=1)

  • Index Cond: ((unitcode)::text = 'HOSP_HISTORY_DEPS'::text)
86.          

SubPlan (for Bitmap Heap Scan)

87. 0.222 0.737 ↓ 423.3 2,540 1

Nested Loop (cost=0.72..50.69 rows=6 width=8) (actual time=0.041..0.737 rows=2,540 loops=1)

88. 0.011 0.011 ↑ 1.0 1 1

Index Scan using uk_d_users on d_users us_3 (cost=0.30..8.31 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: ((username)::text = ((USER)::character varying(30))::text)
89. 0.504 0.504 ↓ 158.8 2,540 1

Index Scan using i_d_userroles_u on d_userroles ur_3 (cost=0.42..42.22 rows=16 width=16) (actual time=0.029..0.504 rows=2,540 loops=1)

  • Index Cond: (sysuser = us_3.id)
90. 0.009 0.081 ↑ 1.0 1 3

Hash (cost=8.44..8.44 rows=1 width=8) (actual time=0.027..0.027 rows=1 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
91. 0.072 0.072 ↑ 1.0 1 3

Index Scan using i_d_hosp_histories_dc on d_hosp_histories h1 (cost=0.42..8.44 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=3)

  • Index Cond: (diseasecase = t2.diseasecase)
  • Filter: (discard_status = '0'::numeric(1,0))
92. 0.006 0.012 ↓ 0.0 0 3

Nested Loop (cost=0.71..16.75 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=3)

93. 0.006 0.006 ↓ 0.0 0 3

Index Scan using pk_d_visits on d_visits v (cost=0.43..8.45 rows=1 width=7) (actual time=0.002..0.002 rows=0 loops=3)

  • Index Cond: (id = t5.reg_visit)
94. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_d_cablab on d_cablab cl (cost=0.28..8.30 rows=1 width=16) (never executed)

  • Index Cond: (id = v.cablab)
95. 26,625.065 44,341.929 ↑ 1.0 1,039,738 3

Hash Join (cost=77,778.49..2,134,937.72 rows=1,039,754 width=825) (actual time=27.140..14,780.643 rows=1,039,738 loops=3)

  • Hash Cond: (t_3.cid = up_3.catalog)
96. 1,140.975 17,713.914 ↓ 1.0 1,039,758 3

Hash Left Join (cost=73,503.15..512,545.21 rows=1,039,754 width=555) (actual time=26.125..5,904.638 rows=1,039,758 loops=3)

  • Hash Cond: (t_3.rhesus = t5_1.rh_code)
97. 1,553.813 16,572.927 ↓ 1.0 1,039,758 3

Hash Left Join (cost=73,502.08..509,807.85 rows=1,039,754 width=517) (actual time=26.116..5,524.309 rows=1,039,758 loops=3)

  • Hash Cond: (t_3.reg_division = t4_1.id)
98. 1,039.494 15,018.936 ↓ 1.0 1,039,758 3

Hash Left Join (cost=73,485.07..507,044.47 rows=1,039,754 width=513) (actual time=26.051..5,006.312 rows=1,039,758 loops=3)

  • Hash Cond: (t_3.bloodgroupe = t2_1.id)
99. 1,573.279 13,979.427 ↓ 1.0 1,039,758 3

Hash Left Join (cost=73,483.98..504,308.82 rows=1,039,754 width=455) (actual time=26.039..4,659.809 rows=1,039,758 loops=3)

  • Hash Cond: (t_3.emp_id = t6_1.id)
100. 5,059.011 12,328.239 ↓ 1.0 1,039,758 3

Merge Join (cost=335.72..421,474.90 rows=1,039,754 width=405) (actual time=0.044..4,109.413 rows=1,039,758 loops=3)

  • Merge Cond: (t_3.agent = t1_1.id)
101. 1,905.711 1,905.711 ↓ 1.0 1,039,758 3

Index Scan using i_d_persmedcard_ag on d_persmedcard t_3 (cost=0.42..80,194.61 rows=1,039,754 width=260) (actual time=0.021..635.237 rows=1,039,758 loops=3)

102. 5,363.517 5,363.517 ↓ 1.0 5,083,620 3

Index Scan using pk_d_agents on d_agents t1_1 (cost=0.43..315,604.18 rows=5,083,617 width=153) (actual time=0.010..1,787.839 rows=5,083,620 loops=3)

103. 4.044 77.909 ↓ 1.0 9,739 1

Hash (cost=73,026.56..73,026.56 rows=9,736 width=58) (actual time=77.909..77.909 rows=9,739 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 1,004kB
104. 4.350 73.865 ↓ 1.0 9,739 1

Nested Loop Left Join (cost=0.43..73,026.56 rows=9,736 width=58) (actual time=0.025..73.865 rows=9,739 loops=1)

105. 1.342 1.342 ↓ 1.0 9,739 1

Seq Scan on d_employers t6_1 (cost=0.00..245.36 rows=9,736 width=16) (actual time=0.005..1.342 rows=9,739 loops=1)

106. 68.173 68.173 ↑ 1.0 1 9,739

Index Scan using pk_d_agents on d_agents t7_1 (cost=0.43..7.48 rows=1 width=58) (actual time=0.007..0.007 rows=1 loops=9,739)

  • Index Cond: (id = t6_1.agent)
107. 0.003 0.015 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=76) (actual time=0.015..0.015 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
108. 0.012 0.012 ↑ 1.0 4 1

Seq Scan on d_bloodgroupe t2_1 (cost=0.00..1.04 rows=4 width=76) (actual time=0.011..0.012 rows=4 loops=1)

109. 0.095 0.178 ↑ 1.0 445 1

Hash (cost=11.45..11.45 rows=445 width=12) (actual time=0.178..0.178 rows=445 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 28kB
110. 0.083 0.083 ↑ 1.0 445 1

Seq Scan on d_divisions t4_1 (cost=0.00..11.45 rows=445 width=12) (actual time=0.006..0.083 rows=445 loops=1)

111. 0.003 0.012 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=48) (actual time=0.012..0.012 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
112. 0.009 0.009 ↑ 1.0 3 1

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

113. 0.009 2.950 ↑ 46.3 10 1

Hash (cost=4,269.56..4,269.56 rows=463 width=8) (actual time=2.950..2.950 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
114. 0.214 2.941 ↑ 42.1 11 1

HashAggregate (cost=4,264.93..4,269.56 rows=463 width=8) (actual time=2.936..2.941 rows=11 loops=1)

  • Group Key: up_3.catalog
115. 1.912 2.727 ↓ 1.5 858 1

Bitmap Heap Scan on d_urprivs up_3 (cost=87.92..4,263.50 rows=569 width=8) (actual time=1.601..2.727 rows=858 loops=1)

  • Recheck Cond: ((unitcode)::text = 'PERSMEDCARD'::text)
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 4))
  • Rows Removed by Filter: 136
  • Heap Blocks: exact=72
116. 0.106 0.106 ↑ 1.1 994 1

Bitmap Index Scan on i_d_urprivs_ur (cost=0.00..37.07 rows=1,135 width=0) (actual time=0.106..0.106 rows=994 loops=1)

  • Index Cond: ((unitcode)::text = 'PERSMEDCARD'::text)
117.          

SubPlan (for Bitmap Heap Scan)

118. 0.210 0.709 ↓ 423.3 2,540 1

Nested Loop (cost=0.72..50.69 rows=6 width=8) (actual time=0.038..0.709 rows=2,540 loops=1)

119. 0.011 0.011 ↑ 1.0 1 1

Index Scan using uk_d_users on d_users us_2 (cost=0.30..8.31 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: ((username)::text = ((USER)::character varying(30))::text)
120. 0.488 0.488 ↓ 158.8 2,540 1

Index Scan using i_d_userroles_u on d_userroles ur_2 (cost=0.42..42.22 rows=16 width=16) (actual time=0.026..0.488 rows=2,540 loops=1)

  • Index Cond: (sysuser = us_2.id)
121. 0.120 0.120 ↑ 1.0 1 3

Index Scan using pk_d_direction_services on d_direction_services t_1 (cost=0.43..8.45 rows=1 width=893) (actual time=0.040..0.040 rows=1 loops=3)

  • Index Cond: (id = t_2.direction_service)
122. 0.051 0.051 ↑ 1.0 1 3

Index Scan using pk_d_directions on d_directions t (cost=0.43..0.51 rows=1 width=1,473) (actual time=0.017..0.017 rows=1 loops=3)

  • Index Cond: (id = t_1.pid)
123. 0.947 1.671 ↑ 4.0 1 3

Index Scan using i_d_urprivs_lpu_uncd on d_urprivs up (cost=51.27..51.56 rows=4 width=7) (actual time=0.557..0.557 rows=1 loops=3)

  • Index Cond: ((lpu = t.lpu) AND ((unitcode)::text = 'DIRECTIONS'::text))
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 2))
  • Rows Removed by Filter: 1
124.          

SubPlan (for Index Scan)

125. 0.214 0.724 ↓ 423.3 2,540 1

Nested Loop (cost=0.72..50.69 rows=6 width=8) (actual time=0.044..0.724 rows=2,540 loops=1)

126. 0.012 0.012 ↑ 1.0 1 1

Index Scan using uk_d_users on d_users us (cost=0.30..8.31 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=1)

  • Index Cond: ((username)::text = ((USER)::character varying(30))::text)
127. 0.498 0.498 ↓ 158.8 2,540 1

Index Scan using i_d_userroles_u on d_userroles ur (cost=0.42..42.22 rows=16 width=16) (actual time=0.030..0.498 rows=2,540 loops=1)

  • Index Cond: (sysuser = us.id)
128. 0.868 1.533 ↑ 4.0 1 3

Index Scan using i_d_urprivs_lpu_uncd on d_urprivs up_1 (cost=51.27..51.55 rows=4 width=7) (actual time=0.511..0.511 rows=1 loops=3)

  • Index Cond: ((lpu = t_1.lpu) AND ((unitcode)::text = 'DIRECTION_SERVICES'::text))
  • Filter: (((username)::text = ((USER)::character varying)::text) OR (hashed SubPlan 3))
  • Rows Removed by Filter: 1
129.          

SubPlan (for Index Scan)

130. 0.215 0.665 ↓ 423.3 2,540 1

Nested Loop (cost=0.72..50.69 rows=6 width=8) (actual time=0.037..0.665 rows=2,540 loops=1)

131. 0.007 0.007 ↑ 1.0 1 1

Index Scan using uk_d_users on d_users us_1 (cost=0.30..8.31 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=1)

  • Index Cond: ((username)::text = ((USER)::character varying(30))::text)
132. 0.443 0.443 ↓ 158.8 2,540 1

Index Scan using i_d_userroles_u on d_userroles ur_1 (cost=0.42..42.22 rows=16 width=16) (actual time=0.029..0.443 rows=2,540 loops=1)

  • Index Cond: (sysuser = us_1.id)
133. 5.187 5.187 ↓ 0.0 0 3

CTE Scan on research r (cost=0.00..20.00 rows=1,000 width=32) (actual time=1.729..1.729 rows=0 loops=3)

Planning time : 20.210 ms
Execution time : 45,383.195 ms