explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RB9P

Settings
# exclusive inclusive rows x rows loops node
1. 0.075 22,401.552 ↑ 1.0 83 1

Sort (cost=931,641.25..931,641.29 rows=83 width=48) (actual time=22,401.543..22,401.552 rows=83 loops=1)

  • Output: tsc.sturges_classid, tsc.limit_inferior, 'drugbank'::text, (count(DISTINCT thst.tb_hit_hitid))
  • Sort Key: tsc.limit_inferior
  • Sort Method: quicksort Memory: 31kB
2. 0.000 22,401.477 ↑ 1.0 83 1

GroupAggregate (cost=921,554.26..931,640.72 rows=83 width=48) (actual time=22,382.470..22,401.477 rows=83 loops=1)

  • Output: tsc.sturges_classid, tsc.limit_inferior, 'drugbank'::text, count(DISTINCT thst.tb_hit_hitid)
  • Group Key: tsc.sturges_classid
3. 0.000 22,443.506 ↑ 4.7 18,570 1

Gather Merge (cost=921,554.26..931,551.92 rows=87,968 width=16) (actual time=22,382.056..22,443.506 rows=18,570 loops=1)

  • Output: tsc.sturges_classid, tsc.limit_inferior, thst.tb_hit_hitid
  • Workers Planned: 2
  • Workers Launched: 2
4. 12.471 66,229.110 ↑ 5.9 6,190 3

Merge Join (cost=920,554.25..921,131.87 rows=36,653 width=16) (actual time=22,069.415..22,076.370 rows=6,190 loops=3)

  • Output: tsc.sturges_classid, tsc.limit_inferior, thst.tb_hit_hitid
  • Merge Cond: (thst.tb_sturges_class_sturges_classid = tsc.sturges_classid)
  • Worker 0: actual time=21922.244..21934.413 rows=10292 loops=1
  • Worker 1: actual time=21905.088..21909.636 rows=3742 loops=1
5. 65.640 66,212.898 ↑ 28.2 6,191 3

Sort (cost=920,544.65..920,631.87 rows=174,434 width=12) (actual time=22,069.201..22,070.966 rows=6,191 loops=3)

  • Output: thst.tb_hit_hitid, thst.tb_sturges_class_sturges_classid
  • Sort Key: thst.tb_sturges_class_sturges_classid
  • Sort Method: quicksort Memory: 1725kB
  • Worker 0: Sort Method: quicksort Memory: 3707kB
  • Worker 1: Sort Method: quicksort Memory: 1558kB
  • Worker 0: actual time=21922.069..21925.203 rows=10293 loops=1
  • Worker 1: actual time=21904.897..21906.067 rows=3743 loops=1
6. 74.328 66,147.258 ↑ 6.3 27,855 3

Nested Loop (cost=0.37..917,507.35 rows=174,434 width=12) (actual time=127.597..22,049.086 rows=27,855 loops=3)

  • Output: thst.tb_hit_hitid, thst.tb_sturges_class_sturges_classid
  • Join Filter: (ths.tb_hit_hitid = thst.tb_hit_hitid)
  • Worker 0: actual time=175.558..21893.224 rows=46314 loops=1
  • Worker 1: actual time=148.841..21890.826 rows=16839 loops=1
7. 24.165 26,918.085 ↑ 9.6 3,095 3

Nested Loop (cost=0.25..238,330.97 rows=29,843 width=16) (actual time=0.289..8,972.695 rows=3,095 loops=3)

  • Output: ths.tb_hit_hitid, tb_hit.hitid
  • Inner Unique: true
  • Worker 0: actual time=0.347..9712.665 rows=5146 loops=1
  • Worker 1: actual time=0.343..8130.397 rows=1871 loops=1
8. 13.845 13.845 ↑ 9.6 3,095 3

Parallel Index Only Scan using tb_hit_source_ak_1 on public.tb_hit_source ths (cost=0.14..2,470.01 rows=29,843 width=8) (actual time=0.132..4.615 rows=3,095 loops=3)

  • Output: ths.source, ths.tb_hit_hitid, ths.uniqueid
  • Index Cond: (ths.source = 'drugbank'::text)
  • Heap Fetches: 0
  • Worker 0: actual time=0.129..6.038 rows=5146 loops=1
  • Worker 1: actual time=0.135..3.590 rows=1871 loops=1
