explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8ezY

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 8,251.301 ↑ 1.0 10 1

Limit (cost=507.82..507.85 rows=10 width=112) (actual time=8,251.300..8,251.301 rows=10 loops=1)

2. 0.064 8,251.298 ↑ 20.0 10 1

Sort (cost=507.82..508.32 rows=200 width=112) (actual time=8,251.298..8,251.298 rows=10 loops=1)

  • Sort Key: (COALESCE((pg_catalog.sum(remote_scan.numwait))::bigint, '0'::bigint)) DESC, remote_scan.su_name
  • Sort Method: top-N heapsort Memory: 26kB
3. 0.283 8,251.234 ↑ 1.1 180 1

HashAggregate (cost=500.00..503.50 rows=200 width=112) (actual time=8,251.200..8,251.234 rows=180 loops=1)

  • Group Key: remote_scan.su_name
4. 3,240.526 8,250.951 ↑ 76.9 1,301 1

Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100,000 width=112) (actual time=8,250.900..8,250.951 rows=1,301 loops=1)

  • Task Count: 128
  • Tuple data received from nodes: 33 kB
  • Tasks Shown: One of 128
  • -> Task
  • Tuple data received from node: 442 bytes
  • Node: host=worker1 port=5,432 dbname=postgres
5. 0.497 5,010.425 ↑ 588.2 17 1

HashAggregate (cost=708,568.89..708,668.89 rows=10,000 width=34) (actual time=5,010.075..5,010.425 rows=17 loops=1)

  • Group Key: supplier.su_name
6. 0.886 5,009.928 ↑ 26,099.1 18 1

Hash Join (cost=34,267.44..706,219.97 rows=469,783 width=26) (actual time=629.755..5,009.928 rows=18 loops=1)

  • Hash Cond: (mod((stock.s_w_id * stock.s_i_id), '10000'::numeric) = (supplier.su_suppkey)::numeric)
7. 6.379 5,004.455 ↑ 675.8 862 1

Hash Join (cost=33,856.60..653,060.95 rows=582,531 width=11) (actual time=619.899..5,004.455 rows=862 loops=1)

  • Hash Cond: ((l1.ol_w_id = stock.s_w_id) AND (l1.ol_i_id = stock.s_i_id))
8. 772.531 4,400.395 ↑ 783.6 862 1

Merge Anti Join (cost=1.28..615,659.57 rows=675,439 width=16) (actual time=19.218..4,400.395 rows=862 loops=1)

  • Merge Cond: ((l1.ol_w_id = l2.ol_w_id) AND (l1.ol_d_id = l2.ol_d_id) AND (l1.ol_o_id = l2.ol_o_id))
  • Join Filter: (l2.ol_delivery_d > l1.ol_delivery_d)
  • Rows Removed by Join Filter: 10,297
9. 2,597.546 2,597.546 ↑ 1,175.4 862 1

Merge Join (cost=0.85..219,889.71 rows=1,013,159 width=34) (actual time=10.957..2,597.546 rows=862 loops=1)

  • Merge Cond: ((orders.o_w_id = l1.ol_w_id) AND (orders.o_d_id = l1.ol_d_id) AND (orders.o_id = l1.ol_o_id))
  • Join Filter: (l1.ol_delivery_d > orders.o_entry_d)
  • Rows Removed by Join Filter: 3,001,613
  • -> Index Scan using orders_i1_105863 on orders_105863 orders (cost=0.42..11149.42 rows=300,000 width=23) (actual time=0.508..119.844 rows=300,000 l
  • -> Index Scan using order_line_i1_105991 on order_line_105991 l1 (cost=0.43..143930.68 rows=3,002,550 width=29) (actual time=0.044..1272.468 rows=3
10. 1,030.318 1,030.318 ↑ 1.0 2,997,549 1

Materialize (cost=0.43..151,437.05 rows=3,002,550 width=23) (actual time=0.024..1,030.318 rows=2,997,549 loops=1)

  • -> Index Scan using order_line_i1_105991 on order_line_105991 l2 (cost=0.43..143930.68 rows=3,002,550 width=23) (actual time=0.017..695.563 rows=29
11. 597.681 597.681 ↑ 1.0 1,000,000 1

Hash (cost=18,854.37..18,854.37 rows=1,000,063 width=11) (actual time=597.681..597.681 rows=1,000,000 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 50,966kB
  • -> Index Only Scan using stock_i1_105607 on stock_105607 stock (cost=0.42..18854.37 rows=1,000,063 width=11) (actual time=0.041..267.822 rows=1,000,000 loo
  • Heap Fetches: 0
12. 0.093 4.587 ↓ 1.1 180 1

Hash (cost=408.83..408.83 rows=161 width=30) (actual time=4.587..4.587 rows=180 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
13. 2.190 4.494 ↓ 1.1 180 1

Hash Join (cost=2.79..408.83 rows=161 width=30) (actual time=0.077..4.494 rows=180 loops=1)

  • Hash Cond: (supplier.su_nationkey = nation.n_nationkey)
14. 2.270 2.270 ↑ 1.0 10,000 1

Seq Scan on supplier_105094 supplier (cost=0.00..378.00 rows=10,000 width=34) (actual time=0.010..2.270 rows=10,000 loops=1)

15. 0.004 0.034 ↑ 1.0 1 1

Hash (cost=2.77..2.77 rows=1 width=4) (actual time=0.034..0.034 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.030 0.030 ↑ 1.0 1 1

Seq Scan on nation_105093 nation (cost=0.00..2.77 rows=1 width=4) (actual time=0.014..0.030 rows=1 loops=1)

  • Filter: (n_name = 'Germany'::bpchar)
  • Rows Removed by Filter: 61
Planning time : 14.650 ms
Execution time : 8,251.635 ms