explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iLou

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.571 1,081.463 ↑ 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,079.310..1,081.463 rows=7,927 loops=1)

2. 30.987 1,079.892 ↑ 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,079.308..1,079.892 rows=7,927 loops=1)

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

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

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

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

  • Hash Cond: (s.domain = d.id)
5. 163.702 163.702 ↓ 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.071..163.702 rows=134,240 loops=1)

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

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

  • Buckets: 16384 Batches: 1 Memory Usage: 4611kB
7. 89.294 89.294 ↓ 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..89.294 rows=131,143 loops=1)

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