Clojurians Log v2

Have you tried a lateral join or EXISTS subquery? That might nudge the query planner towards more of a nested loop than a scan then join. Also worth making sure your statistics are up to date first.

probably unhelpful given your current schema, but I like the way https://github.com/xocolatl/periods does it: you treat the main table as if it were mutable, the history is kept in a separate table

Taking a shot here from the hip:

CREATE
OR REPLACE VIEW current_apple_quality AS
select *
from (
	SELECT
	  apple_quality.apple_id,
	  apple_quality.id,
	  apple_quality.created_at,
	  apple_quality.updated_at,
	  apple_quality.quality,
	  apple_quality.apple_id,
	  apple_quality.valid_at,
	  row_number() over(
		partition by apple_quality.apple_id 
		order by apple_quality.valid_at desc
	  ) as rn
    FROM apple_quality
) t
where t.rn = 1

gonna try that in a bit

Would an index of apple_quality (apple_id, valid_at DESC) help?

it did not

we ended up resolving this specific issue by not using a view and using a LEFT JOIN LATERAL where we needed the newest value

but i asked around and apparently adding a current boolean + unique indexing on it is an efficient way