explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UBZf

Settings
# exclusive inclusive rows x rows loops node
1. 2,112.179 82,942.964 ↓ 0.0 0 1

Insert on tm_wz.wt_subject_rna_probeset (cost=123,633.92..123,634.22 rows=7 width=1,060) (actual time=82,942.964..82,942.964 rows=0 loops=1)

  • Buffers: shared hit=11897452 read=15046 dirtied=15041, temp read=15568 written=15569
2. 415.478 80,830.785 ↓ 208,415.4 1,458,908 1

Subquery Scan on *SELECT* (cost=123,633.92..123,634.22 rows=7 width=1,060) (actual time=78,334.498..80,830.785 rows=1,458,908 loops=1)

  • Output: ("*SELECT*".probeset)::character varying(200), NULL::character varying, "*SELECT*".intensity_value, NULL::integer, NULL::double precision, "*SELECT*".assay_id, "*SELECT*".patient_id, NULL::integer, NULL::character varying, ("*SELECT*".trial_name)::character varying(200), NULL::character varying, NULL::character varying, NULL::character varying, NULL::character varying
  • Buffers: shared hit=10408470, temp read=15568 written=15569
3. 1,582.650 80,415.307 ↓ 208,415.4 1,458,908 1

GroupAggregate (cost=123,633.92..123,634.13 rows=7 width=274) (actual time=78,334.494..80,415.307 rows=1,458,908 loops=1)

  • Output: md.probeset, avg((md.intensity_value)::numeric), sd.patient_id, 'GSE102678_OSPF'::character varying(100), sd.assay_id
  • Group Key: md.probeset, sd.patient_id, sd.assay_id
  • Buffers: shared hit=10408470, temp read=15568 written=15569
4. 9,379.301 78,832.657 ↓ 485,261.7 3,396,832 1

Sort (cost=123,633.92..123,633.94 rows=7 width=27) (actual time=78,334.481..78,832.657 rows=3,396,832 loops=1)

  • Output: md.probeset, sd.patient_id, sd.assay_id, md.intensity_value
  • Sort Key: md.probeset, sd.patient_id, sd.assay_id
  • Sort Method: external merge Disk: 124544kB
  • Buffers: shared hit=10408470, temp read=15568 written=15569
5. 779.343 69,453.356 ↓ 485,261.7 3,396,832 1

Nested Loop (cost=0.70..123,633.82 rows=7 width=27) (actual time=0.040..69,453.356 rows=3,396,832 loops=1)

  • Output: md.probeset, sd.patient_id, sd.assay_id, md.intensity_value
  • Buffers: shared hit=10408470
6. 1,868.115 61,379.473 ↓ 291,781.6 1,458,908 1

Nested Loop Semi Join (cost=0.29..123,629.62 rows=5 width=27) (actual time=0.024..61,379.473 rows=1,458,908 loops=1)

  • Output: sd.patient_id, sd.assay_id, md.probeset, md.intensity_value
  • Join Filter: ((sd.subject_id)::text = (lt_src_rna_seq_subj_samp_map.subject_id)::text)
  • Rows Removed by Join Filter: 15368632
  • Buffers: shared hit=2592364
7. 5,579.383 59,511.358 ↓ 112,223.7 1,458,908 1

Nested Loop (cost=0.29..123,599.74 rows=13 width=35) (actual time=0.016..59,511.358 rows=1,458,908 loops=1)

  • Output: sd.patient_id, sd.assay_id, sd.subject_id, md.probeset, md.intensity_value
  • Join Filter: ((sd.sample_cd)::text = (md.expr_id)::text)
  • Rows Removed by Join Filter: 94829020
  • Buffers: shared hit=2592361
8. 0.141 0.141 ↓ 66.0 66 1

Index Scan using idx_de_subj_smpl_trial_ccode on deapp.de_subject_sample_mapping sd (cost=0.29..6.06 rows=1 width=27) (actual time=0.009..0.141 rows=66 loops=1)

  • Output: sd.patient_id, sd.site_id, sd.subject_id, sd.subject_type, sd.concept_code, sd.assay_id, sd.patient_uid, sd.sample_type, sd.assay_uid, sd.trial_name, sd.timepoint, sd.timepoint_cd, sd.sample_type_cd, sd.tissue_type_cd, sd.platform, sd.platform_cd, sd.tissue_type, sd.data_uid, sd.gpl_id, sd.rbm_panel, sd.sample_id, sd.sample_cd, sd.category_cd, sd.source_cd, sd.omic_source_study, sd.omic_patient_num, sd.omic_patient_id, sd.partition_id
  • Index Cond: ((sd.trial_name)::text = 'GSE102678_OSPF'::text)
  • Filter: (((sd.platform)::text = 'RNA_AFFYMETRIX'::text) AND ((sd.source_cd)::text = 'GEO'::text))
  • Buffers: shared hit=13
9. 53,931.834 53,931.834 ↓ 69.4 1,458,908 66

Seq Scan on tm_lz.lt_src_rna_seq_data md (cost=0.00..123,331.02 rows=21,013 width=30) (actual time=0.003..817.149 rows=1,458,908 loops=66)

  • Output: md.trial_name, md.probeset, md.expr_id, md.intensity_value
  • Filter: (sign((md.intensity_value)::numeric) = '1'::numeric)
  • Rows Removed by Filter: 2743774
  • Buffers: shared hit=2592348
10. 0.000 0.000 ↑ 11.0 12 1,458,908

Materialize (cost=0.00..5.98 rows=132 width=19) (actual time=0.000..0.000 rows=12 loops=1,458,908)

  • Output: lt_src_rna_seq_subj_samp_map.subject_id
  • Buffers: shared hit=3
11. 0.013 0.013 ↑ 6.0 22 1

Seq Scan on tm_lz.lt_src_rna_seq_subj_samp_map (cost=0.00..5.32 rows=132 width=19) (actual time=0.006..0.013 rows=22 loops=1)

  • Output: lt_src_rna_seq_subj_samp_map.subject_id
  • Buffers: shared hit=3
12. 7,294.540 7,294.540 ↓ 2.0 2 1,458,908

Index Only Scan using probeset_deapp_i2 on tm_cz.probeset_deapp gs (cost=0.42..0.83 rows=1 width=16) (actual time=0.005..0.005 rows=2 loops=1,458,908)

  • Output: gs.probeset, gs.platform
  • Index Cond: (gs.probeset = (md.probeset)::text)
  • Heap Fetches: 3396832
  • Buffers: shared hit=7816106
Planning time : 0.675 ms
Execution time : 82,952.233 ms