explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aljf

Settings
# exclusive inclusive rows x rows loops node
1. 0.325 4,900.083 ↑ 1,795.9 315 1

WindowAgg (cost=269,285,133.90..271,013,155.55 rows=565,693 width=716) (actual time=294.070..4,900.083 rows=315 loops=1)

2.          

CTE lpu

3. 4,663.648 4,871.608 ↑ 71,234,860.0 1 1

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

  • Join Filter: (t_5.id = (COALESCE(d_pkg_agent_addrs.get_actual_on_date(t_4.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_4.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. 76.029 207.960 ↓ 1.0 146,879 1

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

  • Hash Cond: (t_5.cid = up_5.catalog)
5. 29.886 29.886 ↓ 1.0 146,879 1

Seq Scan on d_agent_addrs t_5 (cost=0.00..4,723.76 rows=146,876 width=21) (actual time=0.007..29.886 rows=146,879 loops=1)

6. 0.037 102.045 ↑ 36.7 109 1

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

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

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

  • Group Key: up_5.catalog
8. 87.041 95.351 ↓ 1.3 25,227 1

Bitmap Heap Scan on d_urprivs up_5 (cost=1,466.95..78,236.69 rows=19,246 width=7) (actual time=12.009..95.351 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. 7.785 7.785 ↓ 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=7.785..7.785 rows=58,350 loops=1)

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

SubPlan (for Bitmap Heap Scan)

11. 0.076 0.525 ↓ 77.7 855 1

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

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

  • Index Cond: ((username)::text = ((SESSION_USER)::character varying(30))::text)
13. 0.425 0.425 ↓ 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.032..0.425 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.011 6.530 ↑ 970.0 1 1

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

  • Hash Cond: (t_4.version = up_4.version)
16. 1.381 3.748 ↑ 970.0 1 1

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

  • Join Filter: (t_4.id = (t_6.column_value)::text)
  • Rows Removed by Join Filter: 1,939
17. 0.427 0.427 ↑ 1.0 1,940 1

Seq Scan on d_lpudict t_4 (cost=0.00..96.40 rows=1,940 width=125) (actual time=0.010..0.427 rows=1,940 loops=1)

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

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

19. 0.007 2.771 ↑ 32.8 17 1

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

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

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

  • Group Key: up_4.version
21. 2.102 2.555 ↑ 1.0 843 1

Bitmap Heap Scan on d_urprivs up_4 (cost=159.57..6,248.27 rows=845 width=7) (actual time=0.749..2.555 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.156 0.156 ↑ 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.156..0.156 rows=1,294 loops=1)

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

SubPlan (for Bitmap Heap Scan)

24. 0.076 0.297 ↓ 77.7 855 1

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

25. 0.008 0.008 ↑ 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.008..0.008 rows=1 loops=1)

  • Index Cond: ((username)::text = ((SESSION_USER)::character varying(30))::text)
26. 0.213 0.213 ↓ 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.026..0.213 rows=855 loops=1)

  • Index Cond: (sysuser = us.id)
27. 0.114 4,899.758 ↑ 1,795.9 315 1

Hash Join (cost=19,620.39..1,734,913.94 rows=565,693 width=726) (actual time=294.049..4,899.758 rows=315 loops=1)

  • Hash Cond: (t_2.pregnancy = t_3.pid)
28. 0.048 4,886.875 ↑ 1,957.0 182 1

Hash Join (cost=810.38..1,696,200.04 rows=356,174 width=653) (actual time=281.263..4,886.875 rows=182 loops=1)

  • Hash Cond: (t4.id = t_2.lpu_in)
29. 4,871.615 4,871.615 ↑ 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=266.045..4,871.615 rows=1 loops=1)

30. 0.107 15.212 ↓ 197.0 197 1

Hash (cost=810.37..810.37 rows=1 width=112) (actual time=15.212..15.212 rows=197 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
31. 0.040 15.105 ↓ 197.0 197 1

Nested Loop Semi Join (cost=296.92..810.37 rows=1 width=112) (actual time=1.689..15.105 rows=197 loops=1)

32. 0.132 12.504 ↓ 197.0 197 1

Nested Loop Semi Join (cost=198.18..617.15 rows=1 width=119) (actual time=1.171..12.504 rows=197 loops=1)

33. 0.049 9.023 ↓ 197.0 197 1

Nested Loop Semi Join (cost=99.44..518.36 rows=1 width=126) (actual time=0.672..9.023 rows=197 loops=1)

34. 0.104 5.625 ↓ 197.0 197 1

Nested Loop (cost=0.70..418.66 rows=1 width=133) (actual time=0.121..5.625 rows=197 loops=1)

  • Join Filter: (t.pid = t_1.id)
35. 0.032 3.945 ↓ 98.5 197 1

Nested Loop (cost=0.28..402.52 rows=2 width=90) (actual time=0.095..3.945 rows=197 loops=1)

36. 0.058 0.058 ↑ 1.2 257 1

Seq Scan on d_pregnant_cards t_2 (cost=0.00..9.04 rows=304 width=76) (actual time=0.011..0.058 rows=257 loops=1)

37. 3.855 3.855 ↑ 1.0 1 257

Index Scan using i_d_agent_pregnancy_pid on d_agent_pregnancy t (cost=0.28..1.28 rows=1 width=14) (actual time=0.013..0.015 rows=1 loops=257)

  • Index Cond: (pid = t_2.agent)
  • 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: 1
38. 1.576 1.576 ↑ 1.0 1 197

Index Scan using pk_d_agents on d_agents t_1 (cost=0.42..8.06 rows=1 width=64) (actual time=0.008..0.008 rows=1 loops=197)

  • Index Cond: (id = t_2.agent)
39. 3.045 3.349 ↑ 4.0 1 197

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

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

SubPlan (for Index Only Scan)

41. 0.070 0.304 ↓ 77.7 855 1

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

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

  • Index Cond: ((username)::text = ((SESSION_USER)::character varying(30))::text)
43. 0.225 0.225 ↓ 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.024..0.225 rows=855 loops=1)

  • Index Cond: (sysuser = us_2.id)
44. 3.120 3.349 ↑ 4.0 1 197

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

  • Index Cond: ((unitcode = 'AGENTS'::text) AND (catalog = t_1.cid))
  • Filter: (((username)::text = ((SESSION_USER)::character varying)::text) OR (hashed SubPlan 5))
  • Heap Fetches: 197
45.          

SubPlan (for Index Only Scan)

46. 0.070 0.229 ↓ 77.7 855 1

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

47. 0.005 0.005 ↑ 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.005..0.005 rows=1 loops=1)

  • Index Cond: ((username)::text = ((SESSION_USER)::character varying(30))::text)
