explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c8uL

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 224,926.719 ↓ 0.0 10 1

Limit (cost=0.00..0.00 rows=0 width=0) (actual time=224,926.693..224,926.719 rows=10 loops=1)

2. 0.677 224,926.699 ↓ 0.0 10 1

Sort (cost=0.00..0.00 rows=0 width=0) (actual time=224,926.690..224,926.699 rows=10 loops=1)

  • Sort Key: remote_scan.n_name, remote_scan.su_name, remote_scan.i_id
  • Sort Method: top-N heapsort Memory: 28kB
3. 101,017.786 224,926.022 ↓ 0.0 600 1

Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=0 width=0) (actual time=224,925.500..224,926.022 rows=600 loops=1)

  • -> Distributed Subplan 19_1
  • Task Count: 60
  • Tasks Shown: One of 60
  • -> Task
  • Node: host=10.192.0.10 port=5432 dbname=pguser
4. 7,360.320 19,698.343 ↓ 0.0 99,980 1

HashAggregate (cost=0.00..0.00 rows=0 width=0) (actual time=19,601.680..19,698.343 rows=99,980 loops=1)

  • Group Key: remote_scan.m_i_id
5. 8,176.611 12,338.023 ↓ 0.0 5,963,900 1

Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=0 width=0) (actual time=6,996.687..12,338.023 rows=5,963,900 loops=1)

  • Task Count: 60
  • Tasks Shown: One of 60
  • -> Task
  • Node: host=10.192.0.10 port=5432 dbname=pguser
6. 400.091 4,161.412 ↓ 1.0 99,930 1

Finalize HashAggregate (cost=457,338.45..458,325.46 rows=98,701 width=38) (actual time=4,075.516..4,161.412 rows=99,930 loops=1)

  • Group Key: stock.s_i_id
7. 361.464 3,761.321 ↓ 1.3 264,322 1

