Re: Finding latest record for a number of groups in an INSERT-only table

It seems to me one solution is to alter your table topology by
partitioning your table by the keys you need to query on, and then
using simple aggregates.

You;d have to set up ON INSERT DO INSTEAD rules, and you might get a
performance hit.....

Another solution might be to break up the query into several pieces,
and running smaller queries aimed at retrieivng individual rows.
This could be done inside a stored proc. Looking into how we did this
with some queries in LedgerSMB.....

Here's a stored procedure we used in LedgerSMB to pull distinct years
from a table with, maybe 10M rows in a timely fashion. Something
similar might be doable for you with modifications of course:

CREATE OR REPLACE FUNCTION date_get_all_years() returns setof INT AS
$$
DECLARE next_record int;
BEGIN

SELECT MIN(EXTRACT ('YEAR' FROM transdate))::INT
INTO next_record
FROM acc_trans;

LOOP

EXIT WHEN next_record IS NULL;
RETURN NEXT next_record;
SELECT MIN(EXTRACT ('YEAR' FROM transdate))::INT AS YEAR
INTO next_record
FROM acc_trans
WHERE EXTRACT ('YEAR' FROM transdate) > next_record;

END LOOP;

END;

$$ language plpgsql;