Raw data behind Fanning a query out to every tablet with yb_hash_code. The harness which produced these numbers is run-benchmark.sh.

Reproducing

The harness starts a disposable three node YugabyteDB cluster and a postgres:16 client container on a private Docker network, publishes no host ports, and removes everything when it exits. Override IMAGE, NODE_CPUS, ROWS, QUEUE_ROWS or DURATION if necessary, set SECTIONS (e.g. SECTIONS="3 4") to run a subset, and set KEEP=1 to leave the cluster running afterwards.

bash run-benchmark.sh

Run date: 2026-10-09

Environment:

  • YugabyteDB 2026.1.2.0-b137 (yugabytedb/yugabyte image), default replication factor of 3
  • 3 nodes, each capped at 2 CPUs with --cpus 2
  • Docker 29.7 on Apple Silicon, macOS 15
  • Clients (psql and pgbench 16) in a separate, uncapped container

Output, sections 1 to 4

== 1. Tablets of jobs in the hash space
 start_hash_code | end_hash_code |    leader    
-----------------+---------------+--------------
               0 |         10922 | ybfan-1:5433
           10922 |         21845 | ybfan-1:5433
           21845 |         32768 | ybfan-2:5433
           32768 |         43690 | ybfan-3:5433
           43690 |         54613 | ybfan-3:5433
           54613 |         65536 | ybfan-2:5433
(6 rows)

== 2. Plans
-- SELECT id, payload FROM jobs 
 Seq Scan on jobs (actual time=2.060..1677.701 rows=2000000 loops=1)
   Storage Table Read Requests: 979
   Storage Table Read Ops: 1957
   Storage Table Rows Scanned: 2000000
 Storage Rows Scanned: 2000000
-- SELECT id, payload FROM jobs WHERE yb_hash_code(id) >= 10922 AND yb_hash_code(id) < 21845
 Index Scan using jobs_pkey on jobs (actual time=1.421..130.765 rows=333453 loops=1)
   Index Cond: ((yb_hash_code(id) >= 10922) AND (yb_hash_code(id) < 21845))
   Storage Table Read Requests: 326
   Storage Table Read Ops: 326
   Storage Table Rows Scanned: 333453
 Storage Rows Scanned: 333453
-- SELECT id, payload FROM jobs WHERE yb_hash_code(id) >= 5000 AND yb_hash_code(id) < 16000
 Index Scan using jobs_pkey on jobs (actual time=7.838..311.750 rows=335145 loops=1)
   Index Cond: ((yb_hash_code(id) >= 5000) AND (yb_hash_code(id) < 16000))
   Storage Table Read Requests: 177
   Storage Table Read Ops: 329
   Storage Table Rows Scanned: 335145
 Storage Rows Scanned: 335145
-- SELECT id, payload FROM jobs WHERE yb_hash_code(id) % 6 = 1
 Seq Scan on jobs (actual time=1.771..1748.636 rows=333491 loops=1)
   Filter: ((yb_hash_code(id) % 6) = 1)
   Rows Removed by Filter: 1666509
   Storage Table Read Requests: 979
   Storage Table Read Ops: 1957
   Storage Table Rows Scanned: 2000000
 Storage Rows Scanned: 2000000

== 3. Reading every row, median of 3 runs after a warm-up
     1 readers    2884 ms
     2 readers    2054 ms
     3 readers    1281 ms
     6 readers    1370 ms
    12 readers    1387 ms

== 4. Job queue, 12 workers
-- (a) index on (status HASH, created_at ASC), oldest first
 pending_hash_code 
-------------------
             39657
(1 row)

    cpu ybfan-1 205.11%
    cpu ybfan-2 196.75%
    cpu ybfan-3 205.05%
    claims/s 93  failed txns 0
            ->  Index Scan using queue_status on queue (actual time=1.745..1.820 rows=10 loops=1)
                  Index Cond: (status = 'pending'::text)
                  Storage Table Read Execution Time: 0.747 ms
                  Storage Table Rows Scanned: 10
                  Storage Index Read Execution Time: 0.340 ms
                  Storage Index Rows Scanned: 10
    Execution Time: 35.354 ms
    Storage Read Execution Time: 31.803 ms
    Storage Rows Scanned: 30
    Catalog Read Execution Time: 42.221 ms
    Storage Execution Time: 74.023 ms
