Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

450× Faster Joins with Index Condition Pushdown

readyset.io

Post reply on HN
Print view View original

41–50 of 61 posts

Re: 450× Faster Joins with Index Condition Pushdown

#41

Post by dinobones »

futevolei wrote: post #39

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 cla…

I always see these fancy DB engines and data lake blog posts and I am curious… why?

At every place I’ve worked at this is a solved problem: Hive+Spark, just keep everything sharded across a ton of machines.

It’s cheaper to pay for a Hive cluster that does dumb queries than paying these expensive DB licenses, data engineers building arbitrary indices, etc… just throw compute at the problem, who cares. 1TB of RAM /flash is so cheap these days.

Even working on the worlds “biggest platforms” a daily partition of user data is like 2TB.

You’re telling me a F500 can’t buy a 5 machine/40TB cluster for like $40k and basically be set?

Re: 450× Faster Joins with Index Condition Pushdown

#42

Post by richardw »

futevolei wrote: post #39

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 cla…

We didn’t use the DBA’s for this but my last few teams, we got good at DB’s, performance etc. DBA’s were too general and they kept the lights on, but for real performance you should get one or two people who know what they’re doing for your applications. Or learn. I took on juniors who are now fantastic.

For the first decade I wanted nothing to do with DB’s aside from places to store data. One day I saw a few things that made a massive difference and then went wild on learning how to speed things up. It’s fantastic and because few devs know this stuff well, it becomes a superpower. You wouldn’t believe what you can squeeze out of modern SQL DB’s and hardware, without touching any kind of optimised solutions. Which I love too but that’s a different post.

Maybe ask the DBA’s a few questions and see if that triggers any interest for you. Look at query plans and how many rows are processed for a query. How many columns. What is being locked. Can you remove locks when you’re just running a query and how much does that speed things up. There are queries for all sorts of metrics, eg which indexes are huge but never used. The DB can often suggest indexes, but don’t just use add the suggestions. Use them as a starting point to reason about your own. Try get down to low millisecond queries for really frequent stuff, because it’ll make them fast and means less time locking the DB, less RAM, less temp table storage.

All my other skills have aged. Fundamental database knowledge lasts.

Re: 450× Faster Joins with Index Condition Pushdown

#43

Post by zmmmmm »

HDThoreaun wrote: post #27
_1tem wrote: post #23

Shouldn't the query planner catch things like this? Sounds like a performance bug if this happens in Postgres.

Yea this is pretty fucking basic stuff. Any competent optimization engine should be doing this. "push down indexes as much as possible" is literally the first thing a query planner should be trying to do

I had to dig through to see the details of what database was really in play here, and sure enough, it's a wrapper around a key-value store (RocksDB). So while I'll confess I know little about RocksDB it does sound an awful lot like they threw out a mature relational database engine with built in optimization and now are in the process of paying the price for that by manually optimizing each query (against a key-value store no less, which probably fundamentally limits what optimizations can be done in any general way).

Would be curious if any RocksDB knowledgeable people have a different analysis.

Re: 450× Faster Joins with Index Condition Pushdown

#44

Post by airstrike »

OutOfHere wrote: post #40
airstrike wrote: post #38

Earlier quoted context omitted.

I'm puzzled by this reply. It's perfectly fine for me to hypothesize on the reason for downvotes in response to someone else asking why it has been downvoted. You're free to opine on the reason for downvotes too. This metacomment, however, is more noise than signal.

What you had claimed is not even a potential reason in the universe of reasons. It is a demonstration of bias, an excuse to refrain from reason. One line summaries of comprehensible articles can get downvoted because they don't add value beyond what's already very clear from the article.

it is objectively a potential reason in the universe of reasons, but you're 100% free to believe whatever you want, even if it's wrong

and the fact that multiple people upvoted my comment at a minimum suggests others also believe it to be a possible explanation

i have no idea why you've chosen this particular hill to die on, when neither of us stands to profit from this protracted exchange

Re: 450× Faster Joins with Index Condition Pushdown

#45

Post by ww520 »

futevolei wrote: post #39

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 cla…

