explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7IxV

Settings
# exclusive inclusive rows x rows loops node
1. 10.755 1,180.717 ↑ 1.0 1 1

Aggregate (cost=105,465.25..105,465.26 rows=1 width=32) (actual time=1,180.716..1,180.717 rows=1 loops=1)

2. 42.062 1,169.962 ↓ 42,806.0 42,806 1

Nested Loop (cost=1,429.21..105,465.25 rows=1 width=8) (actual time=47.867..1,169.962 rows=42,806 loops=1)

  • Join Filter: ((mvpatmeasure1.pat_id = attachmentaap5.patient_ident_id) AND (mvpatmeasure1.mi_id = attachmentaap5.mi_id) AND ((mvpatpdtproc2.action_date)::date >= (attachmentaap5.enrl_register_date)::date) AND ((attachmentaap5.enrl_unregister_date IS NULL) OR ((mvpatpdtproc2.action_date)::date < (attachmentaap5.enrl_unregister_date)::date)) AND (((mvpatpdtproc2.action_date)::date - '18 years'::interval) >= attachmentaap5.patient_birthday))
  • Rows Removed by Join Filter: 5308
3. 50.571 1,042.264 ↓ 42,818.0 42,818 1

Nested Loop (cost=1,428.78..105,464.44 rows=1 width=184) (actual time=47.844..1,042.264 rows=42,818 loops=1)

  • Join Filter: ((mvpatmeasure1.pat_id = attachmentaap6.patient_ident_id) AND (mvpatmeasure1.mi_id = attachmentaap6.mi_id) AND ((mvpatpdtproc1.action_date)::date >= (attachmentaap6.enrl_register_date)::date) AND ((attachmentaap6.enrl_unregister_date IS NULL) OR ((mvpatpdtproc1.action_date)::date < (attachmentaap6.enrl_unregister_date)::date)) AND (((mvpatpdtproc1.action_date)::date - '18 years'::interval) >= attachmentaap6.patient_birthday))
  • Rows Removed by Join Filter: 5326
4. 24.694 905.997 ↓ 42,848.0 42,848 1

Nested Loop (cost=1,428.35..105,463.63 rows=1 width=176) (actual time=47.813..905.997 rows=42,848 loops=1)

  • Join Filter: ((mvpatpdtproc1.procedure_item_id <> mvpatpdtproc2.procedure_item_id) AND (mvpatmeasure1.pat_id = mvpatpdtproc2.pat_id) AND (mvpatmeasure1.mi_id = mvpatpdtproc2.mi_id))
5. 19.994 670.055 ↓ 17,604.0 17,604 1

Nested Loop (cost=1,427.92..105,462.71 rows=1 width=160) (actual time=47.772..670.055 rows=17,604 loops=1)

  • Join Filter: ((mvpatmeasure1.pat_id = attachmentaap4.patient_ident_id) AND (mvpatmeasure1.mi_id = attachmentaap4.mi_id) AND ((mvpatmeasure4.action_date)::date >= (attachmentaap4.enrl_register_date)::date) AND ((attachmentaap4.enrl_unregister_date IS NULL) OR ((mvpatmeasure4.action_date)::date < (attachmentaap4.enrl_unregister_date)::date)) AND (((mvpatmeasure4.action_date)::date - '18 years'::interval) >= attachmentaap4.patient_birthday))
  • Rows Removed by Join Filter: 2630
6. 11.343 614.771 ↓ 17,645.0 17,645 1

Nested Loop (cost=1,427.49..105,461.90 rows=1 width=152) (actual time=47.748..614.771 rows=17,645 loops=1)

  • Join Filter: ((mvpatmeasure1.pat_id = attachmentaap3.patient_ident_id) AND (mvpatmeasure1.mi_id = attachmentaap3.mi_id) AND ((mvpatmeasure3.action_date)::date >= (attachmentaap3.enrl_register_date)::date) AND ((attachmentaap3.enrl_unregister_date IS NULL) OR ((mvpatmeasure3.action_date)::date < (attachmentaap3.enrl_unregister_date)::date)) AND (((mvpatmeasure3.action_date)::date - '18 years'::interval) >= attachmentaap3.patient_birthday))
  • Rows Removed by Join Filter: 2675
