explain.depesz.com

PostgreSQL's explain analyze made readable

Result: P8NN : q18-p1

Settings
# exclusive inclusive rows x rows loops node
1. 0.112 5,816.164 ↑ 52,641.8 57 1

Sort (cost=3,298,014.28..3,305,515.74 rows=3,000,581 width=75) (actual time=5,816.162..5,816.164 rows=57 loops=1)

  • Sort Key: _t.o_totalprice DESC, _t.o_orderdate
  • Sort Method: quicksort Memory: 32kB
2. 4.151 5,816.052 ↑ 52,641.8 57 1

GroupAggregate (cost=1,956,699.22..2,811,864.81 rows=3,000,581 width=48) (actual time=5,812.042..5,816.052 rows=57 loops=1)

  • Group Key: _t.c_name, _t.c_custkey, _t.o_orderkey, _t.o_orderdate, _t.o_totalprice
3. 1.322 5,811.901 ↑ 7,520.3 399 1

Sort (cost=1,956,699.22..1,964,200.68 rows=3,000,581 width=48) (actual time=5,811.853..5,811.901 rows=399 loops=1)

  • Sort Key: _t.c_name, _t.c_custkey, _t.o_orderkey, _t.o_orderdate, _t.o_totalprice
  • Sort Method: quicksort Memory: 81kB
4. 0.149 5,810.579 ↑ 7,520.3 399 1

Subquery Scan on _t (cost=492,094.23..1,541,579.06 rows=3,000,581 width=48) (actual time=4,639.718..5,810.579 rows=399 loops=1)

5. 16.196 5,810.430 ↑ 7,520.3 399 1

Hash Join (cost=492,094.23..1,511,573.25 rows=3,000,581 width=56) (actual time=4,639.714..5,810.430 rows=399 loops=1)

  • Hash Cond: (o.o_custkey = c.c_custkey)
6. 609.706 5,733.024 ↑ 7,520.3 399 1

Hash Join (cost=485,048.23..713,124.01 rows=3,000,581 width=33) (actual time=4,576.534..5,733.024 rows=399 loops=1)

  • Hash Cond: (l.l_orderkey = o.o_orderkey)
7. 553.228 553.228 ↓ 1.0 6,001,215 1

Seq Scan on lineitem_1 l (cost=0.00..175,565.62 rows=6,001,162 width=13) (actual time=0.003..553.228 rows=6,001,215 loops=1)

8. 0.205 4,570.090 ↑ 13,157.9 57 1

Hash (cost=475,673.23..475,673.23 rows=750,000 width=28) (actual time=4,570.090..4,570.090 rows=57 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 8196kB
9. 198.084 4,569.885 ↑ 13,157.9 57 1

Hash Join (cost=424,468.15..475,673.23 rows=750,000 width=28) (actual time=4,223.147..4,569.885 rows=57 loops=1)

  • Hash Cond: (o.o_orderkey = l2.l_orderkey)
10. 151.990 151.990 ↑ 1.0 1,500,000 1

Seq Scan on orders_1 o (cost=0.00..41,854.00 rows=1,500,000 width=24) (actual time=0.009..151.990 rows=1,500,000 loops=1)

11. 0.284 4,219.811 ↑ 6,537.0 57 1

Hash (cost=419,810.55..419,810.55 rows=372,608 width=4) (actual time=4,219.811..4,219.811 rows=57 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4099kB
12. 2,951.158 4,219.527 ↑ 6,537.0 57 1

GroupAggregate (cost=0.43..416,084.47 rows=372,608 width=13) (actual time=6.593..4,219.527 rows=57 loops=1)

  • Group Key: l2.l_orderkey
  • Filter: (sum(l2.l_quantity) > '300'::numeric)
  • Rows Removed by Filter: 1499943
13. 1,268.369 1,268.369 ↓ 1.0 6,001,215 1

Index Scan using lineitem_1_l_orderkey_idx on lineitem_1 l2 (cost=0.43..271,403.11 rows=6,001,162 width=13) (actual time=0.016..1,268.369 rows=6,001,215 loops=1)

14. 33.496 61.210 ↑ 1.0 150,000 1

Hash (cost=5,171.00..5,171.00 rows=150,000 width=27) (actual time=61.210..61.210 rows=150,000 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 10838kB
15. 27.714 27.714 ↑ 1.0 150,000 1

Seq Scan on customer_1 c (cost=0.00..5,171.00 rows=150,000 width=27) (actual time=0.006..27.714 rows=150,000 loops=1)

Planning time : 3.581 ms
Execution time : 5,817.760 ms