explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WTUy : Optimization for: Optimization for: plan #0JjV; plan #SJet

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.003 29,548.583 ↑ 1.0 10 1

Limit (cost=123,967.70..123,967.73 rows=10 width=134) (actual time=29,548.581..29,548.583 rows=10 loops=1)

2. 4.655 29,548.580 ↑ 2,996.4 10 1

Sort (cost=123,967.70..124,042.61 rows=29,964 width=134) (actual time=29,548.580..29,548.580 rows=10 loops=1)

  • Sort Key: (count(DISTINCT orders.o_custkey))
  • Sort Method: top-N heapsort Memory: 26kB
3. 354.627 29,543.925 ↑ 3.0 10,000 1

GroupAggregate (cost=122,720.91..123,320.19 rows=29,964 width=134) (actual time=27,240.559..29,543.925 rows=10,000 loops=1)

  • Group Key: nation.n_name, supplier.s_name
4. 15,456.926 29,189.298 ↓ 17.9 535,027 1

Sort (cost=122,720.91..122,795.82 rows=29,964 width=134) (actual time=27,240.294..29,189.298 rows=535,027 loops=1)

  • Sort Key: nation.n_name, supplier.s_name
  • Sort Method: external merge Disk: 34536kB
5. 246.222 13,732.372 ↓ 17.9 535,027 1

Hash Join (cost=540.26..114,339.95 rows=29,964 width=134) (actual time=14.291..13,732.372 rows=535,027 loops=1)

  • Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
6. 1,103.686 13,486.127 ↓ 17.9 535,027 1

Hash Join (cost=526.43..113,914.12 rows=29,964 width=34) (actual time=14.258..13,486.127 rows=535,027 loops=1)

  • Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
7. 396.797 12,371.462 ↓ 17.8 535,027 1

Nested Loop (cost=0.43..112,660.95 rows=30,006 width=8) (actual time=0.019..12,371.462 rows=535,027 loops=1)

8. 2,086.563 2,086.563 ↓ 17.8 133,623 1

Seq Scan on orders (cost=0.00..52,345.00 rows=7,500 width=8) (actual time=0.011..2,086.563 rows=133,623 loops=1)

  • Filter: (date_part('year'::text, (o_orderdate)::timestamp without time zone) = 1998::double precision)
  • Rows Removed by Filter: 1366377
9. 9,888.102 9,888.102 ↑ 4.0 4 133,623

Index Scan using idx on lineitem (cost=0.43..7.88 rows=16 width=8) (actual time=0.066..0.074 rows=4 loops=133,623)

  • Index Cond: (l_orderkey = orders.o_orderkey)
10. 7.992 10.979 ↑ 1.0 10,000 1

Hash (cost=322.00..322.00 rows=10,000 width=34) (actual time=10.979..10.979 rows=10,000 loops=1)

  • Buckets: 1024 Batches: 16 Memory Usage: 45kB
11. 2.987 2.987 ↑ 1.0 10,000 1

Seq Scan on supplier (cost=0.00..322.00 rows=10,000 width=34) (actual time=0.002..2.987 rows=10,000 loops=1)

12. 0.006 0.023 ↑ 6.8 25 1

Hash (cost=11.70..11.70 rows=170 width=108) (actual time=0.023..0.023 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
13. 0.017 0.017 ↑ 6.8 25 1

Seq Scan on nation (cost=0.00..11.70 rows=170 width=108) (actual time=0.014..0.017 rows=25 loops=1)