9. 26,880.075 26,880.075 ↑ 1.0 1 9,285

Index Scan using tb_hit_idx_1 on public.tb_hit (cost=0.11..7.90 rows=1 width=8) (actual time=2.895..2.895 rows=1 loops=9,285)

  • Output: tb_hit.hitid, tb_hit.resname, tb_hit.ligandname, tb_hit.inchi, tb_hit.smiles, tb_hit.ion, tb_hit.cristalografyartefact, tb_hit.smilessource, tb_hit.maccs, tb_hit.filename, tb_hit.status, tb_hit.source
  • Index Cond: (tb_hit.hitid = ths.tb_hit_hitid)
  • Filter: ((tb_hit.maccs IS NOT NULL) AND ((tb_hit.smilessource)::text = 'rdkit'::text))
  • Worker 0: actual time=1.884..1.884 rows=1 loops=5146
  • Worker 1: actual time=4.341..4.341 rows=1 loops=1871
10. 39,154.845 39,154.845 ↓ 1.5 9 9,285

Index Scan using tb_hit_sturges_idx_2 on public.tb_hit_sturges thst (cost=0.12..22.70 rows=6 width=12) (actual time=4.134..4.217 rows=9 loops=9,285)

  • Output: thst.hit_sturgesid, thst.tb_hit_hitid, thst.tb_sturges_class_sturges_classid
  • Index Cond: (thst.tb_hit_hitid = tb_hit.hitid)
  • Worker 0: actual time=2.307..2.360 rows=9 loops=5146
  • Worker 1: actual time=7.241..7.346 rows=9 loops=1871
11. 3.261 3.741 ↓ 74.2 6,160 3

Sort (cost=9.60..9.64 rows=83 width=8) (actual time=0.206..1.247 rows=6,160 loops=3)

  • Output: tsc.sturges_classid, tsc.limit_inferior
  • Sort Key: tsc.sturges_classid
  • Sort Method: quicksort Memory: 28kB
  • Worker 0: Sort Method: quicksort Memory: 28kB
  • Worker 1: Sort Method: quicksort Memory: 28kB
  • Worker 0: actual time=0.168..1.976 rows=10220 loops=1
  • Worker 1: actual time=0.182..0.873 rows=3740 loops=1
12. 0.069 0.480 ↑ 1.0 83 3

Nested Loop (cost=0.00..9.07 rows=83 width=8) (actual time=0.063..0.160 rows=83 loops=3)

  • Output: tsc.sturges_classid, tsc.limit_inferior
  • Worker 0: actual time=0.061..0.141 rows=83 loops=1
  • Worker 1: actual time=0.072..0.154 rows=83 loops=1
13. 0.108 0.108 ↑ 1.0 1 3

Seq Scan on public.tb_sturges_parameters tsp (cost=0.00..1.09 rows=1 width=4) (actual time=0.034..0.036 rows=1 loops=3)

  • Output: tsp.sturges_parametersid, tsp.feature, tsp.limit_inferior, tsp.amplitude, tsp.class_amplitude, tsp.name
  • Filter: (tsp.sturges_parametersid = 1)
  • Rows Removed by Filter: 8
  • Worker 0: actual time=0.035..0.037 rows=1 loops=1
  • Worker 1: actual time=0.044..0.046 rows=1 loops=1
14. 0.303 0.303 ↑ 1.0 83 3

Seq Scan on public.tb_sturges_class tsc (cost=0.00..7.15 rows=83 width=12) (actual time=0.026..0.101 rows=83 loops=3)

  • Output: tsc.sturges_classid, tsc.limit_inferior, tsc.tb_sturges_parameters_sturges_parametersid
  • Filter: (tsc.tb_sturges_parameters_sturges_parametersid = 1)
  • Rows Removed by Filter: 312
  • Worker 0: actual time=0.024..0.085 rows=83 loops=1
  • Worker 1: actual time=0.026..0.090 rows=83 loops=1