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