explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 44uN

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.013 810.912 ↑ 34.0 5 1

Sort (cost=61,772.56..61,772.99 rows=170 width=116) (actual time=810.911..810.912 rows=5 loops=1)

  • Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=126686 read=92566
2. 9.789 810.899 ↑ 34.0 5 1

HashAggregate (cost=61,764.14..61,766.26 rows=170 width=116) (actual time=810.899..810.899 rows=5 loops=1)

  • Group Key: nation.n_name
  • Buffers: shared hit=126686 read=92566
3. 49.526 801.110 ↓ 34.5 7,243 1

Hash Join (cost=6,281.27..61,762.04 rows=210 width=116) (actual time=57.072..801.110 rows=7,243 loops=1)

  • Hash Cond: ((lineitem.l_suppkey = supplier.s_suppkey) AND (customer.c_nationkey = supplier.s_nationkey))
  • Buffers: shared hit=126686 read=92566
4. 42.601 745.905 ↓ 35.0 184,082 1

Nested Loop (cost=5,801.27..61,240.50 rows=5,258 width=136) (actual time=51.350..745.905 rows=184,082 loops=1)

  • Buffers: shared hit=126686 read=92336
5. 77.464 427.256 ↓ 35.0 46,008 1

Hash Join (cost=5,800.84..56,723.88 rows=1,314 width=120) (actual time=51.319..427.256 rows=46,008 loops=1)

  • Hash Cond: (orders.o_custkey = customer.c_custkey)
  • Buffers: shared hit=1 read=31267
6. 298.505 298.505 ↓ 1.0 227,597 1

Seq Scan on orders (cost=0.00..50,072.00 rows=223,440 width=12) (actual time=0.010..298.505 rows=227,597 loops=1)

  • Filter: ((o_orderdate >= '1994-01-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 1272403
  • Buffers: shared hit=1 read=27571
7. 6.659 51.287 ↓ 34.2 30,183 1

Hash (cost=5,789.82..5,789.82 rows=882 width=120) (actual time=51.287..51.287 rows=30,183 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2615kB
  • Buffers: shared read=3696
8. 23.436 44.628 ↓ 34.2 30,183 1

Hash Join (cost=24.50..5,789.82 rows=882 width=120) (actual time=0.044..44.628 rows=30,183 loops=1)

  • Hash Cond: (customer.c_nationkey = nation.n_nationkey)
  • Buffers: shared read=3696
9. 21.162 21.162 ↑ 1.0 150,000 1

Seq Scan on customer (cost=0.00..5,194.00 rows=150,000 width=12) (actual time=0.008..21.162 rows=150,000 loops=1)

  • Buffers: shared read=3694
10. 0.004 0.030 ↓ 5.0 5 1

Hash (cost=24.48..24.48 rows=1 width=108) (actual time=0.030..0.030 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=2
11. 0.011 0.026 ↓ 5.0 5 1

Hash Join (cost=12.14..24.48 rows=1 width=108) (actual time=0.023..0.026 rows=5 loops=1)

  • Hash Cond: (nation.n_regionkey = region.r_regionkey)
  • Buffers: shared read=2
12. 0.006 0.006 ↑ 6.8 25 1

Seq Scan on nation (cost=0.00..11.70 rows=170 width=116) (actual time=0.005..0.006 rows=25 loops=1)

  • Buffers: shared read=1
13. 0.000 0.009 ↑ 1.0 1 1

Hash (cost=12.12..12.12 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=1
14. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on region (cost=0.00..12.12 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=1)

  • Filter: (r_name = 'ASIA'::bpchar)
  • Rows Removed by Filter: 4
  • Buffers: shared read=1
15. 276.048 276.048 ↑ 4.2 4 46,008

Index Scan using lineitem_pkey on lineitem (cost=0.43..3.27 rows=17 width=28) (actual time=0.005..0.006 rows=4 loops=46,008)

  • Index Cond: (l_orderkey = orders.o_orderkey)
  • Buffers: shared hit=126685 read=61069
16. 2.696 5.679 ↑ 1.0 10,000 1

Hash (cost=330.00..330.00 rows=10,000 width=12) (actual time=5.679..5.679 rows=10,000 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 597kB
  • Buffers: shared read=230
17. 2.983 2.983 ↑ 1.0 10,000 1

Seq Scan on supplier (cost=0.00..330.00 rows=10,000 width=12) (actual time=0.023..2.983 rows=10,000 loops=1)

  • Buffers: shared read=230