explain.depesz.com

PostgreSQL's explain analyze made readable

Result: H7oO

Settings
# exclusive inclusive rows x rows loops node
1. 0.684 5,382.019 ↑ 67.9 315 1

WindowAgg (cost=269,456,470.98..273,568,654.59 rows=21,375 width=748) (actual time=772.984..5,382.019 rows=315 loops=1)

2.          

CTE lpu

3. 4,649.044 4,907.372 ↑ 71,234,860.0 1 1

Nested Loop (cost=84,638.27..269,265,513.51 rows=71,234,860 width=150) (actual time=309.220..4,907.372 rows=1 loops=1)

  • Join Filter: (t_6.id = (COALESCE(d_pkg_agent_addrs.get_actual_on_date(t_5.agent, to_timestamp_simple('31.10.2020'::text, (d_pkg_std.frm_d())::text), '1'::numeric, 'ID'::character varying), d_pkg_agent_addrs.get_actual_on_date(t_5.agent, to_timestamp_simple('31.10.2020'::text, (d_pkg_std.frm_d())::text), '0'::numeric, 'ID'::character varying)))::text)
  • Rows Removed by Join Filter: 146,878
4. 77.117 258.328 ↓ 1.0 146,879 1

Hash Join (cost=78,374.85..85,118.16 rows=146,876 width=14) (actual time=138.734..258.328 rows=146,879 loops=1)

  • Hash Cond: (t_6.cid = up_6.catalog)
5. 42.521 42.521 ↓ 1.0 146,879 1

Seq Scan on d_agent_addrs t_6 (cost=0.00..4,723.76 rows=146,876 width=21) (actual time=0.008..42.521 rows=146,879 loops=1)

6. 0.035 138.690 ↑ 36.7 109 1

