explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uaIH : Optimization for: plan #JhEK

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.017 2,992.446 ↑ 4.8 42 1

Sort (cost=264,583.97..264,584.47 rows=200 width=8) (actual time=2,992.444..2,992.446 rows=42 loops=1)

  • Sort Key: (count(*)), (count(orders.o_orderkey))
  • Sort Method: quicksort Memory: 26kB
2. 29.386 2,992.429 ↑ 4.8 42 1

HashAggregate (cost=264,574.33..264,576.33 rows=200 width=8) (actual time=2,992.422..2,992.429 rows=42 loops=1)

  • Group Key: count(orders.o_orderkey)
3. 185.922 2,963.043 ↑ 1.0 150,000 1

GroupAggregate (cost=219,210.17..262,324.33 rows=150,000 width=8) (actual time=1,836.673..2,963.043 rows=150,000 loops=1)

  • Group Key: customer.c_custkey
4. 247.865 2,777.121 ↓ 1.0 1,533,923 1

Merge Left Join (cost=219,210.17..253,325.08 rows=1,499,850 width=8) (actual time=1,836.657..2,777.121 rows=1,533,923 loops=1)

  • Merge Cond: (customer.c_custkey = orders.o_custkey)
5. 62.093 62.093 ↑ 1.0 150,000 1

Index Only Scan using idx on customer (cost=0.42..7,494.43 rows=150,000 width=4) (actual time=0.008..62.093 rows=150,000 loops=1)

  • Heap Fetches: 150000
6. 172.683 2,467.163 ↑ 1.0 1,483,918 1

Materialize (cost=219,209.01..226,708.26 rows=1,499,850 width=8) (actual time=1,836.645..2,467.163 rows=1,483,918 loops=1)

7. 1,540.111 2,294.480 ↑ 1.0 1,483,918 1

Sort (cost=219,209.01..222,958.64 rows=1,499,850 width=8) (actual time=1,836.642..2,294.480 rows=1,483,918 loops=1)

  • Sort Key: orders.o_custkey
  • Sort Method: external merge Disk: 26056kB
8. 754.369 754.369 ↑ 1.0 1,483,918 1

Seq Scan on orders (cost=0.00..44,845.00 rows=1,499,850 width=8) (actual time=0.019..754.369 rows=1,483,918 loops=1)

  • Filter: ((o_comment)::text !~~ '%special%requests%'::text)
  • Rows Removed by Filter: 16082