Nope. If they didn't actively work against us, we would thank the lucky stars.

Re: 450× Faster Joins with Index Condition Pushdown

#46

Post by dgacmu »

futevolei wrote: post #39

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 cla…

Performance engineering is a modestly specialized subdomain - I think that applies to databases just as it does to code.

Re: 450× Faster Joins with Index Condition Pushdown

#47

Post by code_runner »

futevolei wrote: post #39

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 cla…

If you make it your thing and keep on being good at your other thing, you’re gonna be 90% more valuable than most of your coworkers.

I totally lose respect for sr engineers who can’t write sql to find even simple answers to questions.

It’s never bad to have another arrow in your quiver

Re: 450× Faster Joins with Index Condition Pushdown

#48

Post by Jupe »

HDThoreaun wrote: post #32
ncruces wrote: post #31

Earlier quoted context omitted.

Yes. But here they are deciding between "pushdown o.status==shipped" and "pushdown u.email==address@", in parallel both , then join (which they already did) or first doing "u.email==address@" then pushing down "u.id==o.user_id" mostly. This is a judgment call. Their planner is pretty dumb to not know which one is better, but “push down as much as possible” doesn't cut it: you need to actually decide what to push down…

No, it is not a judgement call. The query planner should be storing the distributions of the values in every index. This makes it obvious which pushdown to do here. Again, basic stuff. Youre right though not quite simple as "push down as much as possible", it is one step past that.

Agreed. Isn't this precisely why key statistics (table statistics) are maintained in many DB systems? Essentially, always "push down" the predicate with the worst statistics and always execute (early) the predicates with high selectivity.

I'd be very surprised if virtually every RDBMS doesn't do this already.

Re: 450× Faster Joins with Index Condition Pushdown

#49

Post by hxtk »

zmmmmm wrote: post #43
HDThoreaun wrote: post #27

Earlier quoted context omitted.

Yea this is pretty fucking basic stuff. Any competent optimization engine should be doing this. "push down indexes as much as possible" is literally the first thing a query planner should be trying to do

I had to dig through to see the details of what database was really in play here, and sure enough, it's a wrapper around a key-value store (RocksDB). So while I'll confess I know little about RocksDB it does sound an awful lot like they threw out a mature relational database engine with built in optimization and now are in the process of paying the price for that by manually optimizing each query (against a key-value…

> against a key-value store no less, which probably fundamentally limits what optimizations can be done in any general way

I would disagree with this assumption for two reasons: first, theoretically, a file system is a key value store, and basically all databases run on file systems, so it stands to reason that any optimization Postgres does can be achieved as an abstraction over a key-value store with a good API because Postgres already did.

Second, less theoretically, this has already been done by CockroachDB, which stores data in Pebble in the current iteration and previously used RocksDB (pebble is CRDB’s Go rewrite of RocksDB) and TiDB, which stores its data in TiKV.

A thin wrapper over a KV store will only be able to use optimizations provided by the KV store, but if your wrapper is thick enough to include abstractions like adding multiple tables or inserting values into multiple cells in multiple tables atomically, then you can build arbitrary indices into the abstraction.

I wouldn’t tend to call a KV store a bad database engine because I don’t think of it as a database engine at all. It might technically be one under the academic definition of a database engine, but I mostly see it being used as a building block in a more complicated database engine.

Re: 450× Faster Joins with Index Condition Pushdown

#50

Post by Seattle3503 »

jmalicki wrote: post #33
HDThoreaun wrote: post #32

Earlier quoted context omitted.

No, it is not a judgement call. The query planner should be storing the distributions of the values in every index. This makes it obvious which pushdown to do here. Again, basic stuff. Youre right though not quite simple as "push down as much as possible", it is one step past that.

Without storing the joint distribution of the values corresponding to the conditions that span multiple tables, it can be hard to know what's a win.

Postgres by default computes univariate stats for each column and uses those. If this is producing bad query plans, you can extend the statistics to be multivariate for select groups of columns manually. But to avoid combinatorially growth of stats related storage and work, you have to pick the columns by hand.
Post reply on HN