Mark Lewis <mark.lewis@mir3.com> writes:
> Have you considered creating one partial index per assetid? Something
> along the lines of "CREATE INDEX asset_index_N ON asset_positions(ts)
> WHERE assetid=N"? I'd guess that the planner probably wouldn't be smart
> enough to use the partial indexes unless you issued a separate query for
> each assetid, but each one of those queries should be really fast.
Actually, a single index on (assetid, ts) is sufficient to handle
select max(ts) from asset_positions where assetid = constant
The problem is to know what values of "constant" to issue the query for,
and this idea doesn't seem to help with that.
If Graham is willing to assume that the set of assetids changes slowly,
perhaps he could keep a summary table that contains all the valid
assetids (or maybe there already is such a table? is assetid a foreign
key?) and do
select pk.assetid,
(select max(ts) from asset_positions where assetid = pk.assetid)
from other_table pk;
I'm pretty sure the subselect would be planned the way he wants.
regards, tom lane