explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2xwX

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 1,520,604.821 ↑ 1.0 10 1

Limit (cost=928,607.08..928,607.10 rows=10 width=134) (actual time=1,520,604.821..1,520,604.821 rows=10 loops=1)

2. 20.212 1,520,604.819 ↑ 25,000.0 10 1

Sort (cost=928,607.08..929,232.08 rows=250,000 width=134) (actual time=1,520,604.819..1,520,604.819 rows=10 loops=1)

  • Sort Key: (count(DISTINCT orders.o_custkey))
  • Sort Method: top-N heapsort Memory: 26kB
3. 15,652.648 1,520,584.607 ↑ 25.0 10,000 1

GroupAggregate (cost=896,752.52..923,204.67 rows=250,000 width=134) (actual time=1,334,644.594..1,520,584.607 rows=10,000 loops=1)

  • Group Key: nation.n_name, supplier.s_name
4. 1,478,559.831 1,504,931.959 ↓ 17.9 42,802,160 1

Sort (cost=896,752.52..902,740.56 rows=2,395,215 width=134) (actual time=1,334,622.313..1,504,931.959 rows=42,802,160 loops=1)

  • Sort Key: nation.n_name, supplier.s_name
  • Sort Method: external merge Disk: 2760128kB
5. 11,749.838 26,372.128 ↓ 17.9 42,802,160 1

Hash Join (cost=39,024.99..315,484.85 rows=2,395,215 width=134) (actual time=951.058..26,372.128 rows=42,802,160 loops=1)

  • Hash Cond: (supplier.s_suppkey = partsupp.ps_suppkey)
6. 178.585 13,687.132 ↓ 17.9 535,027 1

Hash Join (cost=449.00..238,526.41 rows=29,964 width=142) (actual time=15.791..13,687.132 rows=535,027 loops=1)

  • Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
7. 748.173 13,508.521 ↓ 17.9 535,027 1

Hash Join (cost=447.43..238,112.84 rows=29,964 width=42) (actual time=15.752..13,508.521 rows=535,027 loops=1)

  • Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
8. 152.602 12,744.841 ↓ 17.8 535,027 1

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

9. 833.415 833.415 ↓ 17.8 133,623 1

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

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

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

  • Index Cond: (l_orderkey = orders.o_orderkey)
11. 2.492 15.507 ↑ 1.0 10,000 1

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

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

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

13. 0.010 0.026 ↑ 1.0 25 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
14. 0.016 0.016 ↑ 1.0 25 1

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

15. 195.504 935.158 ↑ 1.0 800,000 1

Hash (cost=25,451.00..25,451.00 rows=800,000 width=4) (actual time=935.158..935.158 rows=800,000 loops=1)

  • Buckets: 16384 Batches: 8 Memory Usage: 3730kB
16. 739.654 739.654 ↑ 1.0 800,000 1

Seq Scan on partsupp (cost=0.00..25,451.00 rows=800,000 width=4) (actual time=0.051..739.654 rows=800,000 loops=1)