explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oWcr

Settings
# exclusive inclusive rows x rows loops node
1. 0.087 1,930.422 ↑ 1.0 1 1

Aggregate (cost=541,379.10..541,379.11 rows=1 width=8) (actual time=1,930.422..1,930.422 rows=1 loops=1)

  • Output: count(DISTINCT disease_details.d_id)
  • Buffers: shared hit=74036 read=292468
  • I/O Timings: read=559.762
2. 0.031 1,930.335 ↑ 6.8 57 1

Hash Join (cost=13,639.96..541,378.13 rows=390 width=4) (actual time=696.404..1,930.335 rows=57 loops=1)

  • Output: disease_details.d_id
  • Inner Unique: true
  • Hash Cond: (keywords_gd_events.k_gd_id = keywords_gd.k_gd_id)
  • Buffers: shared hit=74029 read=292468
  • I/O Timings: read=559.762
3. 0.023 1,930.255 ↑ 6.8 57 1

Nested Loop (cost=13,632.70..541,369.77 rows=390 width=8) (actual time=696.339..1,930.255 rows=57 loops=1)

  • Output: disease_details.d_id, keywords_gd_events.k_gd_id
  • Buffers: shared hit=74023 read=292468
  • I/O Timings: read=559.762
4. 0.026 1,930.139 ↓ 1.6 31 1

Hash Join (cost=13,632.13..541,089.46 rows=20 width=16) (actual time=696.321..1,930.139 rows=31 loops=1)

  • Output: disease_details.d_id, gene_disease_events.pmid, pubmed_2020.pmid
  • Inner Unique: true
  • Hash Cond: (gene_drug_status.gds_id = gene_drug_status_details.gds_id)
  • Buffers: shared hit=73876 read=292468
  • I/O Timings: read=559.762
5. 0.032 1,930.107 ↓ 1.6 31 1

Nested Loop (cost=13,631.09..541,088.26 rows=20 width=20) (actual time=696.299..1,930.107 rows=31 loops=1)

  • Output: gene_drug_status.gds_id, disease_details.d_id, gene_disease_events.pmid, pubmed_2020.pmid
  • Buffers: shared hit=73875 read=292468
  • I/O Timings: read=559.762
6. 0.000 1,930.013 ↓ 1.6 31 1

Nested Loop (cost=13,630.80..541,058.51 rows=20 width=24) (actual time=696.282..1,930.013 rows=31 loops=1)

  • Output: gene_details.g_id, gene_disease_details.g_id, disease_details.d_id, gene_disease_events.pmid, pubmed_2020.pmid
  • Inner Unique: true
  • Buffers: shared hit=73782 read=292468
  • I/O Timings: read=559.762
7. 8.129 1,935.110 ↓ 1.6 31 1

Gather (cost=13,630.51..541,028.96 rows=20 width=20) (actual time=696.265..1,935.110 rows=31 loops=1)

  • Output: disease_details.d_id, gene_disease_details.g_id, gene_disease_events.pmid, pubmed_2020.pmid
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=73689 read=292468
  • I/O Timings: read=559.762
8. 2.153 1,926.981 ↓ 1.2 10 3 / 3

Nested Loop (cost=12,630.51..540,026.96 rows=8 width=20) (actual time=693.691..1,926.981 rows=10 loops=3)

  • Output: disease_details.d_id, gene_disease_details.g_id, gene_disease_events.pmid, pubmed_2020.pmid
  • Buffers: shared hit=73689 read=292468
  • I/O Timings: read=559.762
  • Worker 0: actual time=693.422..1925.772 rows=8 loops=1
  • Buffers: shared hit=22020 read=97410
  • I/O Timings: read=187.450
  • Worker 1: actual time=691.997..1925.946 rows=8 loops=1
  • Buffers: shared hit=27248 read=97276
  • I/O Timings: read=185.351
9. 542.148 1,908.783 ↓ 2.7 4,011 3 / 3

Hash Join (cost=12,629.95..537,256.92 rows=1,507 width=16) (actual time=18.797..1,908.783 rows=4,011 loops=3)

  • Output: disease_details.d_id, gene_disease_details.g_id, gene_disease_events.pmid
  • Hash Cond: (gene_disease_events.gd_id = gene_disease_details.gd_id)
  • Buffers: shared hit=10636 read=292468
  • I/O Timings: read=559.762
  • Worker 0: actual time=20.737..1909.345 rows=3435 loops=1
  • Buffers: shared hit=3565 read=97410
  • I/O Timings: read=187.450
  • Worker 1: actual time=22.778..1906.222 rows=4533 loops=1
  • Buffers: shared hit=3545 read=97276
  • I/O Timings: read=185.351
10. 1,353.670 1,353.670 ↑ 1.2 6,851,990 3 / 3

