Hacker News Reader

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

Reply on news.ycombinator.com