Last/First Point since change

[JLockerman]

Currently, the canonical way to find the first point since a change, e.g. finding the first time since start_time where value became 'x' is, to use an INNER JOIN LATERAL like so

SELECT * FROM vehicles v
INNER JOIN LATERAL (
    SELECT * FROM location l 
    WHERE l.vehicle_id = v.vehicle_id
        AND time >= start_time
        AND value = 'x'
    ORDER BY time ASC LIMIT 1) AS DATA ON true; 

(based on our docs)

Is there any better thing we can do?

(Thanks to slack user Jesse Michael for pointing this out)

[JLockerman]

I'm not sure there's really much we can do for this one; we very much want to use any (time, veh, icle_id) indexes that we may have, and a function-based approach would lose that. (That index provides an orders-of-magnitude speedup)

[inselbuch]

The most recent value for every recording should be maintained by timescale and instantly accessible. Please consider implementing a snapshot() function, viz.,

select snapshot(time),snapshot(value) from hypertable;

There are all kinds of creative ways for getting these values. But all of them are cumbersome, slow, intensive, etc.

I currently support this capability by maintaining a table of my own just for these values. Kludgy.