0

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?

edit

As requested, here's a EXPLAIN (ANALYZE, BUFFERS)

  • 1
    there you go: explain.depesz.com/s/rYUt – Jan Apr 10 at 14:53
  • 1
    The sort isn't your problem. It's the join and the seq scans that are responsible for 94% of the runtime. – a_horse_with_no_name Apr 10 at 14:58
  • That's odd, removing the order by part of the query finishes under one second: explain.depesz.com/s/cmAD – Jan Apr 10 at 15:01
  • Try a different ORDER BY clause, since a LIMIT without an ORDER BY gives the engine complete freedom to retrieve ANY 100 rows, negating the need to perform the full join. – SQLRaptor Apr 10 at 16:48
  • with the order by, Postgres is forced to go through all rows in both tables and thus it needs to scan both tables and process the join for all rows. – a_horse_with_no_name Apr 10 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.