explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SJet : Optimization for: plan #0JjV

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.003 19,192.706 ↑ 1.0 10 1

Limit (cost=248,222.86..248,222.89 rows=10 width=134) (actual time=19,192.704..19,192.706 rows=10 loops=1)

2. 4.441 19,192.703 ↑ 2,996.4 10 1

Sort (cost=248,222.86..248,297.77 rows=29,964 width=134) (actual time=19,192.702..19,192.703 rows=10 loops=1)

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

GroupAggregate (cost=246,976.07..247,575.35 rows=29,964 width=134) (actual time=16,960.292..19,188.262 rows=10,000 loops=1)

  • Group Key: nation.n_name, supplier.s_name
4. 15,078.835 18,845.845 ↓ 17.9 535,027 1

Sort (cost=246,976.07..247,050.98 rows=29,964 width=134) (actual time=16,960.027..18,845.845 rows=535,027 loops=1)

  • Sort Key: nation.n_name, supplier.s_name
  • Sort Method: external merge Disk: 34536kB
5. 216.427 3,767.010 ↓ 17.9 535,027 1

Hash Join (cost=540.26..238,595.11 rows=29,964 width=134) (actual time=11.549..3,767.010 rows=535,027 loops=1)

  • Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
6. 821.531 3,550.558 ↓ 17.9 535,027 1

Hash Join (cost=526.43..238,169.28 rows=29,964 width=34) (actual time=11.514..3,550.558 rows=535,027 loops=1)

  • Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
7. 191.214 2,719.917 ↓ 17.8 535,027 1

Nested Loop (cost=0.43..236,916.11 rows=30,006 width=8) (actual time=0.020..2,719.917 rows=535,027 loops=1)

8. 1,058.850 1,058.850 ↓ 17.8 133,623 1

Seq Scan on orders (cost=0.00..52,345.00 rows=7,500 width=8) (actual time=0.013..1,058.850 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. 1,469.853 1,469.853 ↑ 4.0 4 133,623

Index Scan using pklineitem on lineitem (cost=0.43..24.45 rows=16 width=8) (actual time=0.009..0.011 rows=4 loops=133,623)

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

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

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

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

12. 0.006 0.025 ↑ 6.8 25 1

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

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

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