explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yI12 : q18-p1

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

Sort (cost=3,296,128.57..3,303,630.03 rows=3,000,583 width=75) (actual time=5,889.261..5,889.267 rows=57 loops=1)

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

GroupAggregate (cost=1,954,812.69..2,809,978.85 rows=3,000,583 width=48) (actual time=5,885.174..5,889.153 rows=57 loops=1)

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

Sort (cost=1,954,812.69..1,962,314.15 rows=3,000,583 width=48) (actual time=5,884.973..5,885.016 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.122 5,883.691 ↑ 7,520.3 399 1

Subquery Scan on _t (cost=490,206.84..1,539,692.30 rows=3,000,583 width=48) (actual time=4,702.430..5,883.691 rows=399 loops=1)

5. 16.588 5,883.569 ↑ 7,520.3 399 1

Hash Join (cost=490,206.84..1,509,686.47 rows=3,000,583 width=56) (actual time=4,702.428..5,883.569 rows=399 loops=1)

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

Hash Join (cost=483,160.84..711,236.70 rows=3,000,583 width=33) (actual time=4,637.223..5,803.885 rows=399 loops=1)

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

Seq Scan on lineitem_1 l (cost=0.00..175,565.66 rows=6,001,166 width=13) (actual time=0.004..552.698 rows=6,001,215 loops=1)

8. 0.204 4,630.929 ↑ 13,157.9 57 1

Hash (cost=473,785.84..473,785.84 rows=750,000 width=28) (actual time=4,630.929..4,630.929 rows=57 loops=1)

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

Hash Join (cost=422,643.68..473,785.84 rows=750,000 width=28) (actual time=4,288.338..4,630.725 rows=57 loops=1)

  • Hash Cond: (o.o_orderkey = l2.l_orderkey)
10. 150.399 150.399 ↑ 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.011..150.399 rows=1,500,000 loops=1)

11. 0.292 4,285.090 ↑ 6,426.6 57 1

Hash (cost=418,064.73..418,064.73 rows=366,316 width=4) (actual time=4,285.090..4,285.090 rows=57 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4099kB
12. 2,883.471 4,284.798 ↑ 6,426.6 57 1

GroupAggregate (cost=0.43..414,401.57 rows=366,316 width=13) (actual time=6.727..4,284.798 rows=57 loops=1)

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

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

14. 34.046 63.096 ↑ 1.0 150,000 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 10838kB
15. 29.050 29.050 ↑ 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..29.050 rows=150,000 loops=1)

Planning time : 4.292 ms
Execution time : 5,890.654 ms