Hash (cost=78,324.83..78,324.83 rows=4,002 width=7) (actual time=138.690..138.690 rows=109 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 37kB
7. 8.644 138.655 ↑ 36.4 110 1

HashAggregate (cost=78,284.81..78,324.83 rows=4,002 width=7) (actual time=138.606..138.655 rows=110 loops=1)

  • Group Key: up_6.catalog
8. 118.314 130.011 ↓ 1.3 25,227 1

Bitmap Heap Scan on d_urprivs up_6 (cost=1,466.95..78,236.69 rows=19,246 width=7) (actual time=16.348..130.011 rows=25,227 loops=1)

  • Recheck Cond: ((unitcode)::text = 'AGENT_ADDRS'::text)
  • Filter: (((username)::text = ((SESSION_USER)::character varying)::text) OR (hashed SubPlan 2))
  • Rows Removed by Filter: 13,429
  • Heap Blocks: exact=24,926
9. 11.326 11.326 ↓ 1.5 58,350 1

Bitmap Index Scan on i_d_urprivs_ur (cost=0.00..1,363.96 rows=38,320 width=0) (actual time=11.326..11.326 rows=58,350 loops=1)

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

SubPlan (for Bitmap Heap Scan)

11. 0.093 0.371 ↓ 77.7 855 1

Nested Loop (cost=0.58..98.16 rows=11 width=7) (actual time=0.053..0.371 rows=855 loops=1)

12. 0.014 0.014 ↑ 1.0 1 1

Index Scan using uk_d_users on d_users us_1 (cost=0.29..8.30 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1)

  • Index Cond: ((username)::text = ((SESSION_USER)::character varying(30))::text)
13. 0.264 0.264 ↓ 19.9 855 1

Index Scan using i_d_userroles_u on d_userroles ur_1 (cost=0.29..89.42 rows=43 width=15) (actual time=0.036..0.264 rows=855 loops=1)

  • Index Cond: (sysuser = us_1.id)
14. 0.000 0.000 ↑ 970.0 1 146,879

Materialize (cost=6,263.42..268,801.28 rows=970 width=118) (actual time=0.000..0.000 rows=1 loops=146,879)

15. 0.014 11.707 ↑ 970.0 1 1

Hash Join (cost=6,263.42..268,796.43 rows=970 width=118) (actual time=8.714..11.707 rows=1 loops=1)

  • Hash Cond: (t_5.version = up_5.version)
16. 2.249 4.684 ↑ 970.0 1 1

Nested Loop Semi Join (cost=0.50..262,520.18 rows=970 width=125) (actual time=1.693..4.684 rows=1 loops=1)

  • Join Filter: (t_5.id = (t_7.column_value)::text)
  • Rows Removed by Join Filter: 1,939
17. 0.495 0.495 ↑ 1.0 1,940 1

Seq Scan on d_lpudict t_5 (cost=0.00..96.40 rows=1,940 width=125) (actual time=0.009..0.495 rows=1,940 loops=1)

18. 1.940 1.940 ↑ 1,000.0 1 1,940

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

19. 0.012 7.009 ↑ 32.8 17 1

Hash (cost=6,255.95..6,255.95 rows=557 width=7) (actual time=7.009..7.009 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
20. 0.298 6.997 ↑ 32.8 17 1

HashAggregate (cost=6,250.38..6,255.95 rows=557 width=7) (actual time=6.989..6.997 rows=17 loops=1)

  • Group Key: up_5.version
21. 6.128 6.699 ↑ 1.0 843 1

Bitmap Heap Scan on d_urprivs up_5 (cost=159.57..6,248.27 rows=845 width=7) (actual time=0.950..6.699 rows=843 loops=1)

  • Recheck Cond: ((unitcode)::text = 'LPUDICT'::text)
  • Filter: (((username)::text = ((SESSION_USER)::character varying)::text) OR (hashed SubPlan 1))
  • Rows Removed by Filter: 451
  • Heap Blocks: exact=454
22. 0.205 0.205 ↑ 1.3 1,294 1

Bitmap Index Scan on i_d_urprivs_ur (cost=0.00..61.18 rows=1,683 width=0) (actual time=0.205..0.205 rows=1,294 loops=1)

  • Index Cond: ((unitcode)::text = 'LPUDICT'::text)
23.          

SubPlan (for Bitmap Heap Scan)

24. 0.091 0.366 ↓ 77.7 855 1

Nested Loop (cost=0.58..98.16 rows=11 width=7) (actual time=0.042..0.366 rows=855 loops=1)

25. 0.009 0.009 ↑ 1.0 1 1

Index Scan using uk_d_users on d_users us (cost=0.29..8.30 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: ((username)::text = ((SESSION_USER)::character varying(30))::text)
26. 0.266 0.266 ↓ 19.9 855 1

Index Scan using i_d_userroles_u on d_userroles ur (cost=0.29..89.42 rows=43 width=15) (actual time=0.032..0.266 rows=855 loops=1)

  • Index Cond: (sysuser = us.id)
27. 0.190 5,371.255 ↑ 67.9 315 1

Hash Join (cost=190,957.47..1,883,504.03 rows=21,375 width=733) (actual time=772.874..5,371.255 rows=315 loops=1)

  • Hash Cond: (t_2.pregnancy = t_3.id)
28. 0.071 5,324.315 ↑ 201.8 182 1

Hash Join (cost=112,713.05..1,804,908.16 rows=36,719 width=653) (actual time=726.098..5,324.315 rows=182 loops=1)

  • Hash Cond: (t4.id = t3.id)
29. 4,907.376 4,907.376 ↑ 71,234,860.0 1 1

CTE Scan on lpu t4 (cost=0.00..1,424,697.20 rows=71,234,860 width=566) (actual time=309.223..4,907.376 rows=1 loops=1)

30. 0.169 416.868 ↓ 197.0 197 1

Hash (cost=112,713.03..112,713.03 rows=1 width=119) (actual time=416.868..416.868 rows=197 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 37kB
31. 0.307 416.699 ↓ 197.0 197 1

Nested Loop Semi Join (cost=84,357.06..112,713.03 rows=1 width=119) (actual time=88.543..416.699 rows=197 loops=1)

32. 17.271 413.043 ↓ 197.0 197 1

Nested Loop (cost=84,258.32..112,519.82 rows=1 width=126) (actual time=87.968..413.043 rows=197 loops=1)

  • Join Filter: (t1.id = t.pid)
  • Rows Removed by Join Filter: 102,860
33. 62.062 379.067 ↑ 1.2 257 1

Hash Join (cost=84,159.58..111,507.61 rows=304 width=147) (actual time=76.938..379.067 rows=257 loops=1)

  • Hash Cond: (t_1.id = t1.id)
34. 143.343 314.130 ↓ 1.0 494,438 1

Hash Join (cost=81,575.53..107,066.39 rows=494,436 width=7) (actual time=73.941..314.130 rows=494,438 loops=1)

  • Hash Cond: (t_1.cid = up_1.catalog)
35. 96.882 96.882 ↓ 1.0 494,440 1

Seq Scan on d_agents t_1 (cost=0.00..18,692.36 rows=494,436 width=14) (actual time=0.010..96.882 rows=494,440 loops=1)

36. 0.031 73.905 ↑ 36.8 109 1

Hash (cost=81,525.38..81,525.38 rows=4,012 width=7) (actual time=73.905..73.905 rows=109 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 37kB
37. 7.348 73.874 ↑ 36.5 110 1

HashAggregate (cost=81,485.26..81,525.38 rows=4,012 width=7) (actual time=73.834..73.874 rows=110 loops=1)

  • Group Key: up_1.catalog
38. 57.183 66.526 ↓ 1.4 28,916 1

Bitmap Heap Scan on d_urprivs up_1 (cost=1,569.12..81,433.50 rows=20,706 width=7) (actual time=12.987..66.526 rows=28,916 loops=1)

  • Recheck Cond: ((unitcode)::text = 'AGENTS'::text)
  • Filter: (((username)::text = ((SESSION_USER)::character varying)::text) OR (hashed SubPlan 7))
  • Rows Removed by Filter: 14,260
  • Heap Blocks: exact=24,992
39. 9.043 9.043 ↓ 1.6 65,259 1

Bitmap Index Scan on i_d_urprivs_ur (cost=0.00..1,465.76 rows=41,227 width=0) (actual time=9.043..9.043 rows=65,259 loops=1)

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

SubPlan (for Bitmap Heap Scan)

41. 0.069 0.300 ↓ 77.7 855 1

Nested Loop (cost=0.58..98.16 rows=11 width=7) (actual time=0.042..0.300 rows=855 loops=1)

42. 0.011 0.011 ↑ 1.0 1 1

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

  • Index Cond: ((username)::text = ((SESSION_USER)::character varying(30))::text)
43. 0.220 0.220 ↓ 19.9 855 1

Index Scan using i_d_userroles_u on d_userroles ur_4 (cost=0.29..89.42 rows=43 width=15) (actual time=0.029..0.220 rows=855 loops=1)

  • Index Cond: (sysuser = us_4.id)
44. 0.109 2.875 ↑ 1.2 257 1

Hash (cost=2,580.25..2,580.25 rows=304 width=140) (actual time=2.875..2.875 rows=257 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 52kB
45. 0.107 2.766 ↑ 1.2 257 1

Hash Join (cost=121.07..2,580.25 rows=304 width=140) (actual time=0.646..2.766 rows=257 loops=1)

  • Hash Cond: (t_2.lpu_in = t3.id)
46. 0.200 2.059 ↑ 1.2 257 1

Nested Loop (cost=0.42..2,458.80 rows=304 width=133) (actual time=0.040..2.059 rows=257 loops=1)

47. 0.060 0.060 ↑ 1.2 257 1

Seq Scan on d_pregnant_cards t_2 (cost=0.00..9.04 rows=304 width=124) (actual time=0.016..0.060 rows=257 loops=1)

48. 1.799 1.799 ↑ 1.0 1 257

Index Scan using pk_d_agents on d_agents t1 (cost=0.42..8.06 rows=1 width=57) (actual time=0.007..0.007 rows=1 loops=257)

  • Index Cond: (id = t_2.agent)
49. 0.279 0.600 ↑ 1.0 1,940 1

Hash (cost=96.40..96.40 rows=1,940 width=7) (actual time=0.600..0.600 rows=1,940 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 91kB
50. 0.321 0.321 ↑ 1.0 1,940 1

Seq Scan on d_lpudict t3 (cost=0.00..96.40 rows=1,940 width=7) (actual time=0.005..0.321 rows=1,940 loops=1)

51. 4.722 16.705 ↓ 100.2 401 257

Materialize (cost=98.74..993.98 rows=4 width=7) (actual time=0.003..0.065 rows=401 loops=257)

52. 0.109 11.983 ↓ 100.2 401 1

Nested Loop Semi Join (cost=98.74..993.96 rows=4 width=7) (actual time=0.652..11.983 rows=401 loops=1)

53. 5.859 5.859 ↓ 100.2 401 1

Seq Scan on d_agent_pregnancy t (cost=0.00..595.16 rows=4 width=49) (actual time=0.059..5.859 rows=401 loops=1)

  • Filter: (((end_date >= to_timestamp_simple('01.10.2020'::text, 'dd.mm.yyyy'::text)) AND (end_date <= to_timestamp_simple('31.10.2020'::text, 'dd.mm.yyyy'::text))) OR (nullif2(end_date) IS NULL))
  • Rows Removed by Filter: 364
54. 5.707 6.015 ↑ 4.0 1 401

Index Only Scan using uk_d_urprivs on d_urprivs up (cost=98.74..134.96 rows=4 width=7) (actual time=0.015..0.015 rows=1 loops=401)

  • Index Cond: ((unitcode = 'AGENT_PREGNANCY'::text) AND (catalog = t.cid))
  • Filter: (((username)::text = ((SESSION_USER)::character varying)::text) OR (hashed SubPlan 6))
  • Heap Fetches: 401
55.          

SubPlan (for Index Only Scan)

56. 0.073 0.308 ↓ 77.7 855 1

Nested Loop (cost=0.58..98.16 rows=11 width=7) (actual time=0.051..0.308 rows=855 loops=1)

57. 0.013 0.013 ↑ 1.0 1 1

Index Scan using uk_d_users on d_users us_3 (cost=0.29..8.30 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: ((username)::text = ((SESSION_USER)::character varying(30))::text)
58. 0.222 0.222 ↓ 19.9 855 1

Index Scan using i_d_userroles_u on d_userroles ur_3 (cost=0.29..89.42 rows=43 width=15) (actual time=0.036..0.222 rows=855 loops=1)

  • Index Cond: (sysuser = us_3.id)
59. 3.064 3.349 ↑ 1.0 1 197

Index Only Scan using uk_d_urprivs on d_urprivs up_2 (cost=98.74..193.21 rows=1 width=7) (actual time=0.017..0.017 rows=1 loops=197)

  • Index Cond: ((unitcode = 'PREGNANT_CARDS'::text) AND (version = t_2.version))
  • Filter: (((username)::text = ((SESSION_USER)::character varying)::text) OR (hashed SubPlan 8))
  • Heap Fetches: 197
60.          

SubPlan (for Index Only Scan)

61. 0.070 0.285 ↓ 77.7 855 1

Nested Loop (cost=0.58..98.16 rows=11 width=7) (actual time=0.047..0.285 rows=855 loops=1)

62. 0.011 0.011 ↑ 1.0 1 1

Index Scan using uk_d_users on d_users us_5 (cost=0.29..8.30 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: ((username)::text = ((SESSION_USER)::character varying(30))::text)
63. 0.204 0.204 ↓ 19.9 855 1

Index Scan using i_d_userroles_u on d_userroles ur_5 (cost=0.29..89.42 rows=43 width=15) (actual time=0.033..0.204 rows=855 loops=1)

  • Index Cond: (sysuser = us_5.id)
64. 0.164 46.750 ↑ 1.0 443 1

Hash (cost=78,238.88..78,238.88 rows=443 width=94) (actual time=46.750..46.750 rows=443 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 67kB
65. 0.295 46.586 ↑ 1.0 443 1

Hash Join (cost=18,950.76..78,238.88 rows=443 width=94) (actual time=18.878..46.586 rows=443 loops=1)

  • Hash Cond: (t_3.id = t_4.pid)
66. 1.913 28.533 ↓ 1.0 765 1

Merge Semi Join (cost=140.76..59,394.00 rows=761 width=7) (actual time=1.091..28.533 rows=765 loops=1)

  • Merge Cond: (t_3.cid = up_3.catalog)
67. 0.260 0.260 ↓ 1.0 765 1

Index Scan using i_d_agent_pregnancy_cid on d_agent_pregnancy t_3 (cost=0.28..77.08 rows=761 width=14) (actual time=0.011..0.260 rows=765 loops=1)

68. 25.712 26.360 ↑ 1.5 11,649 1

Index Only Scan using uk_d_urprivs on d_urprivs up_3 (cost=98.74..124,715.69 rows=17,919 width=7) (actual time=1.074..26.360 rows=11,649 loops=1)

  • Index Cond: (unitcode = 'AGENT_PREGNANCY'::text)
  • Filter: (((username)::text = ((SESSION_USER)::character varying)::text) OR (hashed SubPlan 9))
  • Rows Removed by Filter: 6,372
  • Heap Fetches: 18,021
69.          

SubPlan (for Index Only Scan)

70. 0.092 0.648 ↓ 77.7 855 1

Nested Loop (cost=0.58..98.16 rows=11 width=7) (actual time=0.061..0.648 rows=855 loops=1)

71. 0.025 0.025 ↑ 1.0 1 1

Index Scan using uk_d_users on d_users us_6 (cost=0.29..8.30 rows=1 width=8) (actual time=0.024..0.025 rows=1 loops=1)

  • Index Cond: ((username)::text = ((SESSION_USER)::character varying(30))::text)
72. 0.531 0.531 ↓ 19.9 855 1

Index Scan using i_d_userroles_u on d_userroles ur_6 (cost=0.29..89.42 rows=43 width=15) (actual time=0.035..0.531 rows=855 loops=1)

  • Index Cond: (sysuser = us_6.id)
73. 0.152 17.758 ↑ 1.0 443 1

Hash (cost=18,804.47..18,804.47 rows=443 width=87) (actual time=17.758..17.758 rows=443 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 64kB
74. 1.075 17.606 ↑ 1.0 443 1

Merge Semi Join (cost=99.02..18,804.47 rows=443 width=87) (actual time=0.723..17.606 rows=443 loops=1)

  • Merge Cond: (t_4.cid = up_4.catalog)
75. 0.313 0.313 ↑ 1.0 443 1

Index Scan using i_d_agent_prgn_mkbs_cid on d_agent_prgn_mkbs t_4 (cost=0.28..65.74 rows=443 width=94) (actual time=0.040..0.313 rows=443 loops=1)

  • Filter: (diagn_type = '0'::numeric)
  • Rows Removed by Filter: 124
76. 15.902 16.218 ↑ 2.1 6,384 1

Index Only Scan using uk_d_urprivs on d_urprivs up_4 (cost=98.74..97,625.57 rows=13,539 width=7) (actual time=0.681..16.218 rows=6,384 loops=1)

  • Index Cond: (unitcode = 'AGENT_PRGN_MKBS'::text)
  • Filter: (((username)::text = ((SESSION_USER)::character varying)::text) OR (hashed SubPlan 10))
  • Rows Removed by Filter: 3,691
  • Heap Fetches: 10,075
77.          

SubPlan (for Index Only Scan)

78. 0.092 0.316 ↓ 77.7 855 1

Nested Loop (cost=0.58..98.16 rows=11 width=7) (actual time=0.042..0.316 rows=855 loops=1)

79. 0.009 0.009 ↑ 1.0 1 1

Index Scan using uk_d_users on d_users us_7 (cost=0.29..8.30 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: ((username)::text = ((SESSION_USER)::character varying(30))::text)
80. 0.215 0.215 ↓ 19.9 855 1

Index Scan using i_d_userroles_u on d_userroles ur_7 (cost=0.29..89.42 rows=43 width=15) (actual time=0.031..0.215 rows=855 loops=1)

  • Index Cond: (sysuser = us_7.id)
81.          

SubPlan (for WindowAgg)

82. 0.630 10.080 ↑ 1.0 1 315

Aggregate (cost=113.17..113.18 rows=1 width=32) (actual time=0.032..0.032 rows=1 loops=315)

83. 0.930 9.450 ↑ 1.0 1 315

Nested Loop Semi Join (cost=103.05..113.16 rows=1 width=87) (actual time=0.016..0.030 rows=1 loops=315)

84. 1.575 2.520 ↑ 1.0 1 315

Bitmap Heap Scan on d_agent_prgn_mkbs t_8 (cost=4.31..13.44 rows=1 width=94) (actual time=0.007..0.008 rows=1 loops=315)

  • Recheck Cond: (pid = t_3.id)
  • Filter: (diagn_type <> '0'::numeric(1,0))
  • Rows Removed by Filter: 10
  • Heap Blocks: exact=1,032
85. 0.945 0.945 ↓ 2.8 11 315

Bitmap Index Scan on i_d_agent_prgn_mkbs_pid (cost=0.00..4.31 rows=4 width=0) (actual time=0.003..0.003 rows=11 loops=315)

  • Index Cond: (pid = t_3.id)
86. 5.632 6.000 ↑ 3.0 1 400

Index Only Scan using uk_d_urprivs on d_urprivs up_7 (cost=98.74..126.90 rows=3 width=7) (actual time=0.015..0.015 rows=1 loops=400)

  • Index Cond: ((unitcode = 'AGENT_PRGN_MKBS'::text) AND (catalog = t_8.cid))
  • Filter: (((username)::text = ((SESSION_USER)::character varying)::text) OR (hashed SubPlan 4))
  • Heap Fetches: 400
87.          

SubPlan (for Index Only Scan)

88. 0.093 0.368 ↓ 77.7 855 1

Nested Loop (cost=0.58..98.16 rows=11 width=7) (actual time=0.046..0.368 rows=855 loops=1)

89. 0.013 0.013 ↑ 1.0 1 1

Index Scan using uk_d_users on d_users us_2 (cost=0.29..8.30 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: ((username)::text = ((SESSION_USER)::character varying(30))::text)
90. 0.262 0.262 ↓ 19.9 855 1

Index Scan using i_d_userroles_u on d_userroles ur_2 (cost=0.29..89.42 rows=43 width=15) (actual time=0.032..0.262 rows=855 loops=1)

  • Index Cond: (sysuser = us_2.id)
Planning time : 18.182 ms
Execution time : 5,384.353 ms