7. 4.380 550.235 ↓ 17,731.0 17,731 1

Nested Loop (cost=1,427.06..105,461.09 rows=1 width=144) (actual time=47.713..550.235 rows=17,731 loops=1)

  • Join Filter: ((mvpatmeasure1.pat_id = mvpatpdtproc1.pat_id) AND (mvpatmeasure1.mi_id = mvpatpdtproc1.mi_id))
8. 5.446 426.359 ↓ 9,958.0 9,958 1

Nested Loop (cost=1,426.63..105,460.17 rows=1 width=112) (actual time=47.651..426.359 rows=9,958 loops=1)

  • Join Filter: ((mvpatmeasure1.measurement_id <> mvpatmeasure4.measurement_id) AND (mvpatmeasure4.measurement_id <> mvpatmeasure2.measurement_id) AND (mvpatmeasure4.measurement_id <> mvpatmeasure3.measurement_id) AND (mvpatmeasure1.pat_id = mvpatmeasure4.pat_id) AND (mvpatmeasure1.mi_id = mvpatmeasure4.mi_id))
  • Rows Removed by Join Filter: 7388
9. 5.543 270.514 ↓ 8,847.0 8,847 1

Nested Loop (cost=1,426.19..105,458.09 rows=1 width=112) (actual time=46.209..270.514 rows=8,847 loops=1)

  • Join Filter: ((mvpatmeasure1.measurement_id <> mvpatmeasure3.measurement_id) AND (mvpatmeasure2.measurement_id <> mvpatmeasure3.measurement_id) AND (mvpatmeasure1.pat_id = mvpatmeasure3.pat_id) AND (mvpatmeasure1.mi_id = mvpatmeasure3.mi_id))
10. 3.493 203.611 ↓ 4,720.0 4,720 1

Nested Loop (cost=1,425.76..105,456.01 rows=1 width=80) (actual time=46.023..203.611 rows=4,720 loops=1)

  • Join Filter: ((mvpatmeasure1.measurement_id <> mvpatmeasure2.measurement_id) AND (mvpatmeasure1.pat_id = mvpatmeasure2.pat_id) AND (mvpatmeasure1.mi_id = mvpatmeasure2.mi_id) AND ((mvpatmeasure2.action_date)::date >= (attachmentaap2.enrl_register_date)::date) AND ((attachmentaap2.enrl_unregister_date IS NULL) OR ((mvpatmeasure2.action_date)::date < (attachmentaap2.enrl_unregister_date)::date)) AND (((mvpatmeasure2.action_date)::date - '18 years'::interval) >= attachmentaap2.patient_birthday))
  • Rows Removed by Join Filter: 727
11. 0.000 50.178 ↓ 7,497.0 7,497 1

