explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bV7W : Optimization for: plan #iLou

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.686 1,089.495 ↑ 4,394.2 7,927 1

Unique (cost=4,956,168.93..5,217,416.43 rows=34,833,000 width=38) (actual time=1,087.256..1,089.495 rows=7,927 loops=1)

2. 37.103 1,087.809 ↑ 4,394.2 7,927 1

Sort (cost=4,956,168.93..5,043,251.43 rows=34,833,000 width=38) (actual time=1,087.254..1,087.809 rows=7,927 loops=1)

  • Sort Key: (regexp_split_to_table(split_part(s.params, '||'::text, 2), '\s+'::text)), (count(*))
  • Sort Method: quicksort Memory: 812kB
3. 117.043 1,050.706 ↑ 4,394.2 7,927 1

HashAggregate (cost=363,707.55..538,220.88 rows=34,833,000 width=38) (actual time=1,048.922..1,050.706 rows=7,927 loops=1)

  • Group Key: regexp_split_to_table(split_part(s.params, '||'::text, 2), '\s+'::text)
4. 648.581 933.663 ↑ 98.4 353,977 1

Hash Join (cost=6,366.59..189,542.55 rows=34,833,000 width=38) (actual time=120.584..933.663 rows=353,977 loops=1)

  • Hash Cond: (s.domain = d.id)
5. 164.681 164.681 ↓ 1.1 134,240 1

Index Scan using services_shortname on services s (cost=0.43..7,066.01 rows=127,764 width=42) (actual time=0.072..164.681 rows=134,240 loops=1)

  • Index Cond: ((shortname)::text = 'nserver'::text)
  • Filter: (status = 1)
6. 29.069 120.401 ↓ 1.0 131,143 1

Hash (cost=4,763.35..4,763.35 rows=128,225 width=4) (actual time=120.401..120.401 rows=131,143 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 4611kB
7. 91.332 91.332 ↓ 1.0 131,143 1

Seq Scan on domains d (cost=0.00..4,763.35 rows=128,225 width=4) (actual time=0.011..91.332 rows=131,143 loops=1)

  • Filter: ((occup_id = 2) AND (status = 1))
  • Rows Removed by Filter: 156327
Planning time : 0.624 ms
Execution time : 1,090.063 ms