explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DSlm

Settings
# exclusive inclusive rows x rows loops node
1. 79.077 4,877.694 ↓ 1.0 34,847 1

Hash Left Join (cost=1.04..5,781,307.64 rows=34,806 width=199) (actual time=0.231..4,877.694 rows=34,847 loops=1)

  • Hash Cond: (t.taxonomy_id = gs.taxonomy_id)
  • Buffers: shared hit=828395
2. 24.570 24.570 ↓ 1.0 34,847 1

Seq Scan on taxonomy t (cost=0.00..1,549.49 rows=34,806 width=153) (actual time=0.009..24.570 rows=34,847 loops=1)

  • Filter: (rank = 'strain'::text)
  • Rows Removed by Filter: 8952
  • Buffers: shared hit=1002
3. 0.003 0.008 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=18) (actual time=0.008..0.008 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
4. 0.005 0.005 ↑ 1.0 2 1

Seq Scan on taxonomy_gramstain gs (cost=0.00..1.02 rows=2 width=18) (actual time=0.004..0.005 rows=2 loops=1)

  • Buffers: shared hit=1
5.          

SubPlan (forHash Left Join)

6. 174.235 4,774.039 ↑ 1.0 1 34,847

Aggregate (cost=166.04..166.05 rows=1 width=32) (actual time=0.137..0.137 rows=1 loops=34,847)

  • Buffers: shared hit=827392
7.          

Initplan (forAggregate)

8. 139.388 139.388 ↑ 1.0 1 34,847

Index Scan using taxonomy_pkey on taxonomy (cost=0.29..8.31 rows=1 width=133) (actual time=0.004..0.004 rows=1 loops=34,847)

  • Index Cond: (taxonomy_id = t.taxonomy_id)
  • Buffers: shared hit=104734
9. 418.164 4,460.416 ↑ 5.5 8 34,847

Bitmap Heap Scan on taxonomy tx (cost=8.62..157.51 rows=44 width=16) (actual time=0.125..0.128 rows=8 loops=34,847)

  • Recheck Cond: (path @> $1)
  • Heap Blocks: exact=131897
  • Buffers: shared hit=827392
10. 4,042.252 4,042.252 ↑ 5.5 8 34,847

Bitmap Index Scan on taxonomy_path_idx (cost=0.00..8.61 rows=44 width=0) (actual time=0.116..0.116 rows=8 loops=34,847)

  • Index Cond: (path @> $1)
  • Buffers: shared hit=590761