explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wxVL

Settings
# exclusive inclusive rows x rows loops node
1. 16,169.902 221,738.537 ↓ 1.0 293,413 1

Hash Left Join (cost=147,903.63..1,194,610,178.62 rows=292,711 width=1,744) (actual time=4,167.501..221,738.537 rows=293,413 loops=1)

  • Hash Cond: ((rh.registry_data ->> 'moOid'::text) = (hist_mo1.oid)::text)
  • Buffers: shared hit=138502180 read=659914
  • Execution Time: 221817.584 ms(414 строк)
2. 265.862 13,073.557 ↓ 1.0 293,413 1

Hash Left Join (cost=144,568.79..3,246,060.49 rows=292,711 width=2,305) (actual time=4,149.140..13,073.557 rows=293,413 loops=1)

  • Hash Cond: (ra.territory_id = pt.id)
  • Buffers: shared hit=2023834 read=114314
3. 237.492 12,807.611 ↓ 1.0 293,413 1

Hash Left Join (cost=144,563.63..3,245,253.04 rows=292,711 width=2,217) (actual time=4,148.993..12,807.611 rows=293,413 loops=1)

  • Hash Cond: (ra.registry_operation_id = ro.id)
  • Buffers: shared hit=2023830 read=114312
4. 2,187.694 12,570.095 ↓ 1.0 293,413 1

Hash Left Join (cost=144,562.56..3,243,398.14 rows=292,711 width=2,145) (actual time=4,148.949..12,570.095 rows=293,413 loops=1)

  • Hash Cond: ((split_part((rh.registry_data ->> 'moOid'::text), '.'::text, 10))::smallint = hist_re.id)
  • Filter: (((rh.id IS NULL) AND (re.id = ANY ('{22,28,29,30,31,32,33,34,35,36,79,75,37,38,7,39,40,41,9,42,43,44,23,24,45,46,47,48,49,50,51,83,52,53,54,55,56,57,58,59,25,60,1,4,2,3,5,6,8,10,11,91,12,13,14,15,16,17,19,61,62,63,64,65,66,67,26,68,69,70,71,72,18,73,27,86,74,20,21,87,89,76,99,77,78,92}'::smallint[]))) OR ((rh.id IS NOT NULL) AND (hist_re.id = ANY ('{22,28,29,30,31,32,33,34,35,36,79,75,37,38,7,39,40,41,9,42,43,44,23,24,45,46,47,48,49,50,51,83,52,53,54,55,56,57,58,59,25,60,1,4,2,3,5,6,8,10,11,91,12,13,14,15,16,17,19,61,62,63,64,65,66,67,26,68,69,70,71,72,18,73,27,86,74,20,21,87,89,76,99,77,78,92}'::smallint[]))))
  • Buffers: shared hit=2023830 read=114311
5. 1,599.913 10,382.361 ↑ 1.1 293,413 1

Hash Right Join (cost=144,559.49..3,242,055.37 rows=313,133 width=2,108) (actual time=4,148.855..10,382.361 rows=293,413 loops=1)

  • Hash Cond: (rh.registry_id = ra.patient_registry_id)
  • Join Filter: ((rh.operation_timestamp = (SubPlan 35)) OR ((rh.operation_timestamp = (SubPlan 37)) AND (NOT (alternatives: SubPlan 38 or hashed SubPlan 39))))
  • Rows Removed by Join Filter: 112265
  • Buffers: shared hit=1996900 read=113381
6. 1,287.904 1,287.904 ↑ 1.0 406,020 1

Seq Scan on nr_vzn_registry_history rh (cost=0.00..105,761.21 rows=406,021 width=1,697) (actual time=0.061..1,287.904 rows=406,020 loops=1)

  • Buffers: shared hit=9047 read=92654
7. 295.568 4,143.285 ↑ 1.1 293,413 1

