explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ja8j : Optimization for: plan #zYPb

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.016 3,037.027 ↑ 4.8 42 1

Sort (cost=264,583.97..264,584.47 rows=200 width=8) (actual time=3,037.025..3,037.027 rows=42 loops=1)

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

HashAggregate (cost=264,574.33..264,576.33 rows=200 width=8) (actual time=3,037.001..3,037.011 rows=42 loops=1)

  • Group Key: count(orders.o_orderkey)
3. 189.730 3,006.608 ↑ 1.0 150,000 1

GroupAggregate (cost=219,210.17..262,324.33 rows=150,000 width=8) (actual time=1,855.448..3,006.608 rows=150,000 loops=1)

  • Group Key: customer.c_custkey
4. 251.734 2,816.878 ↓ 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,855.430..2,816.878 rows=1,533,923 loops=1)

  • Merge Cond: (customer.c_custkey = orders.o_custkey)
5. 64.153 64.153 ↑ 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..64.153 rows=150,000 loops=1)

  • Heap Fetches: 150000
6. 174.674 2,500.991 ↑ 1.0 1,483,918 1

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

7. 1,567.465 2,326.317 ↑ 1.0 1,483,918 1

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

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

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