explain.depesz.com

PostgreSQL's explain analyze made readable

Result: p4pi

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 11,119.882 ↑ 1.0 10 1

Limit (cost=506.82..506.85 rows=10 width=136) (actual time=11,119.881..11,119.882 rows=10 loops=1)

2. 0.044 11,119.880 ↑ 20.0 10 1

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

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

HashAggregate (cost=500.00..502.50 rows=200 width=136) (actual time=11,119.813..11,119.836 rows=22 loops=1)

  • Group Key: remote_scan.n_name
4. 7,618.486 11,117.435 ↑ 16.3 6,138 1

Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100,000 width=136) (actual time=11,116.957..11,117.435 rows=6,138 loops=1)

  • Task Count: 288
  • Tuple data received from nodes: 194 kB
  • Tasks Shown: One of 288
  • -> Task
  • Tuple data received from node: 706 bytes
  • Node: host=node2 port=5,432 dbname=postgres
5. 1.824 3,498.949 ↑ 2.8 22 1

HashAggregate (cost=41,141.65..41,142.43 rows=62 width=58) (actual time=3,498.933..3,498.949 rows=22 loops=1)

  • Group Key: nation.n_name
6. 104.951 3,497.125 ↑ 9.1 5,164 1

Merge Join (cost=39,418.31..40,907.49 rows=46,832 width=31) (actual time=3,338.008..3,497.125 rows=5,164 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. 10.337 13.642 ↑ 1.0 9,682 1

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

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

Seq Scan on supplier_106626 supplier (cost=0.00..378.00 rows=10,000 width=8) (actual time=0.009..3.305 rows=10,000 loops=1)

9. 1,098.838 3,378.532 ↓ 5.6 323,592 1

Sort (cost=38,375.85..38,521.02 rows=58,069 width=49) (actual time=3,325.877..3,378.532 rows=323,592 loops=1)

  • Sort Key: nation.n_nationkey, (mod((stock.s_w_id * stock.s_i_id), '10000'::numeric))
  • Sort Method: quicksort Memory: 57,794kB
10. 406.345 2,279.694 ↓ 5.6 323,592 1

Hash Join (cost=25,293.74..33,781.00 rows=58,069 width=49) (actual time=1,827.535..2,279.694 rows=323,592 loops=1)

  • Hash Cond: ((stock.s_w_id = customer.c_w_id) AND (stock.s_i_id = order_line.ol_i_id))
11. 45.881 45.881 ↓ 1.0 300,000 1

Index Only Scan using stock_i1_107779 on stock_107779 stock (cost=0.42..5,657.14 rows=299,981 width=11) (actual time=0.021..45.881 rows=300,000 loops=1)

  • Heap Fetches: 0
12. 174.488 1,827.468 ↓ 5.9 323,592 1

Hash (cost=24,474.73..24,474.73 rows=54,572 width=59) (actual time=1,827.468..1,827.468 rows=323,592 loops=1)

  • Buckets: 524,288 (originally 65536) Batches: 1 (originally 1) Memory Usage: 33,980kB
13. 599.964 1,652.980 ↓ 5.9 323,592 1

Nested Loop (cost=5.31..24,474.73 rows=54,572 width=59) (actual time=1.155..1,652.980 rows=323,592 loops=1)

  • Join Filter: ((customer.c_w_id = order_line.ol_w_id) AND (customer.c_d_id = order_line.ol_d_id))
14. 17.130 1,053.016 ↓ 5.8 32,387 1

Nested Loop (cost=4.89..18,220.93 rows=5,539 width=58) (actual time=1.120..1,053.016 rows=32,387 loops=1)

15. 12.964 747.958 ↓ 5.7 31,992 1

Hash Join (cost=4.47..15,564.21 rows=5,584 width=48) (actual time=0.746..747.958 rows=31,992 loops=1)

  • Hash Cond: (nation.n_regionkey = region.r_regionkey)
16. 84.114 734.921 ↓ 3.2 90,000 1

Hash Join (cost=3.40..15,427.73 rows=27,919 width=52) (actual time=0.666..734.921 rows=90,000 loops=1)

  • Hash Cond: (ascii(substr((customer.c_state)::text, 1, 1)) = nation.n_nationkey)
17. 650.774 650.774 ↑ 1.0 90,000 1

Seq Scan on customer_106627 customer (cost=0.00..15,147.62 rows=90,062 width=18) (actual time=0.623..650.774 rows=90,000 loops=1)

18. 0.016 0.033 ↑ 1.0 62 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
19. 0.017 0.017 ↑ 1.0 62 1

Seq Scan on nation_106625 nation (cost=0.00..2.62 rows=62 width=34) (actual time=0.004..0.017 rows=62 loops=1)

20. 0.003 0.073 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
21. 0.070 0.070 ↑ 1.0 1 1

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

  • Filter: (r_name = 'Europe'::bpchar)
  • Rows Removed by Filter: 4
22. 287.928 287.928 ↑ 1.0 1 31,992

Index Scan using orders_i2_108643 on orders_108643 orders (cost=0.42..0.47 rows=1 width=20) (actual time=0.009..0.009 rows=1 loops=31,992)

  • Index Cond: ((o_w_id = customer.c_w_id) A
Planning time : 71.597 ms
Execution time : 11,120.002 ms