explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3QNC

Settings
# exclusive inclusive rows x rows loops node
1. 16,594.867 180,515.621 ↓ 1.0 293,413 1

Hash Left Join (cost=147,903.63..408,226,003.86 rows=292,711 width=1,744) (actual time=12,011.937..180,515.621 rows=293,413 loops=1)

  • Hash Cond: ((rh.registry_data ->> 'moOid'::text) = (hist_mo1.oid)::text)
  • Buffers: shared hit=55091731 read=185317
  • Execution Time: 180593.065 ms(426 строк)
2. 232.282 20,130.360 ↓ 1.0 293,413 1

Hash Left Join (cost=144,568.79..3,246,060.49 rows=292,711 width=2,305) (actual time=11,990.255..20,130.360 rows=293,413 loops=1)

  • Hash Cond: (ra.territory_id = pt.id)
  • Buffers: shared hit=1984530 read=153618
3. 264.812 19,897.985 ↓ 1.0 293,413 1

Hash Left Join (cost=144,563.63..3,245,253.04 rows=292,711 width=2,217) (actual time=11,990.111..19,897.985 rows=293,413 loops=1)

  • Hash Cond: (ra.registry_operation_id = ro.id)
  • Buffers: shared hit=1984526 read=153616
4. 2,140.718 19,633.154 ↓ 1.0 293,413 1

Hash Left Join (cost=144,562.56..3,243,398.14 rows=292,711 width=2,145) (actual time=11,990.073..19,633.154 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=1984525 read=153616
5. 1,678.449 17,492.382 ↑ 1.1 293,413 1

Hash Right Join (cost=144,559.49..3,242,055.37 rows=313,133 width=2,108) (actual time=11,989.960..17,492.382 rows=293,413 loops=1)

  • Hash Cond: (rh.registry_id = ra.patient_registry_id)
  • Join Filter: ((rh.operation_timestamp = (SubPlan 39)) OR ((rh.operation_timestamp = (SubPlan 41)) AND (NOT (alternatives: SubPlan 42 or hashed SubPlan 43))))
  • Rows Removed by Join Filter: 112265
  • Buffers: shared hit=1956900 read=153381
6. 1,195.493 1,195.493 ↑ 1.0 406,021 1

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

  • Buffers: shared hit=14207 read=87494
7. 471.987 11,983.671 ↑ 1.1 293,413 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 98718kB
  • Buffers: shared hit=8554 read=61080
8. 150.660 11,511.684 ↑ 1.1 293,413 1

Hash Left Join (cost=73,948.18..140,645.33 rows=313,133 width=427) (actual time=6,154.956..11,511.684 rows=293,413 loops=1)

  • Hash Cond: (ra.exclude_reason_id = er.id)
  • Buffers: shared hit=8554 read=61080
9. 160.478 11,360.916 ↑ 1.1 293,413 1

Hash Left Join (cost=73,947.11..139,547.59 rows=313,133 width=407) (actual time=6,154.795..11,360.916 rows=293,413 loops=1)

  • Hash Cond: (ra.exclude_mo_id = mo2.id)
  • Buffers: shared hit=8554 read=61079
10. 262.105 11,169.889 ↑ 1.1 293,413 1

Hash Left Join (cost=70,612.26..135,390.73 rows=313,133 width=359) (actual time=6,123.834..11,169.889 rows=293,413 loops=1)

  • Hash Cond: ((r.mo_oid)::text = (mo1.oid)::text)
  • Buffers: shared hit=5718 read=61079
11. 134.412 10,860.050 ↑ 1.1 293,413 1

Hash Left Join (cost=67,277.42..131,233.75 rows=313,133 width=333) (actual time=6,075.728..10,860.050 rows=293,413 loops=1)

  • Hash Cond: (ra.disability_group_id = dg.id)
  • Buffers: shared hit=2882 read=61079
12. 692.097 10,725.590 ↑ 1.1 293,413 1

Hash Join (cost=67,276.33..130,310.98 rows=313,133 width=317) (actual time=6,075.614..10,725.590 rows=293,413 loops=1)

  • Hash Cond: ((split_part((r.mo_oid)::text, '.'::text, 10))::smallint = re.id)
  • Buffers: shared hit=2882 read=61078
13. 1,219.015 10,033.373 ↑ 1.1 293,413 1

Hash Join (cost=67,273.26..129,319.15 rows=313,133 width=276) (actual time=6,075.385..10,033.373 rows=293,413 loops=1)

  • Hash Cond: (p.id = r.patient_id)
  • Buffers: shared hit=2878 read=61078
14. 2,745.672 2,745.672 ↑ 1.0 1,660,314 1

Seq Scan on nr_patient p (cost=0.00..52,468.50 rows=1,718,950 width=91) (actual time=0.083..2,745.672 rows=1,660,314 loops=1)

  • Buffers: shared hit=1194 read=34085
15. 336.229 6,068.686 ↑ 1.1 293,413 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 57947kB
  • Buffers: shared hit=1684 read=26993
16. 1,032.442 5,732.457 ↑ 1.1 293,413 1

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

  • Hash Cond: (r.id = ra.patient_registry_id)
  • Buffers: shared hit=1684 read=26993
17. 4,276.977 4,276.977 ↑ 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.028..4,276.977 rows=1,706,280 loops=1)

  • Filter: (create_date <= '2020-05-18'::date)
  • Rows Removed by Filter: 564
  • Buffers: shared hit=1677 read=21785
