explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TP1y

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.030 5,123.646 ↑ 4.8 42 1

Sort (cost=327,743.47..327,743.97 rows=200 width=8) (actual time=5,123.643..5,123.646 rows=42 loops=1)

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

HashAggregate (cost=327,733.83..327,735.83 rows=200 width=8) (actual time=5,123.603..5,123.616 rows=42 loops=1)

  • Group Key: count(orders.o_orderkey)
3. 283.111 5,075.236 ↑ 1.0 150,000 1

GroupAggregate (cost=280,729.67..325,483.83 rows=150,000 width=8) (actual time=3,668.867..5,075.236 rows=150,000 loops=1)

  • Group Key: customer.c_custkey
4. 376.223 4,792.125 ↓ 1.0 1,533,923 1

Merge Left Join (cost=280,729.67..316,484.58 rows=1,499,850 width=8) (actual time=3,668.845..4,792.125 rows=1,533,923 loops=1)

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

Index Only Scan using pkcustomer on customer (cost=0.42..9,134.43 rows=150,000 width=4) (actual time=0.009..86.283 rows=150,000 loops=1)

  • Heap Fetches: 150000
6. 227.295 4,329.619 ↑ 1.0 1,483,918 1

Materialize (cost=280,728.51..288,227.76 rows=1,499,850 width=8) (actual time=3,668.831..4,329.619 rows=1,483,918 loops=1)

7. 2,959.274 4,102.324 ↑ 1.0 1,483,918 1

Sort (cost=280,728.51..284,478.14 rows=1,499,850 width=8) (actual time=3,668.824..4,102.324 rows=1,483,918 loops=1)

  • Sort Key: orders.o_custkey
  • Sort Method: external merge Disk: 26096kB
8. 1,143.050 1,143.050 ↑ 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.020..1,143.050 rows=1,483,918 loops=1)

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