How to speed up min/max aggregates in Postgres without an index that is unnecessary otherwise

Question

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);

Answer 1

You cannot use aggregate functions or subquery expressions in the predicate of a partial index. That would make hardly any sense logically, anyway, given the IMMUTABLE nature of index entries.

If you have a range of integers and you can guarantee that the maximum will always be greater than x, you can benefit from this meta-information, though.

CREATE INDEX text_max_idx ON test (a) WHERE a > x;

This index will only be used by the query planner if you include a WHERE clause that matches the index predicate. For instance:

SELECT max(a) FROM test WHERE a > x;

There can be more conditions, but this one has to be included to use the index.
I am serious about "guarantee", though. Your query will return nothing if the predicate is false.

You could build in a fail-safe:

SELECT COALESCE( (SELECT max(a) FROM test WHERE a > x)
                 (SELECT max(a) FROM test));

You can generalize this approach with more than one partial index. Similar to this technique, just a lot simpler.

I would consider the trigger approach, though, except for very big write loads on the table.