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

31–40 of 61 posts

Re: 450× Faster Joins with Index Condition Pushdown

#31

Post by ncruces »

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

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 and why.

Re: 450× Faster Joins with Index Condition Pushdown

#32

Post by HDThoreaun »

ncruces wrote: post #31
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

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.

Re: 450× Faster Joins with Index Condition Pushdown

#33

Post by jmalicki »

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.

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.

Re: 450× Faster Joins with Index Condition Pushdown

#34

Post by thr0w »

vjerancrnjak wrote: post #25
sschnei8 wrote: post #13

Earlier quoted context omitted.

I feel like this is more an example of: “We filtered first instead of reading an entire table from disk and performing a lookup” Where both OLAP and OLTP dbms would benefit. To your point, it’s clear certain workloads lend themselves to OLAP and columnar storage much better, but “an endless sequence of misbehavior” seems a bit harsh .

It's not harsh. Recent example, have 1GB of data in total across tables. Query needs 20 minutes. Obvious quadratic/cubic-or-even-worse behavior. I disable nested loop join and it's 4 seconds. Still slow, but don't want to spend time figuring out why it's slower than reading 1GB of data and pipelining the computation so that it's just 1 second, or even faster given the beefy NVME where files are stored (ignoring that…

> I disable nested loop join and it's 4 seconds.

I feel your pain. I've been through all stages of grief with `enable_nestloop`. I've arrived at acceptance. Sometimes you just need to redo your query approach. Usually by the time I get the planner to behave, I've ended up with something that's expressed more simply to boot.

Re: 450× Faster Joins with Index Condition Pushdown

#35

Post by atombender »

vjerancrnjak wrote: post #25
sschnei8 wrote: post #13

Earlier quoted context omitted.

I feel like this is more an example of: “We filtered first instead of reading an entire table from disk and performing a lookup” Where both OLAP and OLTP dbms would benefit. To your point, it’s clear certain workloads lend themselves to OLAP and columnar storage much better, but “an endless sequence of misbehavior” seems a bit harsh .

It's not harsh. Recent example, have 1GB of data in total across tables. Query needs 20 minutes. Obvious quadratic/cubic-or-even-worse behavior. I disable nested loop join and it's 4 seconds. Still slow, but don't want to spend time figuring out why it's slower than reading 1GB of data and pipelining the computation so that it's just 1 second, or even faster given the beefy NVME where files are stored (ignoring that…

I think part of the problem is that the people working on Postgres for the most part aren't PhDs, and Postgres isn't very state of the art.

Postgres implements the ancient Volcano model from the 1980s, but there's been a ton of query optimization research since then, especially from the database groups at TUM Munich, University of Washington, and Carnegie Mellon. Systems like HyPer and Umbra (both at TUM) are state of the art query planners that would eat Postgres' lunch. Lots of work on making planners smarter about rearranging joins to be more optimal, improving cache locality and buffer management, and so on.

Unfortunately, changing an old Volcano planner and applying newer techniques would probably be a huge endeavor.

Re: 450× Faster Joins with Index Condition Pushdown

#36

Post by OutOfHere »

airstrike wrote: post #14
LtdJorge wrote: post #10

Earlier quoted context omitted.

Why downvote?

Reads like an ad written by an LLM, is my guess. It could just be that they translated from their original language to English and got that as a byproduct. Many such cases.

It is completely disingenuous and unfair to claim that something, especially a small blurb, is written by an LLM. And so what if it actually was written by an LLM. If you want to criticize something, do so on the merits or demerits of the points in it. You don't get a free pass by claiming it's LLM output, irrespective of whether it is or not.

Re: 450× Faster Joins with Index Condition Pushdown

#37

Post by OutOfHere »

jamesblonde wrote: post #4

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. Reference https://docs.rondb.com/rondb_parallel_query/#pushdown-joins

rondb code: https://github.com/logicalclocks/rondb

Re: 450× Faster Joins with Index Condition Pushdown

#38

Post by airstrike »

OutOfHere wrote: post #36
airstrike wrote: post #14

Earlier quoted context omitted.

Reads like an ad written by an LLM, is my guess. It could just be that they translated from their original language to English and got that as a byproduct. Many such cases.

It is completely disingenuous and unfair to claim that something, especially a small blurb, is written by an LLM. And so what if it actually was written by an LLM. If you want to criticize something, do so on the merits or demerits of the points in it. You don't get a free pass by claiming it's LLM output, irrespective of whether it is or not.

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.

Re: 450× Faster Joins with Index Condition Pushdown

#39

Post by futevolei »

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?

Re: 450× Faster Joins with Index Condition Pushdown

#40

Post by OutOfHere »

airstrike wrote: post #38
OutOfHere wrote: post #36

Earlier quoted context omitted.

It is completely disingenuous and unfair to claim that something, especially a small blurb, is written by an LLM. And so what if it actually was written by an LLM. If you want to criticize something, do so on the merits or demerits of the points in it. You don't get a free pass by claiming it's LLM output, irrespective of whether it is or not.

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.

Post reply on HN