18. 212.568 423.038 ↑ 1.1 293,516 1

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

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

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

  • Buffers: shared hit=7 read=5208
20. 0.072 0.120 ↑ 1.0 92 1

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

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

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

  • Buffers: shared hit=1
22. 0.012 0.048 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=1
23. 0.036 0.036 ↑ 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.033..0.036 rows=4 loops=1)

  • Buffers: shared read=1
24. 17.703 47.734 ↓ 1.0 22,187 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 2846kB
  • Buffers: shared hit=2836
25. 30.031 30.031 ↓ 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.016..30.031 rows=22,187 loops=1)

  • Buffers: shared hit=2836
26. 13.666 30.549 ↓ 1.0 22,187 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 2423kB
  • Buffers: shared hit=2836
27. 16.883 16.883 ↓ 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.020..16.883 rows=22,187 loops=1)

  • Buffers: shared hit=2836
28. 0.012 0.108 ↑ 1.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=1
29. 0.096 0.096 ↑ 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.094..0.096 rows=3 loops=1)

  • Buffers: shared read=1
30.          

SubPlan (for Hash Right Join)

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

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

  • Buffers: shared hit=1308164 read=10
32.          

Initplan (for Result)

33. 405.678 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=1308164 read=10
34. 1,622.712 1,622.712 ↑ 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.004..0.004 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=1308164 read=10
35. 0.000 336.795 ↑ 1.0 1 112,265

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

  • Buffers: shared hit=382921 read=1
36.          

Initplan (for Result)

37. 112.265 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=382921 read=1
38. 224.530 224.530 ↑ 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.002..0.002 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=382921 read=1
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. 269.584 269.584 ↑ 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.85 rows=405,919 width=8) (actual time=0.027..269.584 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=243054 read=4796
41. 0.033 0.054 ↑ 1.0 92 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
  • Buffers: shared hit=1
42. 0.021 0.021 ↑ 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.021 rows=92 loops=1)

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

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

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

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

  • Buffers: shared hit=1
45. 0.035 0.093 ↑ 1.0 96 1

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

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

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

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

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

  • Buckets: 32768 Batches: 1 Memory Usage: 2846kB
  • Buffers: shared hit=2836
48. 9.943 9.943 ↓ 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.009..9.943 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. 586.826 2,934.130 ↑ 1.0 1 293,413