Gather (cost=435,624.23..456,351.44 rows=197,402 width=38) (actual time=3,397.778..3,761.321 rows=264,322 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 340.644 3,399.857 ↑ 1.1 88,107 3 / 3

Partial HashAggregate (cost=434,624.23..435,611.24 rows=98,701 width=38) (actual time=3,310.225..3,399.857 rows=88,107 loops=3)

  • Group Key: stock.s_i_id
9. 818.423 3,059.213 ↑ 35.5 199,383 3 / 3

Merge Join (cost=230,983.10..399,208.39 rows=7,083,167 width=11) (actual time=1,619.221..3,059.213 rows=199,383 loops=3)

  • Merge Cond: ((mod((stock.s_w_id * stock.s_i_id), '10000'::numeric)) = ((supplier.su_suppkey)::numeric))
10. 1,211.996 2,073.935 ↑ 1.2 566,667 3 / 3

Sort (cost=230,434.46..232,205.25 rows=708,317 width=16) (actual time=1,596.111..2,073.935 rows=566,667 loops=3)

  • Sort Key: (mod((stock.s_w_id * stock.s_i_id), '10000'::numeric))
  • Sort Method: quicksort Memory: 60936kB
  • Worker 0: Sort Method: quicksort Memory: 58301kB
  • Worker 1: Sort Method: quicksort Memory: 58239kB
11. 861.939 861.939 ↑ 1.2 566,667 3 / 3

Parallel Seq Scan on stock_102251 stock (cost=0.00..161,607.17 rows=708,317 width=16) (actual time=0.018..861.939 rows=566,667 loops=3)

12. 147.492 166.855 ↓ 99.7 199,351 3 / 3

Sort (cost=548.65..553.65 rows=2,000 width=4) (actual time=22.822..166.855 rows=199,351 loops=3)

  • Sort Key: ((supplier.su_suppkey)::numeric)
  • Sort Method: quicksort Memory: 264kB
  • Worker 0: Sort Method: quicksort Memory: 264kB
  • Worker 1: Sort Method: quicksort Memory: 264kB
13. 11.409 19.363 ↓ 1.8 3,572 3 / 3

Hash Join (cost=4.14..438.99 rows=2,000 width=4) (actual time=0.308..19.363 rows=3,572 loops=3)

  • Hash Cond: (supplier.su_nationkey = nation.n_nationkey)
14. 7.737 7.737 ↑ 1.0 10,000 3 / 3

Seq Scan on supplier_102010 supplier (cost=0.00..378.00 rows=10,000 width=8) (actual time=0.031..7.737 rows=10,000 loops=3)

15. 0.024 0.217 ↓ 1.8 22 3 / 3

Hash (cost=3.99..3.99 rows=12 width=4) (actual time=0.216..0.217 rows=22 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.085 0.193 ↓ 1.8 22 3 / 3

Hash Join (cost=1.07..3.99 rows=12 width=4) (actual time=0.047..0.193 rows=22 loops=3)

  • Hash Cond: (nation.n_regionkey = region.r_regionkey)
17. 0.084 0.084 ↑ 1.0 62 3 / 3

Seq Scan on nation_102009 nation (cost=0.00..2.62 rows=62 width=8) (actual time=0.013..0.084 rows=62 loops=3)

18. 0.006 0.024 ↑ 1.0 1 3 / 3

Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.022..0.024 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.018 0.018 ↑ 1.0 1 3 / 3

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

  • Filter: (r_name ~~ 'Europ%'::text)
  • Rows Removed by Filter: 4
20. 0.037 104,209.893 ↓ 2.0 10 1

Limit (cost=87,366.99..87,367.57 rows=5 width=229) (actual time=104,209.740..104,209.893 rows=10 loops=1)

21. 26,267.803 104,209.856 ↓ 2.0 10 1

Gather Merge (cost=87,366.99..87,367.57 rows=5 width=229) (actual time=104,209.737..104,209.856 rows=10 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
22. 0.153 77,942.053 ↓ 1.6 8 2 / 2

Sort (cost=86,366.98..86,367.00 rows=5 width=229) (actual time=77,942.048..77,942.053 rows=8 loops=2)

  • Sort Key: nation.n_name, supplier.su_name, item.i_id
  • Sort Method: top-N heapsort Memory: 30kB
  • Worker 0: Sort Method: quicksort Memory: 28kB
23. 0.351 77,941.900 ↓ 4.2 21 2 / 2

Hash Join (cost=9,093.23..86,366.93 rows=5 width=229) (actual time=4,396.939..77,941.900 rows=21 loops=2)

  • Hash Cond: (mod((stock.s_w_id * stock.s_i_id), '10000'::numeric) = (supplier.su_suppkey)::numeric)
24. 29.651 77,918.420 ↓ 36.0 36 2 / 2

Nested Loop (cost=8,629.24..85,901.83 rows=1 width=36) (actual time=3,948.693..77,918.420 rows=36 loops=2)

  • Join Filter: (intermediate_result.m_s_quantity = stock.s_quantity)
  • Rows Removed by Join Filter: 12757
25. 87.796 443.727 ↓ 125.3 752 2 / 2

Merge Join (cost=8,628.81..8,919.02 rows=6 width=71) (actual time=283.961..443.727 rows=752 loops=2)

  • Merge Cond: (item.i_id = intermediate_result.m_i_id)
26. 1.591 14.595 ↓ 1.3 753 2 / 2

Sort (cost=3,473.66..3,475.15 rows=594 width=25) (actual time=13.758..14.595 rows=753 loops=2)

  • Sort Key: item.i_id
  • Sort Method: quicksort Memory: 103kB
  • Worker 0: Sort Method: quicksort Memory: 64kB
27. 13.004 13.004 ↓ 1.3 753 2 / 2

Parallel Seq Scan on item_102491 item (cost=0.00..3,446.29 rows=594 width=25) (actual time=0.016..13.004 rows=753 loops=2)

  • Filter: ((i_data)::text ~~ '%b'::text)
  • Rows Removed by Filter: 49247
28. 209.147 341.336 ↓ 1.8 99,892 2 / 2

Sort (cost=5,155.15..5,297.09 rows=56,774 width=46) (actual time=250.191..341.336 rows=99,892 loops=2)

  • Sort Key: intermediate_result.m_i_id
  • Sort Method: quicksort Memory: 7759kB
  • Worker 0: Sort Method: quicksort Memory: 7759kB
29. 132.189 132.189 ↓ 1.8 99,980 2 / 2

Function Scan on read_intermediate_result intermediate_result (cost=0.00..671.98 rows=56,774 width=46) (actual time=49.036..132.189 rows=99,980 loops=2)

30. 77,445.042 77,445.042 ↑ 1.0 17 1,505 / 2

Index Scan using stock_i1_102251 on stock_102251 stock (cost=0.43..12,830.26 rows=17 width=16) (actual time=3.100..102.917 rows=17 loops=1,505)

  • Index Cond: (s_i_id = item.i_id)
31. 3.926 23.129 ↓ 1.8 3,572 2 / 2

Hash (cost=438.99..438.99 rows=2,000 width=204) (actual time=23.128..23.129 rows=3,572 loops=2)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 859kB
32. 11.191 19.203 ↓ 1.8 3,572 2 / 2

Hash Join (cost=4.14..438.99 rows=2,000 width=204) (actual time=0.271..19.203 rows=3,572 loops=2)

  • Hash Cond: (supplier.su_nationkey = nation.n_nationkey)
33. 7.788 7.788 ↑ 1.0 10,000 2 / 2

Seq Scan on supplier_102010 supplier (cost=0.00..378.00 rows=10,000 width=182) (actual time=0.012..7.788 rows=10,000 loops=2)

34. 0.031 0.224 ↓ 1.8 22 2 / 2

Hash (cost=3.99..3.99 rows=12 width=30) (actual time=0.223..0.224 rows=22 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
35. 0.110 0.193 ↓ 1.8 22 2 / 2

Hash Join (cost=1.07..3.99 rows=12 width=30) (actual time=0.042..0.193 rows=22 loops=2)

  • Hash Cond: (nation.n_regionkey = region.r_regionkey)
36. 0.064 0.064 ↑ 1.0 62 2 / 2

Seq Scan on nation_102009 nation (cost=0.00..2.62 rows=62 width=34) (actual time=0.007..0.064 rows=62 loops=2)

37. 0.006 0.019 ↑ 1.0 1 2 / 2

Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.018..0.019 rows=1 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.013 0.013 ↑ 1.0 1 2 / 2

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

  • Filter: (r_name ~~ 'Europ%'::text)
  • Rows Removed by Filter: 4
Planning time : 17.612 ms
Execution time : 104,210.737 ms