SQL postgres query to check previous matching index

Question

My tables track changes in event variables and state variables. I would like to be able to easily make a query where I get events that occur only when the state variable at that time meets a certain condition. The problem I have is that the timestamps for the events and the states don't align exactly. Rather, the state variable entry with the timestamp most recent relative to the event timestamp should be checked. I could append all the state variables to all my event entries, but this would make my database many times larger.

My question is whether there is a more efficient way to make the following query. Ideally, I think, I would like the timestamp in my state variables to be indexed so that it can be accessed using any value from it's stated value up to the next timestamp. The timestamps are in strictly ascending order.

Edit: Here are example tables included as well.

CREATE TABLE event
(
  tickstamp integer NOT NULL,
  value integer,
  CONSTRAINT event_pkey PRIMARY KEY (tickstamp)
);

INSERT INTO event VALUES
  (121, 100),
  (152, 75),
  (194, 150);

Create TABLE state
(
  tickstamp integer NOT NULL,
  value integer,
  CONSTRAINT state_pkey PRIMARY KEY (tickstamp)
);

INSERT INTO state VALUES
  (0, 30),
  (160, 95);

SELECT * FROM event, state
WHERE state.value > 50 AND state.tickstamp = (
    SELECT tickstamp FROM state WHERE tickstamp <= event.tickstamp ORDER BY tickstamp DESC LIMIT 1);

Really, all I care about here is filtering events so that state.value > 50 when the event occurs, which is only true for the third entry in event, but I have to do a subquery in order to match up the tickstamps.

Answer 1

SELECT * FROM event, state
WHERE state.value > 50 AND state.tickstamp = (
    SELECT MAX(tickstamp) FROM state WHERE tickstamp <= event.tickstamp);

of course, if tickstamp isn't indexed, it'll likely still require a tablescan. Better solution is to have an auto-incremented field as your index and look for max.

Answer 2

I was able to solve this using the postgres range type.

CREATE TABLE event
(
  tickstamp int8range NOT NULL,
  value integer,
  CONSTRAINT event_pkey PRIMARY KEY (tickstamp)
);

INSERT INTO event VALUES
  ('[121,152)', 100),
  ('[152,194)', 75),
  ('[194,)', 150);

Create TABLE state
(
  tickstamp int8range NOT NULL,
  value integer,
  CONSTRAINT state_pkey PRIMARY KEY (tickstamp)
);

INSERT INTO state VALUES
  ('[0,160)', 30),
  ('[160,)', 95);

SELECT * FROM event, state
WHERE state.value > 50 AND event.tickstamp <@ state.tickstamp;