Parallel Seq Scan on public.gene_disease_events (cost=0.00..492,554.38 rows=8,548,673 width=16) (actual time=0.028..1,353.670 rows=6,851,990 loops=3)

  • Output: gene_disease_events.gd_id, gene_disease_events.pmid, gene_disease_events.pubmed_sentence_id, gene_disease_events.created_at, gene_disease_events.sentence_status
  • Filter: (gene_disease_events.sentence_status = 1)
  • Rows Removed by Filter: 5680193
  • Buffers: shared hit=4271 read=292468
  • I/O Timings: read=559.762
  • Worker 0: actual time=0.027..1354.542 rows=6852135 loops=1
  • Buffers: shared hit=1429 read=97410
  • I/O Timings: read=187.450
  • Worker 1: actual time=0.038..1348.692 rows=6820786 loops=1
  • Buffers: shared hit=1409 read=97276
  • I/O Timings: read=185.351
11. 3.268 12.965 ↑ 1.0 19,177 3 / 3

Hash (cost=12,390.24..12,390.24 rows=19,177 width=16) (actual time=12.965..12.965 rows=19,177 loops=3)

  • Output: disease_details.d_id, gene_disease_details.g_id, gene_disease_details.gd_id
  • Buckets: 32768 Batches: 1 Memory Usage: 1155kB
  • Buffers: shared hit=6278
  • Worker 0: actual time=13.154..13.154 rows=19177 loops=1
  • Buffers: shared hit=2094
  • Worker 1: actual time=13.316..13.317 rows=19177 loops=1
  • Buffers: shared hit=2094
12. 2.171 9.697 ↑ 1.0 19,177 3 / 3

Nested Loop (cost=1.27..12,390.24 rows=19,177 width=16) (actual time=0.121..9.697 rows=19,177 loops=3)

  • Output: disease_details.d_id, gene_disease_details.g_id, gene_disease_details.gd_id
  • Buffers: shared hit=6278
  • Worker 0: actual time=0.142..9.912 rows=19177 loops=1
  • Buffers: shared hit=2094
  • Worker 1: actual time=0.144..10.068 rows=19177 loops=1
  • Buffers: shared hit=2094
13. 0.001 0.082 ↑ 1.0 1 3 / 3

Nested Loop (cost=0.70..5.79 rows=1 width=8) (actual time=0.080..0.082 rows=1 loops=3)

  • Output: disease_details.d_id, ta_disease.d_id
  • Inner Unique: true
  • Buffers: shared hit=30
  • Worker 0: actual time=0.095..0.097 rows=1 loops=1
  • Buffers: shared hit=11
  • Worker 1: actual time=0.095..0.098 rows=1 loops=1
  • Buffers: shared hit=11
14. 0.003 0.060 ↑ 1.0 1 3 / 3

Nested Loop (cost=0.56..5.01 rows=1 width=12) (actual time=0.058..0.060 rows=1 loops=3)

  • Output: disease_details.d_id, ta_disease.ta_id, ta_disease.d_id
  • Buffers: shared hit=22
  • Worker 0: actual time=0.067..0.069 rows=1 loops=1
  • Buffers: shared hit=8
  • Worker 1: actual time=0.068..0.070 rows=1 loops=1
  • Buffers: shared hit=8
15. 0.034 0.034 ↑ 1.0 1 3 / 3

Index Scan using disease_details_disease_name_index on public.disease_details (cost=0.28..2.50 rows=1 width=4) (actual time=0.033..0.034 rows=1 loops=3)

  • Output: disease_details.d_id, disease_details.disease_name, disease_details.company, disease_details.deleted, disease_details.unmet_need, disease_details.terms_for_ei
  • Index Cond: ((disease_details.disease_name)::text = 'Nash'::text)
  • Buffers: shared hit=11
  • Worker 0: actual time=0.039..0.039 rows=1 loops=1
  • Buffers: shared hit=4
  • Worker 1: actual time=0.039..0.040 rows=1 loops=1
  • Buffers: shared hit=4
16. 0.023 0.023 ↑ 1.0 1 3 / 3

Index Scan using d_idxx on public.ta_disease (cost=0.28..2.50 rows=1 width=8) (actual time=0.022..0.023 rows=1 loops=3)

  • Output: ta_disease.ta_id, ta_disease.d_id, ta_disease.deleted
  • Index Cond: (ta_disease.d_id = disease_details.d_id)
  • Buffers: shared hit=11
  • Worker 0: actual time=0.027..0.027 rows=1 loops=1
  • Buffers: shared hit=4
  • Worker 1: actual time=0.027..0.027 rows=1 loops=1
  • Buffers: shared hit=4
17. 0.021 0.021 ↑ 1.0 1 3 / 3

