Optimizing Slow Changing Dimension (SCD2) joins
I found a somewhat satisfactory solution to this issue. I feel a bit foolish for overlooking this, but you could put it down to a case of 'premature optimization' on my behalf. I had omitted the end_date columns, as per the academic example in Wikipedia, in my SCD tables. I figured this was not particularly necessary since all you need to do to 'rewind' your table and find the correct SCD row is find the first row with a start_date less than or equal to the date you're querying against when the table is sorted. But this requires a table sort to function correctly.

The major bottleneck in my query was caused by sorting a moderately large ( 2500+ row ) table by a timestamp. I managed to resolve this issue by adding an 'end_date' column and changing the query to use ( simplified example ):

LEFT JOIN LATERAL ( SELECT * FROM tax t
  WHERE p.time_created &gt;= t.time_created AND p.time_created &lt;= COALESCE(t.time_ended, '9999-12-30'::date)) current_tax ON true
....


This cuts the query down from ~1sec to &lt;100ms. I tried adding indexes against fields in the 2500+ row table, but this didn't seem to have an enormous effect on the query time, but perhaps I had made some errors there. This seems more optimized at any rate.

An updated DBFiddle can be seen here.

I would love some advice on how to better represent this, or how to better account for null values in the time_ended column.