explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lnlRi

Settings
# exclusive inclusive rows x rows loops node
1. 371.925 7,552.949 ↑ 1.0 1 1

Aggregate (cost=105,460.50..105,460.51 rows=1 width=32) (actual time=7,552.948..7,552.949 rows=1 loops=1)

2. 1,015.068 7,181.024 ↓ 2,368,822.0 2,368,822 1

Nested Loop (cost=1,427.93..105,460.49 rows=1 width=8) (actual time=43.110..7,181.024 rows=2,368,822 loops=1)

  • Join Filter: ((mvpatmeasure1.pat_id = mvpathissues5.pat_id) AND ((mvpatpdtproc1.action_date)::date >= (mvpathissues5.hi_start_date)::date) AND ((mvpathissues5.hi_end_date IS NULL) OR ((mvpatpdtproc1.action_date)::date < (mvpathissues5.hi_end_date)::date)))
  • Rows Removed by Join Filter: 1683076
3. 195.630 1,511.588 ↓ 193,932.0 193,932 1

Nested Loop (cost=1,427.49..105,459.58 rows=1 width=72) (actual time=43.059..1,511.588 rows=193,932 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: 18186
4. 118.462 927.450 ↓ 194,254.0 194,254 1

Nested Loop (cost=1,427.06..105,458.77 rows=1 width=104) (actual time=43.027..927.450 rows=194,254 loops=1)

  • Join Filter: ((mvpatmeasure1.pat_id = mvpathissues1.pat_id) AND ((mvpatmeasure2.action_date)::date >= (mvpathissues1.hi_start_date)::date) AND ((mvpathissues1.hi_end_date IS NULL) OR ((mvpatmeasure2.action_date)::date < (mvpathissues1.hi_end_date)::date)))
  • Rows Removed by Join Filter: 158817
5. 16.019 366.028 ↓ 22,148.0 22,148 1

Nested Loop (cost=1,426.63..105,457.85 rows=1 width=104) (actual time=42.992..366.028 rows=22,148 loops=1)

  • Join Filter: ((mvpatmeasure1.pat_id = mvpathissues6.pat_id) AND ((mvpatmeasure1.action_date)::date >= (mvpathissues6.hi_start_date)::date) AND ((mvpathissues6.hi_end_date IS NULL) OR ((mvpatmeasure1.action_date)::date < (mvpathissues6.hi_end_date)::date)))
  • Rows Removed by Join Filter: 24690
6. 1.414 253.223 ↓ 5,094.0 5,094 1

Nested Loop (cost=1,426.19..105,456.94 rows=1 width=104) (actual time=42.936..253.223 rows=5,094 loops=1)

  • Join Filter: ((mvpatmeasure1.pat_id = mvpatpdtproc1.pat_id) AND (mvpatmeasure1.mi_id = mvpatpdtproc1.mi_id))
7. 8.929 190.449 ↓ 4,720.0 4,720 1

Nested Loop (cost=1,425.76..105,456.01 rows=1 width=80) (actual time=42.529..190.449 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
8. 0.000 46.574 ↓ 7,497.0 7,497 1

Gather (cost=1,425.32..105,453.92 rows=1 width=84) (actual time=42.429..46.574 rows=7,497 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 19.020 88.265 ↓ 2,499.0 2,499 3

Nested Loop (cost=425.32..104,453.82 rows=1 width=84) (actual time=26.295..88.265 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
10. 29.950 69.240 ↓ 25.3 2,653 3

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

11. 29.252 39.281 ↓ 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=25.816..39.281 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=4496
12. 10.029 10.029 ↑ 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=10.029..10.029 rows=20,871 loops=1)

  • Index Cond: (measurement_code = '13012'::text)
13. 0.009 0.009 ↑ 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.009 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
14. 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
15. 134.946 134.946 ↑ 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.017..0.018 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
16. 61.360 61.360 ↑ 1.0 1 4,720

Index Scan using mvpatpdtproc_pat_id_idx on mvpatpdtproc mvpatpdtproc1 (cost=0.43..0.91 rows=1 width=24) (actual time=0.011..0.013 rows=1 loops=4,720)

  • 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: 11
17. 96.786 96.786 ↓ 9.0 9 5,094

Index Scan using mvpathissues_pat_id_idx on mvpathissues mvpathissues6 (cost=0.43..0.89 rows=1 width=24) (actual time=0.006..0.019 rows=9 loops=5,094)

  • Index Cond: (pat_id = attachmentaap1.patient_ident_id)
  • Filter: ((hi_diagnose >= 'E11'::text) AND (hi_diagnose < 'E12'::text))
  • Rows Removed by Filter: 10
18. 442.960 442.960 ↓ 16.0 16 22,148

Index Scan using mvpathissues_pat_id_idx on mvpathissues mvpathissues1 (cost=0.43..0.89 rows=1 width=24) (actual time=0.003..0.020 rows=16 loops=22,148)

  • Index Cond: (pat_id = attachmentaap1.patient_ident_id)
  • Filter: ((hi_diagnose >= 'E11'::text) AND (hi_diagnose < 'E12'::text))
  • Rows Removed by Filter: 11
19. 388.508 388.508 ↑ 1.0 1 194,254

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=194,254)

  • 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
20. 4,654.368 4,654.368 ↓ 21.0 21 193,932

Index Scan using mvpathissues_pat_id_idx on mvpathissues mvpathissues5 (cost=0.43..0.89 rows=1 width=24) (actual time=0.003..0.024 rows=21 loops=193,932)

  • Index Cond: (pat_id = attachmentaap1.patient_ident_id)
  • Filter: ((hi_diagnose >= 'E11'::text) AND (hi_diagnose < 'E12'::text))
  • Rows Removed by Filter: 13
Planning time : 151.597 ms
Execution time : 7,554.649 ms