Straddled joins were still a bottleneck in Readyset even after switching to hash joins. By integrating Index Condition Pushdown into the execution path, we eliminated the inefficiency and achieved up to 450× speedups.
I love this type of practical optimization for DB queries. I’ve always liked how [rom-rb](https://rom-rb.org/learn/core/5.2/combines/) made the combine pattern easy to use when joins are slow. Nice to see this implemented at DB layer
We call these pushdown joins in rondb. They only support an equality condition for the index condition.
Joins with index condition pushdown is a bit of a mouthful.
We also went from like 6 seconds to 50ms. Huge speedup.
Another example of row based dbs somehow being insanely slow compared to column based.
Just an endless sequence of misbehavior and we’re waving it off as rows work good for specific lookups but columns for aggregations, yet here it is all the other stuff that is unreasonably slow.
I read their website landing page but it’s still kinda confusing — what exactly is readyset? It all sounds like it’s a cache you can set up in front of MySQL/postgres. But then this article is talking about implementing joins which is what the database itself would do, not a cache. But then the blurbs talk about it like it’s a “CDN for your database” that brings your data to the edge. What the heck is it?!
Do the db guys at your company help you optimize queries and table set up at all? Ours basically don’t at all. Their job is to maintain the db apparently and us devs are left to handle this and it seems wrong. I’ve been partitioning tables and creating indexes the past few weeks trying to speed up a view and running explain analyze and throwing the results in Gemini and my queries are still slow af. I had one sql class in college, it’s not my thing. Seems like if dbas would spend a few minutes with me asking about the data and what we are trying to do they could get this guys results relatively easily. Am I wrong?
1) dba - maintain OS, db software/hardware - DBs are complex beasts you want experts setting up hardware or deploying into the clown in a smarter way (virtual machines in the clown will cost you a fortune and your sanity)
2) database developers- specialists in writing sql.
The two functions work together but are distinct.
Nowadays due to private equity and Stanford MBAs we have junior engineers doing all plus “dev ops”.
It’s an absolute circus.
IMO, this is why we end up with ask these crazy DB startups - routing around the damage.
RDMS in modern hardware are insanely fast and powerful.
Hi, I’m from Readyset. We hadn’t realized this post had picked up traction here, but I wanted to share a bit more context.
Some folks pointed out that index pushdowns and join optimizations aren’t novel. That’s fair. In a traditional database engine, pushdowns and access path selection are standard. But Readyset isn’t a conventional engine.
When you create a materialized view in Readyset, the query is compiled into a dataflow graph designed for high-throughput incremental updates, not per-request planning. We receive changes from the upstream database’s replication stream and propagate deltas through the graph. Reads typically hit the cache directly at sub-ms latency.
But when a key hasn’t yet been materialized, we perform what we call an upquery -- a one-off pull from the base tables (stored in RocksDB) to hydrate the missing result. Since we don’t re-plan queries on each request, the structure of that upquery, including filter pushdowns and join execution, is precompiled into the dataflow.
Straddled joins, where filtering is required on both sides of the join, are especially tricky in this model. Without smarter pushdown, we were overfetching data and doing unnecessary join work. This optimization pushes composite filters into both sides of the join to reduce RocksDB scans and hash table size.
It’s a well-known idea in the context of traditional databases, but making it work in a static, incrementally maintained dataflow system is what makes it unique here.
Happy to go deeper if folks are curious. Appreciate the thoughtful feedback.
17 comments
[ 3.3 ms ] story [ 35.7 ms ] threadWe also went from like 6 seconds to 50ms. Huge speedup.
Reference
https://docs.rondb.com/rondb_parallel_query/#pushdown-joins
https://dev.mysql.com/doc/refman/9.4/en/index-condition-push...
Just an endless sequence of misbehavior and we’re waving it off as rows work good for specific lookups but columns for aggregations, yet here it is all the other stuff that is unreasonably slow.
Push down filters to read the least data possible.
Or, know your data and be able to tell the query engine which kind of join strategy you would like (hash vs push down)
Does your hotpath query have a covering index? Is it seek-able. Are you predicates SARG-able.
Seriously biggest bang for your buck is to understand SARG-ability and understand indexes in your database really well.
There are two distinct job functions.
1) dba - maintain OS, db software/hardware - DBs are complex beasts you want experts setting up hardware or deploying into the clown in a smarter way (virtual machines in the clown will cost you a fortune and your sanity) 2) database developers- specialists in writing sql.
The two functions work together but are distinct.
Nowadays due to private equity and Stanford MBAs we have junior engineers doing all plus “dev ops”.
It’s an absolute circus.
IMO, this is why we end up with ask these crazy DB startups - routing around the damage.
RDMS in modern hardware are insanely fast and powerful.
Some folks pointed out that index pushdowns and join optimizations aren’t novel. That’s fair. In a traditional database engine, pushdowns and access path selection are standard. But Readyset isn’t a conventional engine.
When you create a materialized view in Readyset, the query is compiled into a dataflow graph designed for high-throughput incremental updates, not per-request planning. We receive changes from the upstream database’s replication stream and propagate deltas through the graph. Reads typically hit the cache directly at sub-ms latency.
But when a key hasn’t yet been materialized, we perform what we call an upquery -- a one-off pull from the base tables (stored in RocksDB) to hydrate the missing result. Since we don’t re-plan queries on each request, the structure of that upquery, including filter pushdowns and join execution, is precompiled into the dataflow.
Straddled joins, where filtering is required on both sides of the join, are especially tricky in this model. Without smarter pushdown, we were overfetching data and doing unnecessary join work. This optimization pushes composite filters into both sides of the join to reduce RocksDB scans and hash table size.
It’s a well-known idea in the context of traditional databases, but making it work in a static, incrementally maintained dataflow system is what makes it unique here.
Happy to go deeper if folks are curious. Appreciate the thoughtful feedback.