explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0JjV

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.004 29,637.044 ↑ 1.0 10 1

Limit (cost=248,222.86..248,222.89 rows=10 width=134) (actual time=29,637.042..29,637.044 rows=10 loops=1)

2. 4.533 29,637.040 ↑ 2,996.4 10 1

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

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

GroupAggregate (cost=246,976.07..247,575.35 rows=29,964 width=134) (actual time=27,452.009..29,632.507 rows=10,000 loops=1)

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

Sort (cost=246,976.07..247,050.98 rows=29,964 width=134) (actual time=27,451.830..29,298.578 rows=535,027 loops=1)

  • Sort Key: nation.n_name, supplier.s_name
  • Sort Method: external merge Disk: 34536kB
5. 246.393 14,013.190 ↓ 17.9 535,027 1

Hash Join (cost=540.26..238,595.11 rows=29,964 width=134) (actual time=23.916..14,013.190 rows=535,027 loops=1)

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

Hash Join (cost=526.43..238,169.28 rows=29,964 width=34) (actual time=23.865..13,766.756 rows=535,027 loops=1)

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

Nested Loop (cost=0.43..236,916.11 rows=30,006 width=8) (actual time=0.060..12,548.129 rows=535,027 loops=1)

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

Seq Scan on orders (cost=0.00..52,345.00 rows=7,500 width=8) (actual time=0.024..2,202.301 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 pklineitem on lineitem (cost=0.43..24.45 rows=16 width=8) (actual time=0.067..0.074 rows=4 loops=133,623)

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

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

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

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

12. 0.006 0.041 ↑ 6.8 25 1

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

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

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