48. 0.154 0.154 ↓ 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.019..0.154 rows=855 loops=1)

  • Index Cond: (sysuser = us_3.id)
49. 2.329 2.561 ↑ 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.013..0.013 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 6))
  • Heap Fetches: 197
50.          

SubPlan (for Index Only Scan)

51. 0.069 0.232 ↓ 77.7 855 1

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

52. 0.006 0.006 ↑ 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.006..0.006 rows=1 loops=1)

  • Index Cond: ((username)::text = ((SESSION_USER)::character varying(30))::text)
53. 0.157 0.157 ↓ 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.020..0.157 rows=855 loops=1)

  • Index Cond: (sysuser = us_4.id)
54. 0.123 12.769 ↑ 1.0 443 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 64kB
55. 0.821 12.646 ↑ 1.0 443 1

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

  • Merge Cond: (t_3.cid = up_3.catalog)
56. 0.242 0.242 ↑ 1.0 443 1

Index Scan using i_d_agent_prgn_mkbs_cid on d_agent_prgn_mkbs t_3 (cost=0.28..65.74 rows=443 width=94) (actual time=0.020..0.242 rows=443 loops=1)

  • Filter: (diagn_type = '0'::numeric)
  • Rows Removed by Filter: 124
57. 11.298 11.583 ↑ 2.1 6,384 1

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

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

SubPlan (for Index Only Scan)

59. 0.071 0.285 ↓ 77.7 855 1

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

60. 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)
61. 0.203 0.203 ↓ 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.025..0.203 rows=855 loops=1)

  • Index Cond: (sysuser = us_5.id)
Planning time : 10.831 ms
Execution time : 4,901.443 ms