explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R47o

Settings
# exclusive inclusive rows x rows loops node
1. 271,642.239 62,303,787.318 ↓ 0.0 0 1

Insert on tm_wz.wt_subject_rna_probeset (cost=694,209.48..694,209.53 rows=1 width=1,060) (actual time=62,303,787.318..62,303,787.318 rows=0 loops=1)

  • Buffers: shared hit=2,294,414,013 read=1,137,764 dirtied=1,137,483, temp read=510,073 written=510,126
2. 32,690.751 62,032,145.079 ↓ 110,335,800.0 110,335,800 1

Subquery Scan on *SELECT* (cost=694,209.48..694,209.53 rows=1 width=1,060) (actual time=61,881,883.212..62,032,145.079 rows=110,335,800 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=2,181,803,531, temp read=510,073 written=510,126
3. 81,235.045 61,999,454.328 ↓ 110,335,800.0 110,335,800 1

GroupAggregate (cost=694,209.48..694,209.51 rows=1 width=274) (actual time=61,881,883.208..61,999,454.328 rows=110,335,800 loops=1)

  • Output: md.probeset, avg((md.intensity_value)::numeric), sd.assay_id, sd.patient_id, 'GSE103147_OSPF'::character varying(100)
  • Group Key: md.probeset, sd.patient_id, sd.assay_id
  • Buffers: shared hit=2,181,803,531, temp read=510,073 written=510,126
4. 459,778.403 61,918,219.283 ↓ 110,335,800.0 110,335,800 1

Sort (cost=694,209.48..694,209.49 rows=1 width=27) (actual time=61,881,883.196..61,918,219.283 rows=110,335,800 loops=1)

  • Output: md.probeset, sd.assay_id, sd.patient_id, md.intensity_value
  • Sort Key: md.probeset, sd.patient_id, sd.assay_id
  • Sort Method: external merge Disk: 4,080,584kB
  • Buffers: shared hit=2,181,803,531, temp read=510,073 written=510,126
5. 772,942.359 61,458,440.880 ↓ 110,335,800.0 110,335,800 1

Nested Loop Semi Join (cost=0.29..694,209.47 rows=1 width=27) (actual time=0.443..61,458,440.880 rows=110,335,800 loops=1)

  • Output: md.probeset, sd.assay_id, sd.patient_id, md.intensity_value
  • Join Filter: ((sd.subject_id)::text = (lt_src_rna_seq_subj_samp_map.subject_id)::text)
  • Rows Removed by Join Filter: 11,210,688,144
  • Buffers: shared hit=2,181,803,531
6. 10,148,096.953 60,354,491.121 ↓ 7,881,128.6 110,335,800 1

Nested Loop Left Join (cost=0.29..693,414.46 rows=14 width=38) (actual time=0.133..60,354,491.121 rows=110,335,800 loops=1)

  • Output: sd.assay_id, sd.patient_id, sd.subject_id, md.probeset, md.intensity_value
  • Join Filter: ((md.expr_id)::text = (sd.sample_cd)::text)
  • Rows Removed by Join Filter: 181,943,734,200
  • Buffers: shared hit=2,181,803,468
7. 39.068 39.068 ↓ 9,900.0 9,900 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=26) (actual time=0.019..39.068 rows=9,900 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 = 'GSE103147_OSPF'::text)
  • Filter: (((sd.platform)::text = 'RNASEQCOG'::text) AND ((sd.source_cd)::text = 'GEO'::text))
  • Buffers: shared hit=1,868
8. 50,206,355.100 50,206,355.100 ↓ 156.0 18,389,300 9,900

Seq Scan on tm_lz.lt_src_rna_seq_data md (cost=0.00..691,934.80 rows=117,888 width=30) (actual time=0.003..5,071.349 rows=18,389,300 loops=9,900)

  • Output: md.trial_name, md.probeset, md.expr_id, md.intensity_value
  • Filter: (sign((md.intensity_value)::numeric) = '1'::numeric)
  • Rows Removed by Filter: 5,188,950
  • Buffers: shared hit=2,181,801,600
9. 331,007.029 331,007.400 ↑ 31.6 103 110,335,800

Materialize (cost=0.00..119.81 rows=3,254 width=8) (actual time=0.000..0.003 rows=103 loops=110,335,800)

  • Output: lt_src_rna_seq_subj_samp_map.subject_id
  • Buffers: shared hit=63
10. 0.371 0.371 ↑ 2.5 1,287 1

Seq Scan on tm_lz.lt_src_rna_seq_subj_samp_map (cost=0.00..103.54 rows=3,254 width=8) (actual time=0.205..0.371 rows=1,287 loops=1)

  • Output: lt_src_rna_seq_subj_samp_map.subject_id
  • Buffers: shared hit=63
Planning time : 0.518 ms
Execution time : 62,304,081.842 ms