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.
SELECT * FROM reports LIMIT 200; for this to work when unsharded.
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:
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?