explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Wr3T : Optimization for: plan #MRKY

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.003 12,138.380 ↑ 1.0 10 1

Limit (cost=119,797.01..119,797.03 rows=10 width=134) (actual time=12,138.379..12,138.380 rows=10 loops=1)

2. 2.677 12,138.377 ↑ 2,996.4 10 1

Sort (cost=119,797.01..119,871.92 rows=29,964 width=134) (actual time=12,138.377..12,138.377 rows=10 loops=1)

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

GroupAggregate (cost=118,550.22..119,149.50 rows=29,964 width=134) (actual time=10,378.151..12,135.700 rows=10,000 loops=1)

  • Group Key: nation.n_name, supplier.s_name
4. 7,313.886 11,918.618 ↓ 17.9 535,027 1

Sort (cost=118,550.22..118,625.13 rows=29,964 width=134) (actual time=10,377.866..11,918.618 rows=535,027 loops=1)

  • Sort Key: nation.n_name, supplier.s_name
  • Sort Method: external merge Disk: 34432kB
5. 140.166 4,604.732 ↓ 17.9 535,027 1

Hash Join (cost=449.00..114,271.25 rows=29,964 width=134) (actual time=8.083..4,604.732 rows=535,027 loops=1)

  • Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
6. 620.014 4,464.542 ↓ 17.9 535,027 1

Hash Join (cost=447.43..113,857.68 rows=29,964 width=34) (actual time=8.049..4,464.542 rows=535,027 loops=1)

  • Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
7. 79.479 3,837.225 ↓ 17.8 535,027 1

Nested Loop (cost=0.43..112,660.95 rows=30,006 width=8) (actual time=0.720..3,837.225 rows=535,027 loops=1)

8. 684.417 684.417 ↓ 17.8 133,623 1

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 665kB
11. 4.919 4.919 ↑ 1.0 10,000 1

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

12. 0.006 0.024 ↑ 1.0 25 1

Hash (cost=1.25..1.25 rows=25 width=108) (actual time=0.024..0.024 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
13. 0.018 0.018 ↑ 1.0 25 1

Seq Scan on nation (cost=0.00..1.25 rows=25 width=108) (actual time=0.015..0.018 rows=25 loops=1)