explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zcTb

Settings
# exclusive inclusive rows x rows loops node
1. 0.670 47,092.741 ↑ 1.4 1,680 1

Sort (cost=7,711,371.47..7,711,377.55 rows=2,430 width=211) (actual time=47,092.515..47,092.741 rows=1,680 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: 471kB
2. 97.242 47,092.071 ↑ 1.4 1,680 1

Gather (cost=1,093.90..7,711,234.82 rows=2,430 width=211) (actual time=105.620..47,092.071 rows=1,680 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. 8.555 46,972.989 ↑ 1.8 560 3

Nested Loop (cost=93.90..7,703,610.20 rows=1,012 width=118) (actual time=96.912..46,972.989 rows=560 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.663..46904.108 rows=301 loops=1
  • Worker 1: actual time=98.950..47086.349 rows=427 loops=1
4. 46,942.445 46,964.424 ↑ 1.8 560 3

Parallel Bitmap Heap Scan on public.study s (cost=93.48..7,698,984.52 rows=1,012 width=77) (actual time=96.863..46,964.424 rows=560 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: 1136
  • Heap Blocks: exact=217
  • Worker 0: actual time=86.597..46898.338 rows=301 loops=1
  • Worker 1: actual time=98.892..47079.667 rows=427 loops=1
5. 0.548 0.548 ↓ 1.0 5,088 1

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

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

SubPlan (for Parallel Bitmap Heap Scan)

7. 0.000 21.431 ↑ 2.1 63,916 5,088

Materialize (cost=0.00..7,259.14 rows=134,779 width=8) (actual time=0.003..21.431 rows=63,916 loops=5,088)

  • Output: ris_ficha.study_fk
  • Worker 0: actual time=0.004..43.390 rows=62452 loops=950
  • Worker 1: actual time=0.003..33.768 rows=65367 loops=1229
8. 32.787 32.787 ↑ 2.1 64,128 2,180

Seq Scan on public.ris_ficha (cost=0.00..6,058.24 rows=134,779 width=8) (actual time=0.003..32.787 rows=64,128 loops=2,180)

  • Output: ris_ficha.study_fk
  • Filter: (ris_ficha.study_fk IS NOT NULL)
  • Rows Removed by Filter: 25
  • Worker 0: actual time=0.003..37.770 rows=62452 loops=950
  • Worker 1: actual time=0.002..28.909 rows=65367 loops=1229
9. 0.010 0.010 ↑ 1.0 1 1,680

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

  • 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.012..0.012 rows=1 loops=301
  • Worker 1: actual time=0.010..0.010 rows=1 loops=427
10.          

SubPlan (for Gather)

11. 1.680 13.440 ↑ 1.0 1 1,680

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

  • Output: series.src_aet
12. 11.760 11.760 ↑ 14.0 1 1,680

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

  • Output: series.src_aet
  • Index Cond: (series.study_fk = s.pk)
  • Filter: (series.src_aet IS NOT NULL)
13. 1.680 8.400 ↑ 1.0 1 1,680

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

  • Output: series_1.institution
14. 6.720 6.720 ↑ 13.0 1 1,680

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

  • 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.824 ms