[saltcured]

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.

[mrkaye97]

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

[parmindergarcha]

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.

[mrkaye97]

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

[parmindergarcha]

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'.