explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xaEG : ris

Settings
# exclusive inclusive rows x rows loops node
1. 0.041 37,548.034 ↑ 1.4 1,682 1

Sort (cost=7,712,767.69..7,712,773.76 rows=2,429 width=211) (actual time=37,547.695..37,548.034 rows=1,682 loops=1)

  • Output: p.pat_id, (replace(p.pat_name, '^'::text, ' '::text)), p.pat_birthdate, p.pat_sex, s.pk, s.accession_no, (replace(s.ref_physician, '^'::text, ' '::text)), (replace(s.study_desc, '^'::text, ' '::text)), s.mods_in_study, s.num_instances, s.study_datetime, ((SubPlan 1)), ((SubPlan 2))
  • Sort Key: s.study_datetime DESC
  • Sort Method: quicksort Memory: 472kB
2. 177.978 37,547.993 ↑ 1.4 1,682 1

Gather (cost=1,093.90..7,712,631.10 rows=2,429 width=211) (actual time=109.211..37,547.993 rows=1,682 loops=1)

  • Output: p.pat_id, replace(p.pat_name, '^'::text, ' '::text), p.pat_birthdate, p.pat_sex, s.pk, s.accession_no, replace(s.ref_physician, '^'::text, ' '::text), replace(s.study_desc, '^'::text, ' '::text), s.mods_in_study, s.num_instances, s.study_datetime, (SubPlan 1), (SubPlan 2)
  • Workers Planned: 2
  • Workers Launched: 2
3. 7.455 37,351.513 ↑ 1.8 561 3

Nested Loop (cost=93.90..7,705,009.21 rows=1,012 width=118) (actual time=98.072..37,351.513 rows=561 loops=3)

  • Output: p.pat_id, p.pat_name, p.pat_birthdate, p.pat_sex, s.pk, s.accession_no, s.ref_physician, s.study_desc, s.mods_in_study, s.num_instances, s.study_datetime
  • Inner Unique: true
  • Worker 0: actual time=86.594..37143.802 rows=330 loops=1
  • Worker 1: actual time=98.903..37542.019 rows=338 loops=1
4. 37,326.493 37,344.049 ↑ 1.8 561 3

Parallel Bitmap Heap Scan on public.study s (cost=93.48..7,700,383.47 rows=1,012 width=77) (actual time=98.024..37,344.049 rows=561 loops=3)

  • Output: s.pk, s.patient_fk, s.accno_issuer_fk, s.study_iuid, s.study_id, s.study_datetime, s.accession_no, s.ref_physician, s.ref_phys_fn_sx, s.ref_phys_gn_sx, s.ref_phys_i_name, s.ref_phys_p_name, s.study_desc, s.study_custom1, s.study_custom2, s.study_custom3, s.study_status_id, s.mods_in_study, s.cuids_in_study, s.num_series, s.num_instances, s.ext_retr_aet, s.retrieve_aets, s.fileset_iuid, s.fileset_id, s.availability, s.study_status, s.checked_time, s.created_time, s.updated_time, s.study_attrs
  • Recheck Cond: (s.study_datetime >= (('now'::cstring)::date - '30 days'::interval))
  • Filter: (NOT (SubPlan 3))
  • Rows Removed by Filter: 1135
  • Heap Blocks: exact=237
  • Worker 0: actual time=86.528..37139.279 rows=330 loops=1
  • Worker 1: actual time=98.844..37536.786 rows=338 loops=1
5. 0.541 0.541 ↓ 1.0 5,092 1

Bitmap Index Scan on study_datetime (cost=0.00..92.87 rows=4,859 width=0) (actual time=0.541..0.541 rows=5,092 loops=1)

  • Index Cond: (s.study_datetime >= (('now'::cstring)::date - '30 days'::interval))
6.          

SubPlan (for Parallel Bitmap Heap Scan)

7. 0.000 17.015 ↑ 2.1 63,901 5,087

Materialize (cost=0.00..7,260.32 rows=134,858 width=8) (actual time=0.002..17.015 rows=63,901 loops=5,087)

  • Output: ris_ficha.study_fk
  • Worker 0: actual time=0.003..35.965 rows=67295 loops=913
  • Worker 1: actual time=0.003..33.504 rows=64565 loops=987
8. 29.811 29.811 ↑ 2.0 65,913 1,901

Seq Scan on public.ris_ficha (cost=0.00..6,059.03 rows=134,858 width=8) (actual time=0.003..29.811 rows=65,913 loops=1,901)

  • Output: ris_ficha.study_fk
  • Filter: (ris_ficha.study_fk IS NOT NULL)
  • Rows Removed by Filter: 25
  • Worker 0: actual time=0.003..30.965 rows=67295 loops=913
  • Worker 1: actual time=0.002..28.707 rows=64565 loops=987
9. 0.009 0.009 ↑ 1.0 1 1,682

Index Scan using patient_pk on public.patient p (cost=0.42..4.57 rows=1 width=57) (actual time=0.009..0.009 rows=1 loops=1,682)

  • 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
  • Index Cond: (p.pk = s.patient_fk)
  • Worker 0: actual time=0.009..0.009 rows=1 loops=330
  • Worker 1: actual time=0.010..0.010 rows=1 loops=338
10.          

SubPlan (for Gather)

11. 1.682 11.774 ↑ 1.0 1 1,682

Limit (cost=0.43..1.28 rows=1 width=6) (actual time=0.007..0.007 rows=1 loops=1,682)

  • Output: series.src_aet
12. 10.092 10.092 ↑ 14.0 1 1,682

Index Scan using study_fk on public.series (cost=0.43..12.32 rows=14 width=6) (actual time=0.006..0.006 rows=1 loops=1,682)

  • Output: series.src_aet
  • Index Cond: (series.study_fk = s.pk)
  • Filter: (series.src_aet IS NOT NULL)
13. 1.682 6.728 ↑ 1.0 1 1,682

Limit (cost=0.43..1.34 rows=1 width=18) (actual time=0.004..0.004 rows=1 loops=1,682)

  • Output: series_1.institution
14. 5.046 5.046 ↑ 13.0 1 1,682

Index Scan using study_fk on public.series series_1 (cost=0.43..12.32 rows=13 width=18) (actual time=0.003..0.003 rows=1 loops=1,682)

  • 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: 1
Planning time : 0.846 ms