Comment by mrkaye97 | Hacker News Reader

Comment by mrkaye97 | Hacker News Reader

Parent Full thread

(Matt from Hatchet)

One small addendum here is we've had a lot of success performing joins in memory in a few very specific situations where the alternative is a single, often overcomplicated query. I've heard / seen advice many times in the past about performing fewer round trips to the database being something to optimize for (often good advice!). Sometimes this is taken too far, resulting in overly-complex queries requiring complicated JOIN or UNION logic, CASE logic, and so on.

We have a couple of places in our codebase where we perform two or more simpler queries independently instead, and then loop through their results and use maps to match the relevant rows. Conventional wisdom often suggests this path will hurt performance because of the extra database round trip in addition to the loops needed to perform the join, but it is actually beneficial in these cases because of more predictable query planning behavior. We use this trick sparingly, but it can be helpful in a pinch.

Note that some ORMs will also do this for you in the background, which we don't necessarily endorse, and we try to use this sparingly when writing a single query on its own is not realistic.

This kind of advice is very dependent on the scenario.

If you are doing some kind of full cross product where the join creates a much larger set of rows, it could optimize the DB load and network traffic to fetch the source sets and then generate the permuted set locally.

But, many inner join patterns are selective. They produce a much smaller output than the source records. The traffic to pull all the records and then intersect and filter locally is much worse than having the DB do it.

And that's before you even consider indexed joins, where the query plann is able to make good use of indexes to avoid doing brute-force table scans, sorting, and filtering.

Thanks! I should have clarified - we haven't been using this pattern for selective joins. Strongly agreed that pulling down extra data into memory and then doing the filtering doesn't make much sense. We've found it useful in the case where it's hard to write a query where the planner _does_ make good decisions because of the complexity of the join conditions (e.g. joins using cases, a boolean "or", or something similar).

Also, to re-emphasize: we do this rarely, but it's been helpful the times we've done it

Noted that you only use this sparingly but you could try :-

* Materialised views (especially if the computation/joins are particularly nasty).

* Left outer joins are a good alternative to 'joins using case' and more likely to use the index.

Indeed! Materialized views won't work here as PG doesn't support "always updated" / "auto-refreshing" materialized views natively (although you can get something similar with extensions like TimescaleDB). Left joins are the thing we're often trying to avoid though, especially when the join conditions are involved, as we've seen the planner just make poor decisions in the past at unpredictable times. That's exactly the sort of situation where you might reach for this sort of trick

If left joins are causing issues, maybe for a quick win try increasing the sample size on the column(s) involved e.g. ALTER TABLE tablename ALTER COLUMN columnname SET STATISTICS 1000 - (default I think is 100), remember to run 'ANALYZE'.

I feel like i've heard of people using views for this as well. Like setting up two views and then joining across them because of the complexity of doing it all in one query. I could be wrong though.

Reply on news.ycombinator.com