explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sUFu

Settings
# exclusive inclusive rows x rows loops node
1. 39.163 76,225.762 ↑ 5.9 12,121 1

Sort (cost=686,900.42..687,078.65 rows=71,292 width=283) (actual time=76,219.173..76,225.762 rows=12,121 loops=1)

  • Output: p.pat_id, (replace(p.pat_name, '^'::text, ' '::text)), p.pat_birthdate, p.pat_sex, s.pk, s.study_iuid, s.accession_no, (replace(s.ref_physician, '^'::text, ' '::text)), (replace(s.study_desc, '^'::text, ' '::text)), (replace(s.mods_in_study, 'SR'::text, 'OT'::text)), s.num_instances, s.study_datetime, ((SubPlan 1)), ((SubPlan 2))
  • Sort Key: s.study_datetime DESC
  • Sort Method: quicksort Memory: 3606kB
2. 243.641 76,186.599 ↑ 5.9 12,121 1

Hash Join (cost=31,698.24..671,647.77 rows=71,292 width=283) (actual time=5,141.973..76,186.599 rows=12,121 loops=1)

  • Output: p.pat_id, replace(p.pat_name, '^'::text, ' '::text), p.pat_birthdate, p.pat_sex, s.pk, s.study_iuid, s.accession_no, replace(s.ref_physician, '^'::text, ' '::text), replace(s.study_desc, '^'::text, ' '::text), replace(s.mods_in_study, 'SR'::text, 'OT'::text), s.num_instances, s.study_datetime, (SubPlan 1), (SubPlan 2)
  • Hash Cond: (p.pk = s.patient_fk)
3. 232.867 232.867 ↓ 1.0 225,827 1

Seq Scan on public.patient p (cost=0.00..8,783.77 rows=221,177 width=53) (actual time=0.005..232.867 rows=225,827 loops=1)

  • Output: p.pk, p.merge_fk, p.pat_id, p.pat_id_issuer, p.pat_name, p.pat_fn_sx, p.pat_gn_sx, p.pat_i_name, p.pat_p_name, p.pat_birthdate, p.pat_sex, p.pat_custom1, p.pat_custom2, p.pat_custom3, p.created_time, p.updated_time, p.pat_attrs
4. 13.122 5,056.782 ↑ 5.9 12,121 1

Hash (cost=30,807.09..30,807.09 rows=71,292 width=120) (actual time=5,056.782..5,056.782 rows=12,121 loops=1)

  • Output: s.pk, s.study_iuid, s.accession_no, s.ref_physician, s.study_desc, s.mods_in_study, s.num_instances, s.study_datetime, s.patient_fk
  • Buckets: 131072 Batches: 1 Memory Usage: 2695kB
5. 4,825.567 5,043.660 ↑ 5.9 12,121 1

Seq Scan on public.study s (cost=18,503.15..30,807.09 rows=71,292 width=120) (actual time=401.848..5,043.660 rows=12,121 loops=1)

  • Output: s.pk, s.study_iuid, s.accession_no, s.ref_physician, s.study_desc, s.mods_in_study, s.num_instances, s.study_datetime, s.patient_fk
  • Filter: ((NOT (hashed SubPlan 3)) AND (s.study_datetime >= (('now'::cstring)::date - '3650 days'::interval)))
  • Rows Removed by Filter: 127905
6.          

SubPlan (forSeq Scan)

7. 218.093 218.093 ↓ 1.0 233,002 1

Seq Scan on public.ris_ficha (cost=0.00..17,921.72 rows=232,572 width=8) (actual time=0.039..218.093 rows=233,002 loops=1)

  • Output: ris_ficha.study_fk
  • Filter: (ris_ficha.study_fk IS NOT NULL)
8.          

SubPlan (forHash Join)

9. 24.242 69,901.807 ↑ 1.0 1 12,121

Limit (cost=0.42..4.19 rows=1 width=9) (actual time=5.766..5.767 rows=1 loops=12,121)

  • Output: series.src_aet
10. 69,877.565 69,877.565 ↑ 11.0 1 12,121

Index Scan using study_fk on public.series (cost=0.42..41.82 rows=11 width=9) (actual time=5.765..5.765 rows=1 loops=12,121)

  • Output: series.src_aet
  • Index Cond: (series.study_fk = s.pk)
  • Filter: (series.src_aet IS NOT NULL)
11. 36.363 751.502 ↑ 1.0 1 12,121

Limit (cost=0.42..4.56 rows=1 width=20) (actual time=0.061..0.062 rows=1 loops=12,121)

  • Output: series_1.institution
12. 715.139 715.139 ↑ 10.0 1 12,121

Index Scan using study_fk on public.series series_1 (cost=0.42..41.82 rows=10 width=20) (actual time=0.059..0.059 rows=1 loops=12,121)

  • Output: series_1.institution
  • Index Cond: (series_1.study_fk = s.pk)
  • Filter: ((series_1.src_aet IS NOT NULL) AND (series_1.institution IS NOT NULL))
  • Rows Removed by Filter: 0
Planning time : 713.570 ms