explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ooC8

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 13,933.893 ↑ 1.0 10 1

Limit (cost=506.82..506.85 rows=10 width=136) (actual time=13,933.892..13,933.893 rows=10 loops=1)

2. 0.042 13,933.890 ↑ 20.0 10 1

Sort (cost=506.82..507.32 rows=200 width=136) (actual time=13,933.890..13,933.890 rows=10 loops=1)

  • Sort Key: (sum(remote_scan.revenue)) DESC
  • Sort Method: top-N heapsort Memory: 26kB
3. 0.650 13,933.848 ↑ 9.1 22 1

HashAggregate (cost=500.00..502.50 rows=200 width=136) (actual time=13,933.843..13,933.848 rows=22 loops=1)

  • Group Key: remote_scan.n_name
4. 5,570.261 13,933.198 ↑ 35.5 2,816 1

Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100,000 width=136) (actual time=13,933.093..13,933.198 rows=2,816 loops=1)

  • Task Count: 128
  • Tuple data received from nodes: 91 kB
  • Tasks Shown: One of 128
  • -> Task
  • Tuple data received from node: 726 bytes
  • Node: host=worker1 port=5,432 dbname=postgres
5. 4.421 8,362.937 ↑ 2.8 22 1

HashAggregate (cost=139,941.33..139,942.10 rows=62 width=58) (actual time=8,362.928..8,362.937 rows=22 loops=1)

  • Group Key: nation.n_name
6. 243.914 8,358.516 ↑ 9.7 16,737 1

Merge Join (cost=134,313.13..139,126.10 rows=163,045 width=31) (actual time=7,983.182..8,358.516 rows=16,737 loops=1)

  • Merge Cond: ((supplier.su_nationkey = nation.n_nationkey) AND (((supplier.su_suppkey)::numeric) = (mod((stock.s_w_id * stock.s_i_id), '10000'::numeric))))
7. 9.153 13.887 ↑ 1.0 9,682 1

Sort (cost=1,042.39..1,067.39 rows=10,000 width=8) (actual time=12.506..13.887 rows=9,682 loops=1)

  • Sort Key: supplier.su_nationkey, ((supplier.su_suppkey)::numeric)
  • Sort Method: quicksort Memory: 853kB
8. 4.734 4.734 ↑ 1.0 10,000 1

Seq Scan on supplier_105094 supplier (cost=0.00..378.00 rows=10,000 width=8) (actual time=0.017..4.734 rows=10,000 loops=1)

9. 2,942.011 8,100.715 ↓ 5.2 1,057,090 1

Sort (cost=133,270.69..133,776.13 rows=202,176 width=49) (actual time=7,970.381..8,100.715 rows=1,057,090 loops=1)

  • Sort Key: nation.n_nationkey, (mod((stock.s_w_id * stock.s_i_id), '10000'::numeric))
  • Sort Method: quicksort Memory: 197,806kB
10. 1,348.804 5,158.704 ↓ 5.2 1,057,090 1

Hash Join (cost=84,580.76..115,453.67 rows=202,176 width=49) (actual time=3,810.937..5,158.704 rows=1,057,090 loops=1)

  • Hash Cond: ((stock.s_w_id = customer.c_w_id) AND (stock.s_i_id = order_line.ol_i_id))
  • -> Index Only Scan using stock_i1_105607 on stock_105607 stock (cost=0.42..18852.31 rows=999,926 width=11) (actual time=0.015..202.714 rows=1,000,000 loop
  • Heap Fetches: 0
11. 534.824 3,809.900 ↓ 5.7 1,057,090 1

Hash (cost=81,796.26..81,796.26 rows=185,605 width=59) (actual time=3,809.900..3,809.900 rows=1,057,090 loops=1)

  • Buckets: 2,097,152 (originally 262144) Batches: 1 (originally 1) Memory Usage: 114,016kB
12. 2,026.787 3,275.076 ↓ 5.7 1,057,090 1

Nested Loop (cost=5.32..81,796.26 rows=185,605 width=59) (actual time=0.388..3,275.076 rows=1,057,090 loops=1)

  • Join Filter: ((customer.c_w_id = order_line.ol_w_id) AND (customer.c_d_id = order_line.ol_d_id))
13. 669.552 1,248.289 ↓ 5.7 105,597 1

Nested Loop (cost=4.89..60,783.59 rows=18,517 width=58) (actual time=0.344..1,248.289 rows=105,597 loops=1)

  • -> Index Scan using orders_i2_105863 on orders_105863 orders (cost=0.42..0.47 rows=1 width=20) (actual time=0.005..0.006 rows=1 loops
14. 39.020 578.737 ↓ 5.7 106,366 1

Hash Join (cost=4.47..51,839.95 rows=18,603 width=48) (actual time=0.082..578.737 rows=106,366 loops=1)

  • Hash Cond: (nation.n_regionkey = region.r_regionkey)
  • Index Con
15. 539.675 539.704 ↓ 3.2 300,000 1

Hash Join (cost=3.40..51,387.76 rows=93,015 width=52) (actual time=0.053..539.704 rows=300,000 loops=1)

  • Hash Cond: (ascii(substr((customer.c_state)::text, 1, 1)) = nation.n_nationkey)
  • -> Seq Scan on customer_105095 customer (cost=0.00..50462.47 rows=300,047 width=18) (actual time=0.010..192.966 rows=30,000
16. 0.010 0.029 ↑ 1.0 62 1

Hash (cost=2.62..2.62 rows=62 width=34) (actual time=0.028..0.029 rows=62 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
17. 0.019 0.019 ↑ 1.0 62 1

Seq Scan on nation_105093 nation (cost=0.00..2.62 rows=62 width=34) (actual time=0.007..0.019 rows=62 loops=1)

18. 0.002 0.013 ↑ 1.0 1 1

Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on region_105092 region (cost=0.00..1.06 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=1)

  • Filter: (r_name = 'Europe'::bpchar)
  • Rows Removed by Filter: 4
Planning time : 33.435 ms
Execution time : 13,934.258 ms