explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AxRf : Optimization for: plan #ikmm

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.005 272.978 ↓ 1.7 20 1

Limit (cost=31,686.19..31,687.57 rows=12 width=36) (actual time=269.079..272.978 rows=20 loops=1)

2. 0.000 272.973 ↓ 1.7 20 1

Gather Merge (cost=31,686.19..31,687.57 rows=12 width=36) (actual time=269.077..272.973 rows=20 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
3. 0.200 531.116 ↓ 1.2 14 2

Sort (cost=30,686.18..30,686.21 rows=12 width=36) (actual time=265.557..265.558 rows=14 loops=2)

  • Sort Key: (COALESCE(NULLIF(btrim((tt.name)::text), ''::text), (t.name_en)::text))
  • Sort Method: top-N heapsort Memory: 27kB
  • Worker 0: Sort Method: top-N heapsort Memory: 27kB
4. 291.302 530.916 ↓ 4.8 58 2

Parallel Hash Left Join (cost=23,559.31..30,685.97 rows=12 width=36) (actual time=141.970..265.458 rows=58 loops=2)

  • Hash Cond: (t.id = tt.term_id)
  • Filter: ((lower((tt.name)::text) ~~ '%abc%'::text) OR (((lower((tt.name)::text) = ''::text) IS NOT FALSE) AND (lower((t.name_en)::text) ~~ '%abc%'::text)))
  • Rows Removed by Filter: 99942
5. 29.816 29.816 ↑ 1.2 100,000 2

Parallel Seq Scan on terms t (cost=0.00..2,884.47 rows=117,647 width=25) (actual time=0.011..14.908 rows=100,000 loops=2)

6. 44.068 209.798 ↓ 1.2 100,000 2

Parallel Hash (cost=21,968.53..21,968.53 rows=82,222 width=29) (actual time=104.898..104.899 rows=100,000 loops=2)

  • Buckets: 65536 Batches: 4 Memory Usage: 3680kB
7. 147.733 165.730 ↓ 1.2 100,000 2

Parallel Bitmap Heap Scan on term_translations tt (cost=4,273.76..21,968.53 rows=82,222 width=29) (actual time=17.113..82.865 rows=100,000 loops=2)

  • Recheck Cond: ((language)::text = 'GB'::text)
  • Heap Blocks: exact=8726
8. 17.997 17.997 ↓ 1.0 200,000 1

Bitmap Index Scan on term_translations_language_idx (cost=0.00..4,224.42 rows=197,333 width=0) (actual time=17.997..17.997 rows=200,000 loops=1)

  • Index Cond: ((language)::text = 'GB'::text)
Planning time : 0.490 ms
Execution time : 273.013 ms