- # get last 10 transaction ids
- my $query = 'SELECT trans_id, max(itime) FROM inventory GROUP BY trans_id ORDER BY max(itime) DESC LIMIT 10';
- my @ids = selectall_array_query($::form, $::form->get_standard_dbh, $query);
+ # We want to fetch the last 10 inventory events (inventory rows with the same trans_id)
+ # To prevent a Seq Scan on inventory set an index on inventory.itime
+ # Each event may have one (transfer_in/out) or two (transfer) inventory rows
+ # So fetch the last 20, group by trans_id, limit to the last 10 trans_ids,
+ # and then extract the inventory ids from those 10 trans_ids
+ # By querying Inventory->get_all via the id instead of trans_id we can make
+ # use of the existing index on id
+
+ # inventory ids of the most recent 10 inventory trans_ids
+ my $query = <<SQL;
+with last_inventories as (
+ select id,
+ trans_id,
+ itime
+ from inventory
+ order by itime desc
+ limit 20
+),
+grouped_ids as (
+ select trans_id,
+ array_agg(id) as ids
+ from last_inventories
+ group by trans_id
+ order by max(itime)
+ desc limit 10
+)
+select unnest(ids)
+ from grouped_ids
+ limit 20 -- so the planner knows how many ids to expect, the cte is an optimisation fence
+SQL