Aggregate (cost=1.51..1.52 rows=1 width=32) (actual time=0.010..0.010 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,449.082 ↑ 1.0 1 293,413

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

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

Hash Join (cost=1.89..3.09 rows=1 width=58) (actual time=0.104..0.106 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,047.887 ↑ 1.0 1 293,413

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=836293
65. 28,167.648 28,167.648 ↑ 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.096 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. 0.000 1,760.478 ↓ 0.0 0 293,413

Limit (cost=0.01..1.51 rows=1 width=32) (actual time=0.006..0.006 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=594551
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=594551
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=594548
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.003..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=592081
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=592081
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. 293.413 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. 880.239 2,640.717 ↓ 0.0 0 293,413

Sort (cost=8.74..8.74 rows=1 width=47) (actual time=0.009..0.009 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=2070180 read=874
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=2070180 read=874
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=2070180 read=874
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=1762994 read=874
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=1302215 read=874
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. 586.826 46,065.841 ↑ 1.0 1 293,413

Aggregate (cost=640.46..640.47 rows=1 width=8) (actual time=0.157..0.157 rows=1 loops=293,413)

  • Buffers: shared hit=24186121 read=30825
129. 22,577.447 45,479.015 ↓ 0.0 0 293,413

Nested Loop Left Join (cost=0.85..640.46 rows=1 width=8) (actual time=0.154..0.155 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=24186121 read=30825
130. 4,107.782 4,107.782 ↑ 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.014 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=2449307 read=937
131. 9,703.886 18,793.786 ↑ 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..25.74 rows=1 width=172) (actual time=0.009..0.011 rows=1 loops=1,708,526)

  • Index Cond: (vr.id = recipe_id)
  • Filter: ((operation_timestamp = (SubPlan 24)) OR ((operation_timestamp = (SubPlan 26)) AND (NOT (alternatives: SubPlan 27 or hashed SubPlan 28))))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=15421807 read=7213
132.          

SubPlan (for Index Scan)

133. 0.000 7,972.656 ↑ 1.0 1 1,993,164

Result (cost=1.45..1.46 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1,993,164)

  • Buffers: shared hit=6901308 read=47
134.          

Initplan (for Result)

135. 1,993.164 7,972.656 ↑ 1.0 1 1,993,164

Limit (cost=0.43..1.45 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1,993,164)

  • Buffers: shared hit=6901308 read=47
136. 5,979.492 5,979.492 ↑ 4.0 1 1,993,164

Index Only Scan Backward using nr_vzn_recipe_history_recipe_id_operation_timestamp_idx on nr_vzn_recipe_history (cost=0.43..4.52 rows=4 width=8) (actual time=0.003..0.003 rows=1 loops=1,993,164)

  • Index Cond: ((recipe_id = vr.id) AND (operation_timestamp IS NOT NULL) AND (operation_timestamp < '2020-05-19 00:00:00'::timestamp without time zone))
  • Heap Fetches: 410262
  • Buffers: shared hit=6901308 read=47
137. 0.000 854.568 ↑ 1.0 1 284,856

Result (cost=1.45..1.46 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=284,856)

  • Buffers: shared hit=935877 read=7
138.          

Initplan (for Result)

139. 0.000 854.568 ↑ 1.0 1 284,856

Limit (cost=0.43..1.45 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=284,856)

  • Buffers: shared hit=935877 read=7
140. 854.568 854.568 ↑ 4.0 1 284,856

Index Only Scan using nr_vzn_recipe_history_recipe_id_operation_timestamp_idx on nr_vzn_recipe_history nr_vzn_recipe_history_1 (cost=0.43..4.51 rows=4 width=8) (actual time=0.003..0.003 rows=1 loops=284,856)

  • Index Cond: ((recipe_id = vr.id) AND (operation_timestamp IS NOT NULL))
  • Heap Fetches: 38901
  • Buffers: shared hit=935877 read=7
141. 262.676 262.676 ↑ 4.0 1 131,338

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

  • Index Cond: ((recipe_id = vr.id) AND (operation_timestamp < '2020-05-19 00:00:00'::timestamp without time zone))
  • Heap Fetches: 17049
  • Buffers: shared hit=453189
142. 0.000 0.000 ↓ 0.0 0

Index Only Scan using nr_vzn_recipe_history_recipe_id_operation_timestamp_idx on nr_vzn_recipe_history nr_vzn_recipe_history_3 (cost=0.43..117,440.83 rows=2,001,774 width=8) (never executed)

  • Index Cond: (operation_timestamp < '2020-05-19 00:00:00'::timestamp without time zone)
  • Heap Fetches: 0
143. 293.413 32,275.430 ↑ 1.0 1 293,413

Aggregate (cost=640.52..640.53 rows=1 width=8) (actual time=0.110..0.110 rows=1 loops=293,413)

  • Buffers: shared hit=24216927
144. 17,968.444 31,982.017 ↑ 3.8 6 293,413

Nested Loop Left Join (cost=0.85..640.46 rows=23 width=8) (actual time=0.011..0.109 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=24216927
145. 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=2450244
146. 4,862.946 11,959.682 ↑ 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..25.74 rows=1 width=172) (actual time=0.006..0.007 rows=1 loops=1,708,526)

  • Index Cond: (vr_1.id = recipe_id)
  • Filter: ((operation_timestamp = (SubPlan 31)) OR ((operation_timestamp = (SubPlan 33)) AND (NOT (alternatives: SubPlan 34 or hashed SubPlan 35))))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=15429020
147.          

SubPlan (for Index Scan)

148. 0.000 5,979.492 ↑ 1.0 1 1,993,164

Result (cost=1.45..1.46 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1,993,164)

  • Buffers: shared hit=6901355
149.          

Initplan (for Result)

150. 1,993.164 5,979.492 ↑ 1.0 1 1,993,164

Limit (cost=0.43..1.45 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1,993,164)

  • Buffers: shared hit=6901355
151. 3,986.328 3,986.328 ↑ 4.0 1 1,993,164

Index Only Scan Backward using nr_vzn_recipe_history_recipe_id_operation_timestamp_idx on nr_vzn_recipe_history nr_vzn_recipe_history_4 (cost=0.43..4.52 rows=4 width=8) (actual time=0.002..0.002 rows=1 loops=1,993,164)

  • Index Cond: ((recipe_id = vr_1.id) AND (operation_timestamp IS NOT NULL) AND (operation_timestamp < '2020-05-19 00:00:00'::timestamp without time zone))
  • Heap Fetches: 410262
  • Buffers: shared hit=6901355
152. 0.000 854.568 ↑ 1.0 1 284,856

Result (cost=1.45..1.46 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=284,856)

  • Buffers: shared hit=935884
153.          

Initplan (for Result)

154. 0.000 854.568 ↑ 1.0 1 284,856

Limit (cost=0.43..1.45 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=284,856)

  • Buffers: shared hit=935884
155. 854.568 854.568 ↑ 4.0 1 284,856

Index Only Scan using nr_vzn_recipe_history_recipe_id_operation_timestamp_idx on nr_vzn_recipe_history nr_vzn_recipe_history_5 (cost=0.43..4.51 rows=4 width=8) (actual time=0.003..0.003 rows=1 loops=284,856)

  • Index Cond: ((recipe_id = vr_1.id) AND (operation_timestamp IS NOT NULL))
  • Heap Fetches: 38901
  • Buffers: shared hit=935884
156. 262.676 262.676 ↑ 4.0 1 131,338

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

  • Index Cond: ((recipe_id = vr_1.id) AND (operation_timestamp < '2020-05-19 00:00:00'::timestamp without time zone))
  • Heap Fetches: 17049
  • Buffers: shared hit=453189
157. 0.000 0.000 ↓ 0.0 0

Index Only Scan using nr_vzn_recipe_history_recipe_id_operation_timestamp_idx on nr_vzn_recipe_history nr_vzn_recipe_history_7 (cost=0.43..117,440.83 rows=2,001,774 width=8) (never executed)

  • Index Cond: (operation_timestamp < '2020-05-19 00:00:00'::timestamp without time zone)
  • Heap Fetches: 0
158. 0.000 0.000 ↓ 0.0 0

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

159. 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 : 72.485 ms