explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NqFc

Settings
# exclusive inclusive rows x rows loops node
1. 0.106 187,294.049 ↑ 1.0 10 1

Limit (cost=164,297.72..164,298.87 rows=10 width=230) (actual time=187,293.786..187,294.049 rows=10 loops=1)

2. 34,439.689 187,293.943 ↑ 1.0 10 1

Gather Merge (cost=164,297.72..164,298.87 rows=10 width=230) (actual time=187,293.781..187,293.943 rows=10 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
3. 0.317 152,854.254 ↑ 1.0 10 2 / 2

Sort (cost=163,297.71..163,297.73 rows=10 width=230) (actual time=152,854.236..152,854.254 rows=10 loops=2)

  • Sort Key: nation.n_name, supplier.su_name, item.i_id
  • Sort Method: top-N heapsort Memory: 29kB
  • Worker 0: Sort Method: quicksort Memory: 28kB
4. 0.801 152,853.937 ↓ 2.8 28 2 / 2

Hash Join (cost=9,125.54..163,297.54 rows=10 width=230) (actual time=4,985.985..152,853.937 rows=28 loops=2)

  • Hash Cond: (mod((stock.s_w_id * stock.s_i_id), '10000'::numeric) = (supplier.su_suppkey)::numeric)
5. 152,023.118 152,808.289 ↓ 63.0 63 2 / 2

Nested Loop (cost=8,661.55..162,832.45 rows=1 width=37) (actual time=2,868.525..152,808.289 rows=63 loops=2)

  • Join Filter: (intermediate_result.m_s_quantity = stock.s_quantity)
  • Rows Removed by Join Filter: 13324
  • -> Index Scan using stock_i1_102308 on stock_102308 stock (cost=0.43..12822.69 rows=17 width=16) (actual time=5.439..192.981 r
6. 198.407 785.171 ↓ 65.7 788 2 / 2

Merge Join (cost=8,661.12..8,957.67 rows=12 width=72) (actual time=432.330..785.171 rows=788 loops=2)

  • Merge Cond: (item.i_id = intermediate_result.m_i_id)
  • Index Cond: (s_i_id = item.i_id)
7. 19.183 19.183 ↑ 1.5 788 2 / 2

Sort (cost=3,505.97..3,508.94 rows=1,188 width=26) (actual time=17.207..19.183 rows=788 loops=2)

  • Sort Key: item.i_id
  • Sort Method: quicksort Memory: 132kB
  • Worker 0: Sort Method: quicksort Memory: 65kB
  • -> Parallel Seq Scan on item_102491 item (cost=0.00..3445.29 rows=1188 width=26) (actual time=0.020..15.800 rows=7
  • Filter: ((i_data)::text ~~ '%b'::text)
  • Rows Removed by Filter: 49212
8. 567.581 567.581 ↓ 1.8 99,874 2 / 2

Sort (cost=5,155.15..5,297.09 rows=56,774 width=46) (actual time=379.821..567.581 rows=99,874 loops=2)

  • Sort Key: intermediate_result.m_i_id
  • Sort Method: quicksort Memory: 7759kB
  • Worker 0: Sort Method: quicksort Memory: 7759kB
  • -> Function Scan on read_intermediate_result intermediate_result (cost=0.00..671.98 rows=56774 width=46) (actual t
9. 6.996 44.847 ↓ 1.8 3,572 2 / 2

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

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 859kB
10. 37.497 37.851 ↓ 1.8 3,572 2 / 2

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

  • Hash Cond: (supplier.su_nationkey = nation.n_nationkey)
  • -> Seq Scan on supplier_102010 supplier (cost=0.00..378.00 rows=10000 width=182) (actual time=0.013..15.562 rows=10000 l
11. 0.038 0.354 ↓ 1.8 22 2 / 2

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
12. 0.290 0.316 ↓ 1.8 22 2 / 2

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

  • Hash Cond: (nation.n_regionkey = region.r_regionkey)
  • -> Seq Scan on nation_102009 nation (cost=0.00..2.62 rows=62 width=34) (actual time=0.009..0.109 rows=62 loo
13. 0.026 0.026 ↑ 1.0 1 2 / 2

Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.023..0.026 rows=1 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • -> Seq Scan on region_102008 region (cost=0.00..1.06 rows=1 width=4) (actual time=0.013..0.018 rows=1
  • Filter: (r_name ~~ 'Europ%'::text)
  • Rows Removed by Filter: 4
Planning time : 1.323 ms
Execution time : 187,295.165 ms