450× Faster Joins with Index Condition Pushdown
1–10 of 61 posts
Re: 450× Faster Joins with Index Condition Pushdown
#2Straddled 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.
Re: 450× Faster Joins with Index Condition Pushdown
#3I 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
Re: 450× Faster Joins with Index Condition Pushdown
#4We 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.
Reference
Re: 450× Faster Joins with Index Condition Pushdown
#5What database engine is this in? You reference your product, but I assume this is in MySQL/MariaDB?
https://dev.mysql.com/doc/refman/9.4/en/index-condition-push...
Re: 450× Faster Joins with Index Condition Pushdown
#6[deleted]
Re: 450× Faster Joins with Index Condition Pushdown
#7What database engine is this in? You reference your product, but I assume this is in MySQL/MariaDB? https://dev.mysql.com/doc/refman/9.4/en/index-condition-push...
This isn't really the same as MySQL's ICP; it seems more like what MySQL would call a “ref” or “eq_ref” lookup, i.e. a simple lookup on an indexed value on the right side of a nested-loop join. It's bread and butter for basically any database optimizer.
ICP in MySQL (which can be built on top of ref/eq_ref, but isn't part of the normal index lookup per se) is a fairly weird concept where the storage engine is told to evaluate certain predicates on its own, without returning the row to the optimizer. This is to a) reduce the number of round-trips (function calls) from the executor down into the storage engine, and b) because InnoDB's secondary indexes need an extra storage round-trip to return the row (secondary indexes don't point at the row you want, they contain the primary key and then you have to lookup the actual row from the PK), so if you can remove the row early, you can skip the main row lookup.
Re: 450× Faster Joins with Index Condition Pushdown
#8[deleted]
Re: 450× Faster Joins with Index Condition Pushdown
#9What database engine is this in? You reference your product, but I assume this is in MySQL/MariaDB? https://dev.mysql.com/doc/refman/9.4/en/index-condition-push...
Seems like they are caching MySQL with their own layer built on RocksDB.
Re: 450× Faster Joins with Index Condition Pushdown
#10Straddled 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.
Why downvote?