Index Only Scan using ta_id_index on public.ta_details (cost=0.14..0.70 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=3)

  • Output: ta_details.ta_id
  • Index Cond: (ta_details.ta_id = ta_disease.ta_id)
  • Heap Fetches: 3
  • Buffers: shared hit=8
  • Worker 0: actual time=0.026..0.026 rows=1 loops=1
  • Buffers: shared hit=3
  • Worker 1: actual time=0.027..0.027 rows=1 loops=1
  • Buffers: shared hit=3
18. 7.444 7.444 ↑ 1.0 19,177 3 / 3

Index Scan using gene_disease_details_d_id_index on public.gene_disease_details (cost=0.57..12,192.67 rows=19,177 width=16) (actual time=0.040..7.444 rows=19,177 loops=3)

  • Output: gene_disease_details.gd_id, gene_disease_details.g_id, gene_disease_details.d_id, gene_disease_details.relation_status_pmid, gene_disease_details.relation_status_sentence
  • Index Cond: (gene_disease_details.d_id = ta_disease.d_id)
  • Buffers: shared hit=6248
  • Worker 0: actual time=0.047..7.620 rows=19177 loops=1
  • Buffers: shared hit=2083
  • Worker 1: actual time=0.048..7.770 rows=19177 loops=1
  • Buffers: shared hit=2083
19. 16.045 16.045 ↓ 0.0 0 12,034 / 3

Index Scan using pubmed_2020_pmid_idx on public.pubmed_2020 (cost=0.56..1.83 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=12,034)

  • Output: pubmed_2020.id, pubmed_2020.pmid, pubmed_2020.title, pubmed_2020.abstract, pubmed_2020.pub_date, pubmed_2020.entrez_date, pubmed_2020.xml_date, pubmed_2020.country, pubmed_2020.j_title, pubmed_2020.mesh_terms, pubmed_2020.publication_type, pubmed_2020.keywords, pubmed_2020.textindex_ta, pubmed_2020.textindex_t, pubmed_2020.textindex_a, pubmed_2020.textindex_mesh, pubmed_2020.textindex_kws, pubmed_2020.created_at, pubmed_2020.updated_at
  • Index Cond: (pubmed_2020.pmid = gene_disease_events.pmid)
  • Filter: (((pubmed_2020.entrez_date)::date >= '2020-02-04'::date) AND ((pubmed_2020.entrez_date)::date <= '2020-02-14'::date))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=63053
  • Worker 0: actual time=0.004..0.004 rows=0 loops=3435
  • Buffers: shared hit=18455
  • Worker 1: actual time=0.004..0.004 rows=0 loops=4533
  • Buffers: shared hit=23703
20. 0.093 0.093 ↑ 1.0 1 31

Index Only Scan using gene_details_pkey on public.gene_details (cost=0.29..1.48 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=31)

  • Output: gene_details.g_id
  • Index Cond: (gene_details.g_id = gene_disease_details.g_id)
  • Heap Fetches: 31
  • Buffers: shared hit=93
21. 0.062 0.062 ↑ 1.0 1 31

Index Scan using gene_drug_status_g_id_index on public.gene_drug_status (cost=0.29..1.48 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=31)

  • Output: gene_drug_status.gds_id, gene_drug_status.g_id, gene_drug_status.deleted
  • Index Cond: (gene_drug_status.g_id = gene_disease_details.g_id)
  • Buffers: shared hit=93
22. 0.003 0.006 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=4) (actual time=0.006..0.006 rows=2 loops=1)

  • Output: gene_drug_status_details.gds_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
23. 0.003 0.003 ↑ 1.0 2 1

Seq Scan on public.gene_drug_status_details (cost=0.00..1.02 rows=2 width=4) (actual time=0.003..0.003 rows=2 loops=1)

  • Output: gene_drug_status_details.gds_id
  • Buffers: shared hit=1
24. 0.093 0.093 ↑ 10.0 2 31

Index Scan using keywords_gd_events_pmid on public.keywords_gd_events (cost=0.57..13.82 rows=20 width=12) (actual time=0.003..0.003 rows=2 loops=31)

  • Output: keywords_gd_events.k_gd_id, keywords_gd_events.pmid, keywords_gd_events.pubmed_sentence_id, keywords_gd_events.created_at
  • Index Cond: (keywords_gd_events.pmid = gene_disease_events.pmid)
  • Buffers: shared hit=147
25. 0.013 0.049 ↑ 1.0 56 1

Hash (cost=6.56..6.56 rows=56 width=4) (actual time=0.048..0.049 rows=56 loops=1)

  • Output: keywords_gd.k_gd_id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=6
26. 0.036 0.036 ↑ 1.0 56 1

Seq Scan on public.keywords_gd (cost=0.00..6.56 rows=56 width=4) (actual time=0.012..0.036 rows=56 loops=1)

  • Output: keywords_gd.k_gd_id
  • Buffers: shared hit=6
Planning time : 45.633 ms
Execution time : 1,935.871 ms