-- (b) partial index on (id HASH) WHERE pending, no slice
    cpu ybfan-1 197.68%
    cpu ybfan-2 189.12%
    cpu ybfan-3 193.51%
    claims/s 129  failed txns 0
            ->  Index Scan using queue_pending on queue q (actual time=2.446..2.563 rows=10 loops=1)
                  Storage Filter: (status = 'pending'::text)
                  Storage Table Read Execution Time: 0.722 ms
                  Storage Table Rows Scanned: 10
                  Storage Index Read Execution Time: 1.095 ms
                  Storage Index Rows Scanned: 20
    Execution Time: 45.519 ms
    Storage Read Execution Time: 40.791 ms
    Storage Rows Scanned: 40
    Catalog Read Execution Time: 21.727 ms
    Storage Execution Time: 62.518 ms
-- (c) as (b), random slice of 6, one per tablet
    cpu ybfan-1 203.37%
    cpu ybfan-2 203.55%
    cpu ybfan-3 201.80%
    claims/s 367  failed txns 0
            ->  Index Scan using queue_pending on queue q (actual time=1.904..1.971 rows=10 loops=1)
                  Index Cond: ((yb_hash_code(id) >= 0) AND (yb_hash_code(id) < 10922))
                  Storage Filter: (status = 'pending'::text)
                  Storage Table Read Execution Time: 0.507 ms
                  Storage Table Rows Scanned: 10
                  Storage Index Read Execution Time: 0.918 ms
                  Storage Index Rows Scanned: 10
    Execution Time: 39.396 ms
    Storage Read Execution Time: 36.779 ms
    Storage Rows Scanned: 30
    Catalog Read Execution Time: 34.209 ms
    Storage Execution Time: 70.989 ms
-- (d) as (b), random slice of 64
    cpu ybfan-1 206.65%
    cpu ybfan-2 206.95%
    cpu ybfan-3 204.23%
    claims/s 1003  failed txns 0
            ->  Index Scan using queue_pending on queue q (actual time=2.123..2.243 rows=10 loops=1)
                  Index Cond: ((yb_hash_code(id) >= 0) AND (yb_hash_code(id) < 1024))
                  Storage Filter: (status = 'pending'::text)
                  Storage Table Read Execution Time: 0.657 ms
                  Storage Table Rows Scanned: 10
                  Storage Index Read Execution Time: 0.782 ms
                  Storage Index Rows Scanned: 10
    Execution Time: 64.560 ms
    Storage Read Execution Time: 58.934 ms
    Storage Rows Scanned: 30
    Catalog Read Execution Time: 41.566 ms
    Storage Execution Time: 100.500 ms

Output, section 5

Run separately against a fresh cluster after section 5 was added to the harness.

== 5. Slicing an index hashed on something else, and built-in parallel scan
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Limit
   ->  Index Scan using queue_status on queue
         Index Cond: (status = 'pending'::text)
         Filter: ((yb_hash_code(id) >= 10922) AND (yb_hash_code(id) < 21845))
(4 rows)

    QUERY PLAN    
------------------
 Seq Scan on jobs
(1 row)

Notes

  • Section 3 varies between runs by a few hundred milliseconds. An earlier run gave 3,652 ms for 1 reader and 1,975 to 2,008 ms for 3, 6 and 12, so the same plateau at one reader per node with a 1.8x gain rather than 2.3x
  • The CPU figures in section 4 are docker stats samples taken halfway through each run, where 200% is the full 2 CPU cap
  • Variants (b) to (d) pin queue_pending with a pg_hint_plan hint. Without it the planner scans queue_pkey and filters on status, because FOR UPDATE needs the table row anyway, and an earlier unhinted run of (c) managed 218 claims/s against 367, with one more claim at the end reading 12,004 rows to find 10
  • No transaction failed in any variant, with pgbench retrying up to 10 times on serialisation errors