Hash Sharded Indexes - Force read across all shards?

Is there any way to write this query to tell Cockroach to evenly distribute the reads across all shards?

SELECT * FROM reports LIMIT 200;

Currently Cockroach will SELECT everything from BUCKET 0, then 1, then 2, etc. and never reaches the other shards if LIMIT is too small.

PS: Thanks to the Cockroach Labs team for their work on Hash Sharded Indexes!!

What’s your goal for hitting all of the shards?

I don’t know exactly if it will help but you could add an order by on the indexed columns. That should do a top K from all shards.

1 Like

Thank you for your insight, Andrew!

This table’s goal is: Newest rows, as fast as possible. Rough ordering is okay!

I am set up with PRIMARY KEY (id DESC) with a unique_rowid() to achieve rough ordering.
This allows SELECT * FROM reports LIMIT 200; for this to work when unsharded.

When sharded:

SELECT * FROM reports ORDER BY id DESC LIMIT 200;

As you’ve recommended, works well! Scanning 200 rows for each shard then sorting & reducing the result.

However, I was hoping to fetch just the minimum amount from each shard, as the results can be roughly ordered.

Here’s what I have:

SELECT * 
FROM (SELECT * FROM reports WHERE crdb_internal_id_shard_4=0 LIMIT 50) 
UNION (SELECT * FROM reports WHERE crdb_internal_id_shard_4=1 LIMIT 50) 
UNION (SELECT * FROM reports WHERE crdb_internal_id_shard_4=2 LIMIT 50) 
UNION (SELECT * FROM reports WHERE crdb_internal_id_shard_4=3 LIMIT 50);

Works, and has higher performance, as there’s less work involved for cockroach.

Any suggestions on how to make this query shorter, or more maintainable? Or other insight on how to do this better?