explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q1q1

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Insert on dev.mapping_view (cost=50,873.07..50,873.14 rows=1 width=1,313) (actual rows= loops=)

2.          

CTE gene_acc

3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=13,168.59..15,731.94 rows=127,777 width=39) (actual rows= loops=)

  • Output: uga.uniprot_acc, string_agg((uga.gene_accession)::text, ', '::text)
  • Group Key: uga.uniprot_acc
4. 0.000 0.000 ↓ 0.0

Sort (cost=13,168.59..13,490.64 rows=128,818 width=18) (actual rows= loops=)

  • Output: uga.uniprot_acc, uga.gene_accession
  • Sort Key: uga.uniprot_acc
5. 0.000 0.000 ↓ 0.0

Seq Scan on dev.uniprot_gene_accessions uga (cost=0.00..2,235.18 rows=128,818 width=18) (actual rows= loops=)

  • Output: uga.uniprot_acc, uga.gene_accession
6. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* (cost=35,141.14..35,141.20 rows=1 width=1,313) (actual rows= loops=)

  • Output: NULL::bigint, "*SELECT*".uniprot_id, NULL::bigint, NULL::integer, "*SELECT*".status, NULL::bigint, "*SELECT*".uniprot_acc, "*SELECT*".uniprot_tax_id, "*SELECT*".sequence_version, "*SELECT*".upi,
7. 0.000 0.000 ↓ 0.0

Unique (cost=35,141.14..35,141.18 rows=1 width=199) (actual rows= loops=)

  • Output: ue.uniprot_id, ue.uniprot_acc, ue.uniprot_tax_id, ue.sequence_version, ue.upi, ue.md5, ue.canonical_uniprot_id, ue.ensembl_derived, ue.alias, ue.entry_type, ue.gene_symbol, ue.chromosome_l
8. 0.000 0.000 ↓ 0.0

Sort (cost=35,141.14..35,141.14 rows=1 width=199) (actual rows= loops=)

  • Output: ue.uniprot_id, ue.uniprot_acc, ue.uniprot_tax_id, ue.sequence_version, ue.upi, ue.md5, ue.canonical_uniprot_id, ue.ensembl_derived, ue.alias, ue.entry_type, ue.gene_symbol, ue.chromo
  • Sort Key: ue.uniprot_id, ue.uniprot_acc, ue.sequence_version, ue.upi, ue.md5, ue.canonical_uniprot_id, ue.ensembl_derived, ue.alias, ue.entry_type, ue.gene_symbol, ue.chromosome_line, ue.len
9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=32,106.18..35,141.13 rows=1 width=199) (actual rows= loops=)

  • Output: ue.uniprot_id, ue.uniprot_acc, ue.uniprot_tax_id, ue.sequence_version, ue.upi, ue.md5, ue.canonical_uniprot_id, ue.ensembl_derived, ue.alias, ue.entry_type, ue.gene_symbol, ue.
  • Inner Unique: true
10. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=32,105.76..35,140.47 rows=1 width=159) (actual rows= loops=)

  • Output: mv.grouping_id, mv.release_mapping_history_id, ue.uniprot_id, ue.uniprot_acc, ue.uniprot_tax_id, ue.sequence_version, ue.upi, ue.md5, ue.canonical_uniprot_id, ue.ensembl_
  • Hash Cond: ((ga.uniprot_acc)::text = (ue.uniprot_acc)::text)
11. 0.000 0.000 ↓ 0.0

CTE Scan on gene_acc ga (cost=0.00..2,555.54 rows=127,777 width=80) (actual rows= loops=)

  • Output: ga.uniprot_acc, ga.gene_accession
12. 0.000 0.000 ↓ 0.0

Hash (cost=32,105.74..32,105.74 rows=1 width=127) (actual rows= loops=)

  • Output: mv.grouping_id, mv.release_mapping_history_id, ue.uniprot_id, ue.uniprot_acc, ue.uniprot_tax_id, ue.sequence_version, ue.upi, ue.md5, ue.canonical_uniprot_id, ue.en
13. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=29,206.90..32,105.74 rows=1 width=127) (actual rows= loops=)

  • Output: mv.grouping_id, mv.release_mapping_history_id, ue.uniprot_id, ue.uniprot_acc, ue.uniprot_tax_id, ue.sequence_version, ue.upi, ue.md5, ue.canonical_uniprot_id,
  • Hash Cond: ((uge.uniprot_acc)::text = (ue.uniprot_acc)::text)
  • Filter: (((mv.gene_symbol_eg)::text = (ue.gene_symbol)::text) OR ((mv.gene_accession)::text = (uge.gene_accession)::text))
14. 0.000 0.000 ↓ 0.0

Seq Scan on dev.uniprot_gene_accessions uge (cost=0.00..2,235.18 rows=128,818 width=18) (actual rows= loops=)

  • Output: uge.uniprot_acc, uge.gene_accession, uge.tax_id
