explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5WBH

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 10,319.926 ↓ 10.0 10 1

Limit (cost=4.77..4.77 rows=1 width=64) (actual time=10,319.923..10,319.926 rows=10 loops=1)

2. 20.516 10,319.922 ↓ 10.0 10 1

Sort (cost=4.77..4.77 rows=1 width=64) (actual time=10,319.921..10,319.922 rows=10 loops=1)

  • Sort Key: (sum(d.last_hour_hits)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
3. 43.269 10,299.406 ↓ 114,588.0 114,588 1

GroupAggregate (cost=4.73..4.76 rows=1 width=64) (actual time=10,246.378..10,299.406 rows=114,588 loops=1)

  • Group Key: (split_part(d.name, '.'::text, 1))
4. 260.042 10,256.137 ↓ 114,588.0 114,588 1

Sort (cost=4.73..4.74 rows=1 width=40) (actual time=10,246.366..10,256.137 rows=114,588 loops=1)

  • Sort Key: (split_part(d.name, '.'::text, 1))
  • Sort Method: quicksort Memory: 9780kB
5. 1,632.537 9,996.095 ↓ 114,588.0 114,588 1

Nested Loop (cost=0.84..4.72 rows=1 width=40) (actual time=0.071..9,996.095 rows=114,588 loops=1)

  • Join Filter: (d.tld_id = t.id)
  • Rows Removed by Join Filter: 1365883
6. 3,922.145 3,922.145 ↓ 1,480,471.0 1,480,471 1

Index Scan using domain_last_seen_idx on domain d (cost=0.57..2.32 rows=1 width=31) (actual time=0.030..3,922.145 rows=1,480,471 loops=1)

  • Index Cond: (last_seen = date_trunc('hour'::text, (now() - '01:00:00'::interval)))
  • Filter: ((name !~ '^zz--icann-monitoring\.'::text) AND (last_rescode = 'NXDOMAIN'::response_code_type))
  • Rows Removed by Filter: 98336
7. 4,441.413 4,441.413 ↑ 1.0 1 1,480,471

Index Scan using tld_name_key on tld t (cost=0.27..2.39 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1,480,471)

  • Index Cond: (name = 'link'::text)
Planning time : 1.000 ms
Execution time : 10,319.976 ms