Hash (cost=140,645.33..140,645.33 rows=313,133 width=427) (actual time=4,143.285..4,143.285 rows=293,413 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 98718kB
  • Buffers: shared hit=64424 read=5210
8. 95.096 3,847.717 ↑ 1.1 293,413 1

Hash Left Join (cost=73,948.18..140,645.33 rows=313,133 width=427) (actual time=1,953.362..3,847.717 rows=293,413 loops=1)

  • Hash Cond: (ra.exclude_reason_id = er.id)
  • Buffers: shared hit=64424 read=5210
9. 100.870 3,752.554 ↑ 1.1 293,413 1

Hash Left Join (cost=73,947.11..139,547.59 rows=313,133 width=407) (actual time=1,953.270..3,752.554 rows=293,413 loops=1)

  • Hash Cond: (ra.exclude_mo_id = mo2.id)
  • Buffers: shared hit=64424 read=5209
10. 158.385 3,637.735 ↑ 1.1 293,413 1

Hash Left Join (cost=70,612.26..135,390.73 rows=313,133 width=359) (actual time=1,939.146..3,637.735 rows=293,413 loops=1)

  • Hash Cond: ((r.mo_oid)::text = (mo1.oid)::text)
  • Buffers: shared hit=61588 read=5209
11. 84.952 3,458.250 ↑ 1.1 293,413 1

Hash Left Join (cost=67,277.42..131,233.75 rows=313,133 width=333) (actual time=1,917.876..3,458.250 rows=293,413 loops=1)

  • Hash Cond: (ra.disability_group_id = dg.id)
  • Buffers: shared hit=58752 read=5209
12. 415.494 3,373.222 ↑ 1.1 293,413 1

Hash Join (cost=67,276.33..130,310.98 rows=313,133 width=317) (actual time=1,917.774..3,373.222 rows=293,413 loops=1)

  • Hash Cond: ((split_part((r.mo_oid)::text, '.'::text, 10))::smallint = re.id)
  • Buffers: shared hit=58752 read=5208
13. 677.839 2,957.629 ↑ 1.1 293,413 1

Hash Join (cost=67,273.26..129,319.15 rows=313,133 width=276) (actual time=1,917.574..2,957.629 rows=293,413 loops=1)

  • Hash Cond: (p.id = r.patient_id)
  • Buffers: shared hit=58748 read=5208
14. 367.718 367.718 ↑ 1.0 1,660,313 1

Seq Scan on nr_patient p (cost=0.00..52,468.50 rows=1,718,950 width=91) (actual time=0.058..367.718 rows=1,660,313 loops=1)

  • Buffers: shared hit=35279
15. 200.413 1,912.072 ↑ 1.1 293,413 1

Hash (cost=63,359.09..63,359.09 rows=313,133 width=193) (actual time=1,912.072..1,912.072 rows=293,413 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 57947kB
  • Buffers: shared hit=23469 read=5208
16. 651.199 1,711.659 ↑ 1.1 293,413 1

Hash Join (cost=12,260.81..63,359.09 rows=313,133 width=193) (actual time=588.285..1,711.659 rows=293,413 loops=1)

  • Hash Cond: (r.id = ra.patient_registry_id)
  • Buffers: shared hit=23469 read=5208
17. 477.453 477.453 ↑ 1.1 1,706,280 1

Seq Scan on nr_patient_registry r (cost=0.00..46,302.07 rows=1,827,124 width=58) (actual time=0.022..477.453 rows=1,706,280 loops=1)

  • Filter: (create_date <= '2020-05-18'::date)
  • Rows Removed by Filter: 563
  • Buffers: shared hit=23462
18. 295.747 583.007 ↑ 1.1 293,515 1

Hash (cost=8,346.47..8,346.47 rows=313,147 width=135) (actual time=583.007..583.007 rows=293,515 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 42049kB
  • Buffers: shared hit=7 read=5208
19. 287.260 287.260 ↑ 1.1 293,515 1

Seq Scan on nr_vzn_registry_addition ra (cost=0.00..8,346.47 rows=313,147 width=135) (actual time=0.122..287.260 rows=293,515 loops=1)

  • Buffers: shared hit=7 read=5208
20. 0.055 0.099 ↑ 1.0 92 1

Hash (cost=1.92..1.92 rows=92 width=41) (actual time=0.099..0.099 rows=92 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
  • Buffers: shared hit=1
21. 0.044 0.044 ↑ 1.0 92 1

Seq Scan on ref_region re (cost=0.00..1.92 rows=92 width=41) (actual time=0.020..0.044 rows=92 loops=1)

  • Buffers: shared hit=1
22. 0.010 0.076 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=20) (actual time=0.076..0.076 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=1
23. 0.066 0.066 ↑ 1.0 4 1

Seq Scan on ref_nsi_vzn_disability_group dg (cost=0.00..1.04 rows=4 width=20) (actual time=0.065..0.066 rows=4 loops=1)

  • Buffers: shared read=1
24. 7.185 21.100 ↓ 1.0 22,187 1

Hash (cost=3,057.71..3,057.71 rows=22,171 width=87) (actual time=21.100..21.100 rows=22,187 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2846kB
  • Buffers: shared hit=2836
25. 13.915 13.915 ↓ 1.0 22,187 1

Seq Scan on nr_pmu_mo mo1 (cost=0.00..3,057.71 rows=22,171 width=87) (actual time=0.008..13.915 rows=22,187 loops=1)

  • Buffers: shared hit=2836
26. 5.578 13.949 ↓ 1.0 22,187 1

Hash (cost=3,057.71..3,057.71 rows=22,171 width=64) (actual time=13.949..13.949 rows=22,187 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2423kB
  • Buffers: shared hit=2836
27. 8.371 8.371 ↓ 1.0 22,187 1

Seq Scan on nr_pmu_mo mo2 (cost=0.00..3,057.71 rows=22,171 width=64) (actual time=0.009..8.371 rows=22,187 loops=1)

  • Buffers: shared hit=2836
28. 0.015 0.067 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=24) (actual time=0.066..0.067 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=1
29. 0.052 0.052 ↑ 1.0 3 1

Seq Scan on ref_nsi_vzn_exclude_reason er (cost=0.00..1.03 rows=3 width=24) (actual time=0.051..0.052 rows=3 loops=1)

  • Buffers: shared read=1
30.          

SubPlan (for Hash Right Join)

31. 405.678 2,434.068 ↑ 1.0 1 405,678

Result (cost=2.44..2.45 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=405,678)

  • Buffers: shared hit=1306956 read=1218
32.          

Initplan (for Result)

33. 0.000 2,028.390 ↑ 1.0 1 405,678

Limit (cost=0.42..2.44 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=405,678)

  • Buffers: shared hit=1306956 read=1218
34. 2,028.390 2,028.390 ↑ 2.0 1 405,678

Index Only Scan Backward using registry_id_and_operation_timestamp on nr_vzn_registry_history (cost=0.42..4.47 rows=2 width=8) (actual time=0.005..0.005 rows=1 loops=405,678)

  • Index Cond: ((registry_id = ra.patient_registry_id) AND (operation_timestamp IS NOT NULL) AND (operation_timestamp < '2020-05-19 00:00:00'::timestamp without time zone))
  • Heap Fetches: 53774
  • Buffers: shared hit=1306956 read=1218
35. 112.265 449.060 ↑ 1.0 1 112,265

Result (cost=2.44..2.45 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=112,265)

  • Buffers: shared hit=382778 read=144
36.          

Initplan (for Result)

37. 0.000 336.795 ↑ 1.0 1 112,265

Limit (cost=0.42..2.44 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=112,265)

  • Buffers: shared hit=382778 read=144
38. 336.795 336.795 ↑ 2.0 1 112,265

Index Only Scan using registry_id_and_operation_timestamp on nr_vzn_registry_history nr_vzn_registry_history_1 (cost=0.42..4.46 rows=2 width=8) (actual time=0.003..0.003 rows=1 loops=112,265)

  • Index Cond: ((registry_id = ra.patient_registry_id) AND (operation_timestamp IS NOT NULL))
  • Heap Fetches: 15123
  • Buffers: shared hit=382778 read=144
39. 0.000 0.000 ↓ 0.0 0

Index Only Scan using registry_id_and_operation_timestamp on nr_vzn_registry_history nr_vzn_registry_history_2 (cost=0.42..4.46 rows=2 width=0) (never executed)

  • Index Cond: ((registry_id = ra.patient_registry_id) AND (operation_timestamp < '2020-05-19 00:00:00'::timestamp without time zone))
  • Heap Fetches: 0
40. 468.131 468.131 ↑ 1.0 405,769 1

Index Only Scan using registry_id_and_operation_timestamp on nr_vzn_registry_history nr_vzn_registry_history_3 (cost=0.42..40,694.86 rows=405,920 width=8) (actual time=0.028..468.131 rows=405,769 loops=1)

  • Index Cond: (operation_timestamp < '2020-05-19 00:00:00'::timestamp without time zone)
  • Heap Fetches: 53309
  • Buffers: shared hit=233695 read=14155
41. 0.022 0.040 ↑ 1.0 92 1

Hash (cost=1.92..1.92 rows=92 width=41) (actual time=0.040..0.040 rows=92 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
  • Buffers: shared hit=1
42. 0.018 0.018 ↑ 1.0 92 1

Seq Scan on ref_region hist_re (cost=0.00..1.92 rows=92 width=41) (actual time=0.009..0.018 rows=92 loops=1)

  • Buffers: shared hit=1
43. 0.005 0.024 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=76) (actual time=0.024..0.024 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=1
44. 0.019 0.019 ↑ 1.0 3 1

Seq Scan on ref_nsi_registry_operation ro (cost=0.00..1.03 rows=3 width=76) (actual time=0.018..0.019 rows=3 loops=1)

  • Buffers: shared read=1
45. 0.028 0.084 ↑ 1.0 96 1

Hash (cost=3.96..3.96 rows=96 width=96) (actual time=0.084..0.084 rows=96 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
  • Buffers: shared hit=1 read=2
46. 0.056 0.056 ↑ 1.0 96 1

Seq Scan on ref_nsi_pmu_territory pt (cost=0.00..3.96 rows=96 width=96) (actual time=0.011..0.056 rows=96 loops=1)

  • Buffers: shared hit=1 read=2
47. 6.960 16.150 ↓ 1.0 22,187 1

Hash (cost=3,057.71..3,057.71 rows=22,171 width=87) (actual time=16.150..16.150 rows=22,187 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2846kB
  • Buffers: shared hit=2836
48. 9.190 9.190 ↓ 1.0 22,187 1

Seq Scan on nr_pmu_mo hist_mo1 (cost=0.00..3,057.71 rows=22,171 width=87) (actual time=0.012..9.190 rows=22,187 loops=1)

  • Buffers: shared hit=2836
49.          

SubPlan (for Hash Left Join)

50. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=4.45..4.46 rows=1 width=32) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Index Only Scan using nr_vzn_addition_desease_unq on nr_vzn_addition_desease ad (cost=0.42..4.44 rows=1 width=5) (never executed)

  • Index Cond: (patient_registry_id = r.id)
  • Heap Fetches: 0
52. 880.239 3,227.543 ↑ 1.0 1 293,413

Aggregate (cost=1.51..1.52 rows=1 width=32) (actual time=0.010..0.011 rows=1 loops=293,413)

  • Buffers: shared hit=13920
53. 2,347.304 2,347.304 ↑ 100.0 1 293,413

Function Scan on jsonb_array_elements x (cost=0.01..1.01 rows=100 width=32) (actual time=0.008..0.008 rows=1 loops=293,413)

  • Buffers: shared hit=13920
54. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=6.33..6.34 rows=1 width=32) (never executed)

55. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.59..6.33 rows=1 width=58) (never executed)

56. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=4.45..6.00 rows=1 width=2) (never executed)

  • Hash Cond: ((gd.desease)::text = (ad_1.desease)::text)
57. 0.000 0.000 ↓ 0.0 0

Seq Scan on ref_nsi_vzn_nos_group_desease gd (cost=0.00..1.39 rows=39 width=7) (never executed)

58. 0.000 0.000 ↓ 0.0 0

Hash (cost=4.44..4.44 rows=1 width=5) (never executed)

59. 0.000 0.000 ↓ 0.0 0

Index Only Scan using nr_vzn_addition_desease_unq on nr_vzn_addition_desease ad_1 (cost=0.42..4.44 rows=1 width=5) (never executed)

  • Index Cond: (patient_registry_id = r.id)
  • Heap Fetches: 0
60. 0.000 0.000 ↓ 0.0 0

Index Scan using ref_vzn_nos_group_pkey on ref_nsi_vzn_nos_group g (cost=0.14..0.31 rows=1 width=60) (never executed)

  • Index Cond: (id = gd.nos_group_id)
61. 2,347.304 33,742.495 ↑ 1.0 1 293,413

Aggregate (cost=3.10..3.11 rows=1 width=32) (actual time=0.115..0.115 rows=1 loops=293,413)

  • Buffers: shared hit=1129709
62. 1,467.065 31,395.191 ↑ 1.0 1 293,413

Hash Join (cost=1.89..3.09 rows=1 width=58) (actual time=0.105..0.107 rows=1 loops=293,413)

  • Hash Cond: (g_1.id = gd_1.nos_group_id)
  • Buffers: shared hit=1129706
63. 586.826 586.826 ↑ 1.0 14 293,413

Seq Scan on ref_nsi_vzn_nos_group g_1 (cost=0.00..1.14 rows=14 width=60) (actual time=0.001..0.002 rows=14 loops=293,413)

  • Buffers: shared hit=293413
64. 880.239 29,341.300 ↑ 1.0 1 293,413

Hash (cost=1.88..1.88 rows=1 width=2) (actual time=0.100..0.100 rows=1 loops=293,413)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=836293
65. 28,461.061 28,461.061 ↑ 1.0 1 293,413

Seq Scan on ref_nsi_vzn_nos_group_desease gd_1 (cost=0.00..1.88 rows=1 width=2) (actual time=0.062..0.097 rows=1 loops=293,413)

  • Filter: (rh.registry_data @> ((('{"nrVznRegistryAddition": {"deseaseSet": [{"desease": "'::text || (desease)::text) || '"}]}}'::text))::jsonb)
  • Rows Removed by Filter: 38
  • Buffers: shared hit=836293
66. 0.000 0.000 ↓ 0.0 0

Limit (cost=5.50..5.50 rows=1 width=48) (never executed)

67. 0.000 0.000 ↓ 0.0 0

Sort (cost=5.50..5.50 rows=1 width=48) (never executed)

  • Sort Key: pd.pass_date DESC
68. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.43..5.49 rows=1 width=48) (never executed)

  • Join Filter: (doc.id = pd.document_id)
69. 0.000 0.000 ↓ 0.0 0

Index Scan using nr_patient_document_idx_patient_id on nr_patient_document pd (cost=0.43..4.45 rows=1 width=6) (never executed)

  • Index Cond: (patient_id = p.id)
70. 0.000 0.000 ↓ 0.0 0

Seq Scan on ref_nsi_vzn_document doc (cost=0.00..1.02 rows=2 width=46) (never executed)

71. 293.413 3,814.369 ↑ 1.0 1 293,413

Limit (cost=2.26..2.26 rows=1 width=96) (actual time=0.013..0.013 rows=1 loops=293,413)

72. 586.826 3,520.956 ↑ 100.0 1 293,413

Sort (cost=2.26..2.51 rows=100 width=96) (actual time=0.012..0.012 rows=1 loops=293,413)

  • Sort Key: ((x_1.v ->> 'sortOrder'::text)), ((x_1.v ->> 'passDate'::text)) DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
73. 2,934.130 2,934.130 ↑ 100.0 1 293,413

Function Scan on jsonb_array_elements x_1 (cost=0.01..1.76 rows=100 width=96) (actual time=0.010..0.010 rows=1 loops=293,413)

74. 0.000 0.000 ↓ 0.0 0

Limit (cost=4.46..4.46 rows=1 width=36) (never executed)

75. 0.000 0.000 ↓ 0.0 0

Sort (cost=4.46..4.46 rows=1 width=36) (never executed)

  • Sort Key: pd_1.pass_date DESC
76. 0.000 0.000 ↓ 0.0 0

Index Scan using nr_patient_document_idx_patient_id on nr_patient_document pd_1 (cost=0.43..4.45 rows=1 width=36) (never executed)

  • Index Cond: (patient_id = p.id)
77. 293.413 2,640.717 ↑ 1.0 1 293,413

Limit (cost=2.76..2.76 rows=1 width=96) (actual time=0.009..0.009 rows=1 loops=293,413)

78. 293.413 2,347.304 ↑ 100.0 1 293,413

Sort (cost=2.76..3.01 rows=100 width=96) (actual time=0.008..0.008 rows=1 loops=293,413)

  • Sort Key: ((x_2.v ->> 'sortOrder'::text)), ((x_2.v ->> 'passDate'::text)) DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
79. 2,053.891 2,053.891 ↑ 100.0 1 293,413

Function Scan on jsonb_array_elements x_2 (cost=0.01..2.26 rows=100 width=96) (actual time=0.007..0.007 rows=1 loops=293,413)

80. 0.000 0.000 ↓ 0.0 0

Limit (cost=4.46..4.46 rows=1 width=4) (never executed)

81. 0.000 0.000 ↓ 0.0 0

Sort (cost=4.46..4.46 rows=1 width=4) (never executed)

  • Sort Key: pd_2.pass_date DESC
82. 0.000 0.000 ↓ 0.0 0

Index Scan using nr_patient_document_idx_patient_id on nr_patient_document pd_2 (cost=0.43..4.45 rows=1 width=4) (never executed)

  • Index Cond: (patient_id = p.id)
83. 293.413 2,640.717 ↑ 1.0 1 293,413

Limit (cost=2.76..2.76 rows=1 width=68) (actual time=0.009..0.009 rows=1 loops=293,413)

84. 293.413 2,347.304 ↑ 100.0 1 293,413

Sort (cost=2.76..3.01 rows=100 width=68) (actual time=0.008..0.008 rows=1 loops=293,413)

  • Sort Key: ((x_3.v ->> 'sortOrder'::text)), ((x_3.v ->> 'passDate'::text)) DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
85. 2,053.891 2,053.891 ↑ 100.0 1 293,413

Function Scan on jsonb_array_elements x_3 (cost=0.01..2.26 rows=100 width=68) (actual time=0.007..0.007 rows=1 loops=293,413)

86. 0.000 0.000 ↓ 0.0 0

Limit (cost=4.46..4.46 rows=1 width=76) (never executed)

87. 0.000 0.000 ↓ 0.0 0

Sort (cost=4.46..4.46 rows=1 width=76) (never executed)

  • Sort Key: pd_3.pass_date DESC
88. 0.000 0.000 ↓ 0.0 0

Index Scan using nr_patient_document_idx_patient_id on nr_patient_document pd_3 (cost=0.43..4.45 rows=1 width=76) (never executed)

  • Index Cond: (patient_id = p.id)
89. 293.413 2,347.304 ↑ 1.0 1 293,413

Limit (cost=2.26..2.26 rows=1 width=96) (actual time=0.008..0.008 rows=1 loops=293,413)

90. 293.413 2,053.891 ↑ 100.0 1 293,413

Sort (cost=2.26..2.51 rows=100 width=96) (actual time=0.007..0.007 rows=1 loops=293,413)

  • Sort Key: ((x_4.v ->> 'sortOrder'::text)), ((x_4.v ->> 'passDate'::text)) DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
91. 1,760.478 1,760.478 ↑ 100.0 1 293,413

Function Scan on jsonb_array_elements x_4 (cost=0.01..1.76 rows=100 width=96) (actual time=0.006..0.006 rows=1 loops=293,413)

92. 0.000 0.000 ↓ 0.0 0

Limit (cost=4.46..4.47 rows=1 width=36) (never executed)

93. 0.000 0.000 ↓ 0.0 0

Sort (cost=4.46..4.47 rows=1 width=36) (never executed)

  • Sort Key: pd_4.pass_date DESC
94. 0.000 0.000 ↓ 0.0 0

Index Scan using nr_patient_document_idx_patient_id on nr_patient_document pd_4 (cost=0.43..4.46 rows=1 width=36) (never executed)

  • Index Cond: (patient_id = p.id)
95. 0.000 2,934.130 ↑ 1.0 1 293,413

Limit (cost=4.01..4.01 rows=1 width=96) (actual time=0.010..0.010 rows=1 loops=293,413)

96. 586.826 2,934.130 ↑ 100.0 1 293,413

Sort (cost=4.01..4.26 rows=100 width=96) (actual time=0.010..0.010 rows=1 loops=293,413)

  • Sort Key: ((x_5.v ->> 'sortOrder'::text)), ((x_5.v ->> 'passDate'::text)) DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
97. 2,347.304 2,347.304 ↑ 100.0 1 293,413

Function Scan on jsonb_array_elements x_5 (cost=0.01..3.51 rows=100 width=96) (actual time=0.008..0.008 rows=1 loops=293,413)

98. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.29..4.56 rows=1 width=32) (never executed)

99. 0.000 0.000 ↓ 0.0 0

Index Scan using nr_vzn_patient_address_idx_patient_registy_id on nr_vzn_patient_address pa (cost=0.29..4.56 rows=1 width=32) (never executed)

  • Index Cond: (patient_registry_id = r.id)
  • Filter: (address_type_id = 1)
100. 293.413 2,053.891 ↓ 0.0 0 293,413

Limit (cost=0.01..1.51 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=293,413)

  • Buffers: shared hit=13920
101. 1,760.478 1,760.478 ↓ 0.0 0 293,413

Function Scan on jsonb_array_elements x_6 (cost=0.01..1.51 rows=1 width=32) (actual time=0.006..0.006 rows=0 loops=293,413)

  • Filter: ((v #>> '{addressTypeId,id}'::text[]) = '1'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=13920
102. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.29..4.56 rows=1 width=32) (never executed)

103. 0.000 0.000 ↓ 0.0 0

Index Scan using nr_vzn_patient_address_idx_patient_registy_id on nr_vzn_patient_address pa_1 (cost=0.29..4.56 rows=1 width=32) (never executed)

  • Index Cond: (patient_registry_id = r.id)
  • Filter: (address_type_id = 2)
104. 0.000 1,467.065 ↓ 0.0 0 293,413

Limit (cost=0.01..1.51 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=293,413)

  • Buffers: shared hit=13920
105. 1,467.065 1,467.065 ↓ 0.0 0 293,413

Function Scan on jsonb_array_elements x_7 (cost=0.01..1.51 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=293,413)

  • Filter: ((v #>> '{addressTypeId,id}'::text[]) = '2'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=13920
106. 0.000 4,694.608 ↓ 0.0 0 293,413

Limit (cost=8.74..8.74 rows=1 width=47) (actual time=0.016..0.016 rows=0 loops=293,413)

  • Buffers: shared hit=594509 read=42
107. 1,467.065 4,694.608 ↓ 0.0 0 293,413

Sort (cost=8.74..8.74 rows=1 width=47) (actual time=0.016..0.016 rows=0 loops=293,413)

  • Sort Key: dh.direct_date DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=594509 read=42
108. 858.036 3,227.543 ↓ 0.0 0 293,413

Hash Join (cost=6.11..8.73 rows=1 width=47) (actual time=0.011..0.011 rows=0 loops=293,413)

  • Hash Cond: (re1.id = (split_part((dh.from_mo_oid)::text, '.'::text, 10))::smallint)
  • Buffers: shared hit=594506 read=42
109. 22.203 22.203 ↑ 1.0 92 2,467

Seq Scan on ref_region re1 (cost=0.00..1.92 rows=92 width=41) (actual time=0.004..0.009 rows=92 loops=2,467)

  • Buffers: shared hit=2467
110. 880.239 2,347.304 ↓ 0.0 0 293,413

Hash (cost=6.10..6.10 rows=1 width=38) (actual time=0.008..0.008 rows=0 loops=293,413)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=592039 read=42
111. 293.413 1,467.065 ↓ 0.0 0 293,413

Bitmap Heap Scan on nr_vzn_registry_direct_history dh (cost=2.30..6.10 rows=1 width=38) (actual time=0.005..0.005 rows=0 loops=293,413)

  • Recheck Cond: (patient_registry_id = r.id)
  • Filter: ((action_type = ANY ('{2,6}'::integer[])) AND (direct_date <= '2020-05-18'::date))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=4897
  • Buffers: shared hit=592039 read=42
112. 1,173.652 1,173.652 ↓ 0.0 0 293,413

Bitmap Index Scan on nr_vzn_registry_direct_history_idx_patient_registry_id (cost=0.00..2.30 rows=2 width=0) (actual time=0.004..0.004 rows=0 loops=293,413)

  • Index Cond: (patient_registry_id = r.id)
  • Buffers: shared hit=587184
113. 0.000 2,934.130 ↓ 0.0 0 293,413

Limit (cost=8.74..8.74 rows=1 width=47) (actual time=0.010..0.010 rows=0 loops=293,413)

  • Buffers: shared hit=594548
114. 1,173.652 2,934.130 ↓ 0.0 0 293,413

Sort (cost=8.74..8.74 rows=1 width=47) (actual time=0.010..0.010 rows=0 loops=293,413)

  • Sort Key: dh_1.direct_date DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=594548
115. 569.557 1,760.478 ↓ 0.0 0 293,413

Hash Join (cost=6.11..8.73 rows=1 width=47) (actual time=0.006..0.006 rows=0 loops=293,413)

  • Hash Cond: (re1_1.id = (split_part((dh_1.to_mo_oid)::text, '.'::text, 10))::smallint)
  • Buffers: shared hit=594548
116. 17.269 17.269 ↑ 1.0 92 2,467

Seq Scan on ref_region re1_1 (cost=0.00..1.92 rows=92 width=41) (actual time=0.002..0.007 rows=92 loops=2,467)

  • Buffers: shared hit=2467
117. 586.826 1,173.652 ↓ 0.0 0 293,413

Hash (cost=6.10..6.10 rows=1 width=38) (actual time=0.004..0.004 rows=0 loops=293,413)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=592081
118. 0.000 586.826 ↓ 0.0 0 293,413

Bitmap Heap Scan on nr_vzn_registry_direct_history dh_1 (cost=2.30..6.10 rows=1 width=38) (actual time=0.002..0.002 rows=0 loops=293,413)

  • Recheck Cond: (patient_registry_id = r.id)
  • Filter: ((action_type = ANY ('{2,6}'::integer[])) AND (direct_date <= '2020-05-18'::date))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=4897
  • Buffers: shared hit=592081
119. 586.826 586.826 ↓ 0.0 0 293,413

Bitmap Index Scan on nr_vzn_registry_direct_history_idx_patient_registry_id (cost=0.00..2.30 rows=2 width=0) (actual time=0.002..0.002 rows=0 loops=293,413)

  • Index Cond: (patient_registry_id = r.id)
  • Buffers: shared hit=587184
120. 293.413 3,814.369 ↑ 1.0 1 293,413

Result (cost=17.39..17.40 rows=1 width=32) (actual time=0.013..0.013 rows=1 loops=293,413)

  • Buffers: shared hit=2067353 read=3701
121.          

Initplan (for Result)

122. 293.413 3,520.956 ↓ 0.0 0 293,413

Limit (cost=0.83..17.39 rows=1 width=32) (actual time=0.012..0.012 rows=0 loops=293,413)

  • Buffers: shared hit=2067353 read=3701
123. 0.000 3,227.543 ↓ 0.0 0 293,413

Nested Loop (cost=0.83..17.39 rows=1 width=32) (actual time=0.011..0.011 rows=0 loops=293,413)

  • Buffers: shared hit=2067353 read=3701
124. 419.460 2,934.130 ↑ 2.0 1 293,413

Nested Loop (cost=0.70..17.01 rows=2 width=4) (actual time=0.009..0.010 rows=1 loops=293,413)

  • Buffers: shared hit=1760167 read=3701
125. 2,053.891 2,053.891 ↑ 2.0 1 293,413

Index Scan using nr_vzn_need_pers_idx_patient_registry_id on nr_vzn_need_pers pers (cost=0.42..8.41 rows=2 width=8) (actual time=0.007..0.007 rows=1 loops=293,413)

  • Index Cond: (patient_registry_id = r.id)
  • Filter: (status_id = 5)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1299388 read=3701
126. 460.779 460.779 ↑ 1.0 1 153,593

Index Scan using nr_vzn_need_pkey on nr_vzn_need need (cost=0.27..4.29 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=153,593)

  • Index Cond: (id = pers.vzn_need_id)
  • Buffers: shared hit=460779
127. 307.186 307.186 ↑ 1.0 1 153,593

Index Scan using ref_nsi_vzn_app_event_pkey on ref_nsi_vzn_app_event ev (cost=0.13..0.17 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=153,593)

  • Index Cond: (id = need.app_event_id)
  • Filter: (event_period = '2020'::smallint)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=307186
128. 293.413 73,059.837 ↑ 1.0 1 293,413

Aggregate (cost=1,983.74..1,983.75 rows=1 width=8) (actual time=0.249..0.249 rows=1 loops=293,413)

  • Buffers: shared hit=65617612 read=541857
129. 27,360.605 72,766.424 ↓ 0.0 0 293,413

Nested Loop Left Join (cost=0.85..1,983.74 rows=1 width=8) (actual time=0.246..0.248 rows=0 loops=293,413)

  • Filter: (((vrh.id IS NULL) AND (vr.delivery_date IS NULL)) OR ((vrh.id IS NOT NULL) AND ((vrh.recipe_data ->> 'deliveryDate'::text) IS NULL)))
  • Rows Removed by Filter: 6
  • Buffers: shared hit=65617612 read=541857
130. 4,401.195 4,401.195 ↑ 3.8 6 293,413

Index Scan using nr_vzn_recipe_idx_patient_registry_id on nr_vzn_recipe vr (cost=0.43..48.07 rows=23 width=12) (actual time=0.005..0.015 rows=6 loops=293,413)

  • Index Cond: (patient_registry_id = r.id)
  • Filter: (create_date <= '2020-05-18'::date)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2434012 read=16230
131. 11,694.088 41,004.624 ↑ 1.0 1 1,708,526

Index Scan using nr_vzn_recipe_history_idx_recipe_id on nr_vzn_recipe_history vrh (cost=0.43..84.15 rows=1 width=172) (actual time=0.010..0.024 rows=1 loops=1,708,526)

  • Index Cond: (vr.id = recipe_id)
  • Filter: ((operation_timestamp = (SubPlan 23)) OR ((operation_timestamp = (SubPlan 24)) AND (NOT (alternatives: SubPlan 25 or hashed SubPlan 26))))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=57238972 read=132573
132.          

SubPlan (for Index Scan)

133. 3,986.328 17,938.476 ↑ 1.0 1 1,993,164

Aggregate (cost=8.28..8.29 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1,993,164)

  • Buffers: shared hit=28397208 read=25486
134. 13,952.148 13,952.148 ↓ 2.8 11 1,993,164

Index Scan using nr_vzn_recipe_history_idx_recipe_id on nr_vzn_recipe_history (cost=0.43..8.27 rows=4 width=8) (actual time=0.002..0.007 rows=11 loops=1,993,164)

  • Index Cond: (recipe_id = vr.id)
  • Filter: (operation_timestamp < '2020-05-19 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=28397208 read=25486
135. 1,709.136 11,109.384 ↑ 1.0 1 284,856

Aggregate (cost=8.27..8.28 rows=1 width=8) (actual time=0.039..0.039 rows=1 loops=284,856)

  • Buffers: shared hit=21284700
136. 9,400.248 9,400.248 ↓ 18.2 73 284,856

Index Scan using nr_vzn_recipe_history_idx_recipe_id on nr_vzn_recipe_history nr_vzn_recipe_history_1 (cost=0.43..8.26 rows=4 width=8) (actual time=0.003..0.033 rows=73 loops=284,856)

  • Index Cond: (recipe_id = vr.id)
  • Buffers: shared hit=21284700
137. 262.676 262.676 ↑ 4.0 1 131,338

Index Scan using nr_vzn_recipe_history_idx_recipe_id on nr_vzn_recipe_history nr_vzn_recipe_history_2 (cost=0.43..8.27 rows=4 width=0) (actual time=0.002..0.002 rows=1 loops=131,338)

  • Index Cond: (recipe_id = vr.id)
  • Filter: (operation_timestamp < '2020-05-19 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=525559
138. 0.000 0.000 ↓ 0.0 0

Seq Scan on nr_vzn_recipe_history nr_vzn_recipe_history_3 (cost=0.00..330,769.19 rows=1,970,859 width=8) (never executed)

  • Filter: (operation_timestamp < '2020-05-19 00:00:00'::timestamp without time zone)
139. 293.413 53,107.753 ↑ 1.0 1 293,413

Aggregate (cost=1,983.79..1,983.80 rows=1 width=8) (actual time=0.181..0.181 rows=1 loops=293,413)

  • Buffers: shared hit=66159450
140. 18,298.455 52,814.340 ↑ 3.8 6 293,413

Nested Loop Left Join (cost=0.85..1,983.74 rows=23 width=8) (actual time=0.013..0.180 rows=6 loops=293,413)

  • Filter: (((vrh_1.id IS NULL) AND (vr_1.delivery_date IS NOT NULL)) OR ((vrh_1.id IS NOT NULL) AND ((vrh_1.recipe_data ->> 'deliveryDate'::text) IS NOT NULL)))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=66159450
141. 2,053.891 2,053.891 ↑ 3.8 6 293,413

Index Scan using nr_vzn_recipe_idx_patient_registry_id on nr_vzn_recipe vr_1 (cost=0.43..48.07 rows=23 width=12) (actual time=0.002..0.007 rows=6 loops=293,413)

  • Index Cond: (patient_registry_id = r.id)
  • Filter: (create_date <= '2020-05-18'::date)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2450242
142. 4,859.766 32,461.994 ↑ 1.0 1 1,708,526

Index Scan using nr_vzn_recipe_history_idx_recipe_id on nr_vzn_recipe_history vrh_1 (cost=0.43..84.15 rows=1 width=172) (actual time=0.006..0.019 rows=1 loops=1,708,526)

  • Index Cond: (vr_1.id = recipe_id)
  • Filter: ((operation_timestamp = (SubPlan 28)) OR ((operation_timestamp = (SubPlan 29)) AND (NOT (alternatives: SubPlan 30 or hashed SubPlan 31))))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=57371545
143.          

SubPlan (for Index Scan)

144. 1,993.164 15,945.312 ↑ 1.0 1 1,993,164

Aggregate (cost=8.28..8.29 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=1,993,164)

  • Buffers: shared hit=28422694
145. 13,952.148 13,952.148 ↓ 2.8 11 1,993,164

Index Scan using nr_vzn_recipe_history_idx_recipe_id on nr_vzn_recipe_history nr_vzn_recipe_history_4 (cost=0.43..8.27 rows=4 width=8) (actual time=0.002..0.007 rows=11 loops=1,993,164)

  • Index Cond: (recipe_id = vr_1.id)
  • Filter: (operation_timestamp < '2020-05-19 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=28422694
146. 1,993.992 11,394.240 ↑ 1.0 1 284,856

Aggregate (cost=8.27..8.28 rows=1 width=8) (actual time=0.040..0.040 rows=1 loops=284,856)

  • Buffers: shared hit=21284700
147. 9,400.248 9,400.248 ↓ 18.2 73 284,856

Index Scan using nr_vzn_recipe_history_idx_recipe_id on nr_vzn_recipe_history nr_vzn_recipe_history_5 (cost=0.43..8.26 rows=4 width=8) (actual time=0.002..0.033 rows=73 loops=284,856)

  • Index Cond: (recipe_id = vr_1.id)
  • Buffers: shared hit=21284700
148. 262.676 262.676 ↑ 4.0 1 131,338

Index Scan using nr_vzn_recipe_history_idx_recipe_id on nr_vzn_recipe_history nr_vzn_recipe_history_6 (cost=0.43..8.27 rows=4 width=0) (actual time=0.002..0.002 rows=1 loops=131,338)

  • Index Cond: (recipe_id = vr_1.id)
  • Filter: (operation_timestamp < '2020-05-19 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=525559
149. 0.000 0.000 ↓ 0.0 0

Seq Scan on nr_vzn_recipe_history nr_vzn_recipe_history_7 (cost=0.00..330,769.19 rows=1,970,859 width=8) (never executed)

  • Filter: (operation_timestamp < '2020-05-19 00:00:00'::timestamp without time zone)
150. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.42..2.44 rows=1 width=40) (never executed)

151. 0.000 0.000 ↓ 0.0 0

Index Only Scan Backward using registry_id_and_operation_timestamp on nr_vzn_registry_history rh_1 (cost=0.42..4.46 rows=2 width=40) (never executed)

  • Index Cond: (registry_id = r.id)
  • Heap Fetches: 0
Planning time : 50.247 ms