15. 0.000 0.000 ↓ 0.0

Hash (cost=29,012.93..29,012.93 rows=15,517 width=144) (actual rows= loops=)

  • Output: mv.grouping_id, mv.release_mapping_history_id, mv.gene_symbol_eg, mv.gene_accession, ue.uniprot_id, ue.uniprot_acc, ue.uniprot_tax_id, ue.sequence_versi
16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=11,961.27..29,012.93 rows=15,517 width=144) (actual rows= loops=)

  • Output: mv.grouping_id, mv.release_mapping_history_id, mv.gene_symbol_eg, mv.gene_accession, ue.uniprot_id, ue.uniprot_acc, ue.uniprot_tax_id, ue.sequence
  • Join Filter: ((mv.uniprot_acc)::text <> (ue.uniprot_acc)::text)
17. 0.000 0.000 ↓ 0.0

Index Scan using mapping_view_release_mapping_history_id_idx on dev.mapping_view mv (cost=0.42..2.60 rows=1 width=40) (actual rows= loops=)

  • Output: mv.mapping_id, mv.uniprot_id, mv.transcript_id, mv.alignment_difference, mv.status, mv.first_release_mapping_history_id, mv.uniprot_acc, mv.uniprot_tax_id, mv.sequence_version, mv.upi, mv.md5, mv.canonical_uniprot_id, mv.ensembl_derived, mv.alias, mv.entry_type, mv.gene_symbol_up, mv.chromosome_line, mv.length, mv.protein_existence_id, mv.release_version, mv.gene_id, mv.enst_id, mv.enst_version, mv.ccds_id, mv.uniparc_accession, mv.biotype, mv.deleted, mv.seq_region_end, mv.seq_region_start, mv.supporting_evidence, mv."select", mv.ensp_id, mv.ensp_len, mv.source, mv.mapping_history_id, mv.release_mapping_history_id, mv.entry_version, mv.sp_ensembl_mapping_type, mv.grouping_id, mv.ensg_id, mv.id, mv.chromosome, mv.region_accession, mv.gene_name, mv.gene_symbol_eg, mv.gene_accession, mv.time_mapped, mv.uniprot_release, mv.ensembl_release, mv.seq_region_strand, mv.uniprot_mapping_status, mv.gene_accession_up
  • Index Cond: (mv.release_mapping_history_id = 216)
18. 0.000 0.000 ↓ 0.0

Hash Join (cost=11,960.85..28,816.37 rows=15,517 width=111) (actual rows= loops=)

  • Output: ue.uniprot_id, ue.uniprot_acc, ue.uniprot_tax_id, ue.sequence_version, ue.upi, ue.md5, ue.canonical_uniprot_id, ue.ensembl_derived, ue.alias, ue.entry_type, ue.gene_symbol, ue.chromosome_line, ue.length, ue.protein_existence_id, ue.status
  • Hash Cond: (("ANY_subquery".uniprot_acc)::text = (ue.uniprot_acc)::text)
19. 0.000 0.000 ↓ 0.0

Subquery Scan on ANY_subquery (cost=5,980.43..22,385.21 rows=39,409 width=78) (actual rows= loops=)

  • Output: "ANY_subquery".uniprot_acc
20. 0.000 0.000 ↓ 0.0

HashSetOp Except (cost=5,980.43..21,991.12 rows=39,409 width=82) (actual rows= loops=)

  • Output: "*SELECT* 1".uniprot_acc, (0)
21. 0.000 0.000 ↓ 0.0

Append (cost=5,980.43..21,892.60 rows=39,410 width=82) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=5,980.43..21,886.21 rows=39,409 width=11) (actual rows= loops=)

  • Output: "*SELECT* 1".uniprot_acc, 0
23. 0.000 0.000 ↓ 0.0

Hash Join (cost=5,980.43..21,492.12 rows=39,409 width=7) (actual rows= loops=)

  • Output: ue_1.uniprot_acc
  • Inner Unique: true
  • Hash Cond: (ueh_1.uniprot_id = ue_1.uniprot_id)
24. 0.000 0.000 ↓ 0.0

Seq Scan on dev.uniprot_entry_history ueh_1 (cost=0.00..14,244.96 rows=100,336 width=8) (actual rows= loops=)

  • Output: ueh_1.release_version, ueh_1.uniprot_id, ueh_1.grouping_id
  • Filter: ((ueh_1.grouping_id IS NULL) AND ((ueh_1.release_version)::text = '2019_09'::text))
25. 0.000 0.000 ↓ 0.0

Hash (cost=5,163.33..5,163.33 rows=65,368 width=15) (actual rows= loops=)

  • Output: ue_1.uniprot_acc, ue_1.uniprot_id