Gather (cost=1,425.32..105,453.92 rows=1 width=76) (actual time=45.918..50.178 rows=7,497 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 17.941 86.102 ↓ 2,499.0 2,499 3

Nested Loop (cost=425.32..104,453.82 rows=1 width=76) (actual time=26.936..86.102 rows=2,499 loops=3)

  • Join Filter: ((mvpatmeasure1.pat_id = attachmentaap1.patient_ident_id) AND (mvpatmeasure1.mi_id = attachmentaap1.mi_id) AND ((mvpatmeasure1.action_date)::date >= (attachmentaap1.enrl_register_date)::date) AND ((attachmentaap1.enrl_unregister_date IS NULL) OR ((mvpatmeasure1.action_date)::date < (attachmentaap1.enrl_unregister_date)::date)) AND (((mvpatmeasure1.action_date)::date - '18 years'::interval) >= attachmentaap1.patient_birthday))
  • Rows Removed by Join Filter: 1266
13. 29.329 68.156 ↓ 25.3 2,653 3

Nested Loop (cost=424.89..104,369.12 rows=105 width=68) (actual time=26.879..68.156 rows=2,653 loops=3)

14. 27.543 38.819 ↓ 1.1 3,161 3

Parallel Bitmap Heap Scan on mvpatmeasure mvpatmeasure1 (cost=424.46..57,919.73 rows=2,966 width=32) (actual time=26.382..38.819 rows=3,161 loops=3)

  • Recheck Cond: (measurement_code = '13012'::text)
  • Filter: (((action_date)::date >= to_date('2019-01-01'::text, 'YYYY-MM-DD'::text)) AND ((action_date)::date <= to_date('2019-06-30'::text, 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 3796
  • Heap Blocks: exact=4545
15. 11.276 11.276 ↑ 1.1 20,871 1

Bitmap Index Scan on mvpatmeasure_measurement_code_idx (cost=0.00..422.69 rows=22,700 width=0) (actual time=11.276..11.276 rows=20,871 loops=1)

  • Index Cond: (measurement_code = '13012'::text)
16. 0.008 0.008 ↑ 1.0 1 9,484

Index Scan using attachmentaap_patient_ident_id_idx1 on attachmentaap attachmentaap2 (cost=0.43..15.65 rows=1 width=36) (actual time=0.008..0.008 rows=1 loops=9,484)

  • Index Cond: (patient_ident_id = mvpatmeasure1.pat_id)
  • Filter: ((mvpatmeasure1.mi_id = mi_id) AND (hw_specialization = ANY ('{1,16,31}'::text[])))
  • Rows Removed by Filter: 1
17. 0.005 0.005 ↑ 1.0 1 7,960

Index Scan using attachmentaap_patient_ident_id_idx1 on attachmentaap attachmentaap1 (cost=0.43..0.77 rows=1 width=36) (actual time=0.004..0.005 rows=1 loops=7,960)

  • Index Cond: (patient_ident_id = attachmentaap2.patient_ident_id)
  • Filter: ((patient_birthday IS NOT NULL) AND (attachmentaap2.mi_id = mi_id) AND (hw_specialization = ANY ('{1,16,31}'::text[])))
  • Rows Removed by Filter: 0
18. 149.940 149.940 ↑ 1.0 1 7,497

Index Scan using mvpatmeasure_pat_id_idx on mvpatmeasure mvpatmeasure2 (cost=0.43..2.06 rows=1 width=32) (actual time=0.019..0.020 rows=1 loops=7,497)

  • Index Cond: (pat_id = attachmentaap1.patient_ident_id)
  • Filter: ((measurement_code = ANY ('{01050,01054}'::text[])) AND (attachmentaap1.mi_id = mi_id) AND ((action_date)::date >= to_date('2019-01-01'::text, 'YYYY-MM-DD'::text)) AND ((action_date)::date <= to_date('2019-06-30'::text, 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 25
19. 61.360 61.360 ↓ 2.0 2 4,720

Index Scan using mvpatmeasure_pat_id_idx on mvpatmeasure mvpatmeasure3 (cost=0.43..2.06 rows=1 width=32) (actual time=0.010..0.013 rows=2 loops=4,720)

  • Index Cond: (pat_id = attachmentaap1.patient_ident_id)
  • Filter: ((measurement_code = '10028'::text) AND (attachmentaap1.mi_id = mi_id) AND ((action_date)::date >= to_date('2019-01-01'::text, 'YYYY-MM-DD'::text)) AND ((action_date)::date <= to_date('2019-06-30'::text, 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 30
20. 150.399 150.399 ↓ 2.0 2 8,847

Index Scan using mvpatmeasure_pat_id_idx on mvpatmeasure mvpatmeasure4 (cost=0.43..2.06 rows=1 width=32) (actual time=0.013..0.017 rows=2 loops=8,847)

  • Index Cond: (pat_id = attachmentaap1.patient_ident_id)
  • Filter: ((measurement_code = ANY ('{01050,01053}'::text[])) AND (attachmentaap1.mi_id = mi_id) AND ((action_date)::date >= to_date('2019-01-01'::text, 'YYYY-MM-DD'::text)) AND ((action_date)::date <= to_date('2019-06-30'::text, 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 37
21. 119.496 119.496 ↓ 2.0 2 9,958

Index Scan using mvpatpdtproc_pat_id_idx on mvpatpdtproc mvpatpdtproc1 (cost=0.43..0.91 rows=1 width=32) (actual time=0.009..0.012 rows=2 loops=9,958)

  • Index Cond: (pat_id = attachmentaap1.patient_ident_id)
  • Filter: ((procedure_code = '10029'::text) AND (attachmentaap1.mi_id = mi_id) AND ((action_date)::date >= to_date('2019-01-01'::text, 'YYYY-MM-DD'::text)) AND ((action_date)::date <= to_date('2019-06-30'::text, 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 19
22. 53.193 53.193 ↑ 1.0 1 17,731

Index Scan using attachmentaap_patient_ident_id_idx1 on attachmentaap attachmentaap3 (cost=0.43..0.77 rows=1 width=36) (actual time=0.002..0.003 rows=1 loops=17,731)

  • Index Cond: (patient_ident_id = attachmentaap1.patient_ident_id)
  • Filter: ((attachmentaap1.mi_id = mi_id) AND (hw_specialization = ANY ('{1,16,31}'::text[])))
  • Rows Removed by Filter: 0
23. 35.290 35.290 ↑ 1.0 1 17,645

Index Scan using attachmentaap_patient_ident_id_idx1 on attachmentaap attachmentaap4 (cost=0.43..0.77 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=17,645)

  • Index Cond: (patient_ident_id = attachmentaap1.patient_ident_id)
  • Filter: ((attachmentaap1.mi_id = mi_id) AND (hw_specialization = ANY ('{1,16,31}'::text[])))
  • Rows Removed by Filter: 0
24. 211.248 211.248 ↓ 2.0 2 17,604

Index Scan using mvpatpdtproc_pat_id_idx on mvpatpdtproc mvpatpdtproc2 (cost=0.43..0.91 rows=1 width=32) (actual time=0.008..0.012 rows=2 loops=17,604)

  • Index Cond: (pat_id = attachmentaap1.patient_ident_id)
  • Filter: ((procedure_code = '10030'::text) AND (attachmentaap1.mi_id = mi_id) AND ((action_date)::date >= to_date('2019-01-01'::text, 'YYYY-MM-DD'::text)) AND ((action_date)::date <= to_date('2019-06-30'::text, 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 22
25. 85.696 85.696 ↑ 1.0 1 42,848

Index Scan using attachmentaap_patient_ident_id_idx1 on attachmentaap attachmentaap6 (cost=0.43..0.77 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=42,848)

  • Index Cond: (patient_ident_id = attachmentaap1.patient_ident_id)
  • Filter: ((attachmentaap1.mi_id = mi_id) AND (hw_specialization = ANY ('{1,16,31}'::text[])))
  • Rows Removed by Filter: 0
26. 85.636 85.636 ↑ 1.0 1 42,818

Index Scan using attachmentaap_patient_ident_id_idx1 on attachmentaap attachmentaap5 (cost=0.43..0.77 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=42,818)

  • Index Cond: (patient_ident_id = attachmentaap1.patient_ident_id)
  • Filter: ((attachmentaap1.mi_id = mi_id) AND (hw_specialization = ANY ('{1,16,31}'::text[])))
  • Rows Removed by Filter: 0
Planning time : 168.954 ms
Execution time : 1,194.753 ms