Say I have a table with an int column, and all I am ever going to read from it is the MAX() int value.
If I create an index on that column, Postgres can perform a reverse scan of that index to get the MAX() value. But since all except one row in the index are just an overhead, can we get the same performance without having to create the full index.
Yes, one can create a trigger to update a single-row table that tracks the MAX value, and query that table instead of issuing a MAX() against the main table. But I am looking for something elegant, because I know Postgres has partial indexes, and I can't seem to find a way to leverage them for this purpose.
Update: This partial-index definition is ideally what I'd like, but Postgres does not allow subqueries in the WHERE clause of a partial-index.
create index on test(a) where a = (select max(a) from test);