26. 0.000 0.000 ↓ 0.0

Seq Scan on dev.uniprot_entry ue_1 (cost=0.00..5,163.33 rows=65,368 width=15) (actual rows= loops=)

  • Output: ue_1.uniprot_acc, ue_1.uniprot_id
  • Filter: (ue_1.uniprot_tax_id = 10090)
27. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=1.69..6.39 rows=1 width=11) (actual rows= loops=)

  • Output: "*SELECT* 2".uniprot_acc, 1
28. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.69..6.38 rows=1 width=7) (actual rows= loops=)

  • Output: ue_2.uniprot_acc
  • Inner Unique: true
  • Join Filter: (ue_2.uniprot_id = ueh_2.uniprot_id)
29. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.26..5.86 rows=1 width=23) (actual rows= loops=)

  • Output: ue_2.uniprot_acc, ue_2.uniprot_id, m.uniprot_id
  • Inner Unique: true
30. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..5.39 rows=1 width=8) (actual rows= loops=)

  • Output: m.uniprot_id
  • Inner Unique: true
31. 0.000 0.000 ↓ 0.0

Index Scan using mapping_history_release_mapping_history_id_idx on dev.mapping_history mh (cost=0.42..1.96 rows=1 width=8) (actual rows= loops=)

  • Output: mh.mapping_history_id, mh.release_mapping_history_id, mh.sequence_version, mh.entry_type, mh.entry_version, mh.enst_version, mh.mapping_id, mh.sp_ensembl_mapping_type, mh.grouping_id
  • Index Cond: (mh.release_mapping_history_id = 216)
32. 0.000 0.000 ↓ 0.0

Index Scan using ensembl_uniprot_pk on dev.mapping m (cost=0.42..3.44 rows=1 width=16) (actual rows= loops=)

  • Output: m.mapping_id, m.uniprot_id, m.transcript_id, m.alignment_difference, m.status, m.first_release_mapping_history_id
  • Index Cond: (m.mapping_id = mh.mapping_id)
33. 0.000 0.000 ↓ 0.0

Index Scan using uniprot_entry_pk on dev.uniprot_entry ue_2 (cost=0.42..0.46 rows=1 width=15) (actual rows= loops=)

  • Output: ue_2.uniprot_id, ue_2.uniprot_acc, ue_2.uniprot_tax_id, ue_2.userstamp, ue_2."timestamp", ue_2.sequence_version, ue_2.upi, ue_2.md5, ue_2.canonical_uniprot_id, ue_2.ensembl_derived, ue_2.alias, ue_2.entry_type, ue_2.gene_symbol, ue_2.chromosome_line, ue_2.length, ue_2.protein_existence_id, ue_2.gene_accession, ue_2.status
  • Index Cond: (ue_2.uniprot_id = m.uniprot_id)
  • Filter: (ue_2.uniprot_tax_id = 10090)
34. 0.000 0.000 ↓ 0.0

Index Only Scan using uniprot_entry_history_pk on dev.uniprot_entry_history ueh_2 (cost=0.42..0.51 rows=1 width=8) (actual rows= loops=)

  • Output: ueh_2.uniprot_id, ueh_2.release_version
  • Index Cond: ((ueh_2.uniprot_id = m.uniprot_id) AND (ueh_2.release_version = '2019_09'::text))
35. 0.000 0.000 ↓ 0.0

Hash (cost=5,163.33..5,163.33 rows=65,368 width=111) (actual rows= loops=)

  • Output: ue.uniprot_id, ue.uniprot_acc, ue.uniprot_tax_id, ue.sequence_version, ue.upi, ue.md5, ue.canonical_uniprot_id, ue.ensembl_derived, ue.alias, ue.entry_type, ue.gene_symbol, ue.chromosome_line, ue.length, ue.protein_existence_id, ue.status
36. 0.000 0.000 ↓ 0.0

Seq Scan on dev.uniprot_entry ue (cost=0.00..5,163.33 rows=65,368 width=111) (actual rows= loops=)

  • Output: ue.uniprot_id, ue.uniprot_acc, ue.uniprot_tax_id, ue.sequence_version, ue.upi, ue.md5, ue.canonical_uniprot_id, ue.ensembl_derived, ue.alias, ue.entry_type, ue.gene_symbol, ue.chromosome_line, ue.length, ue.protein_existence_id, ue.status
  • Filter: (ue.uniprot_tax_id = 10090)
37. 0.000 0.000 ↓ 0.0

Index Only Scan using uniprot_entry_history_pk on dev.uniprot_entry_history ueh (cost=0.42..0.66 rows=1 width=16) (actual rows= loops=)

  • Output: ueh.uniprot_id, ueh.release_version
  • Index Cond: ((ueh.uniprot_id = ue.uniprot_id) AND (ueh.release_version = '2019_09'::text))