Having the following table structure
table1 id: BIGSERIAL updated_at: TIMESTAMP WITHOUT TIME ZONE DEFAULT now() ... CREATE INDEX table1_updated_at ON table2(updated_at);
table2 table1_id: BIGINT PRIMARY KEY updated_at: TIMESTAMP WITHOUT TIME ZONE DEFAULT now() ... CREATE INDEX table2_updated_at ON table1(updated_at);
trying to get results ordered by the most recently updated like:
select * from table 1 a inner join table2 b on a.id = b.table1_id order by GREATEST(a.updated_at, b.updated_at) limit 100
But running into performance issues on large tables. I'm not entirely sure how to approach this problem. The pattern seems common enough to me.
It looks like postgres isn't able to combine the indices for
updated_at when using
GREATEST. Which seems kind of odd to me because if I have two large ordered lists, and want to get the 100 greatest values across the two lists, I don't have to traverse them completely, right?
As requested, here's a
EXPLAIN (ANALYZE, BUFFERS)