How to search the short strings by index?

Good afternoon.

Suppose there is a table that reflects different versions of the same object over time:

 updated DATE,
 PRIMARY KEY (id, updated)

-- Data:
INSERT INTO foo (id, updated) VALUES
 (1, '2000-01-01'), -- actual from 01 to 09 the number of
 (1, '2000-01-10'), -- current with 10 to 19 number
 (1, '2000-01-20'), -- actual from 20 to 24 count
 (1, '2000-01-25'), -- actual 25 29 number
 (1, '2000-01-30') -- current 30 to this day

The task is to find up to date :date_param line with :id_param.

Not a very desirable implementation, it is possible to consider such request:
SELECT * FROM foo WHERE id = :id_param AND date <= :date_param ORDER BY updated DESC LIMIT 1

Theoretically, this query should be performed on the basis of the index, without fetching and pre-sorting of the result. But looks a bit bulky. And if you need to extract the relevant records for several id values, the query becomes even more complex: I can't even imagine how (I think you can do with unnest or union).

The question is, is it possible to do this lookup more elegantly? With the ability to search multiple "actual" rows.

Thank you.
July 2nd 19 at 17:31
2 answers
July 2nd 19 at 17:33
1. Option 1: expr BETWEEN min AND max => here
2. Option 2: Interlayer: add an additional table time intervals (dateRanges), in this additional field with the record ID of dateRanges. dateRanges - support when adding a new record: the date came from the last interval to create the next.

If you add both options is the best best choice.
Because when the "waterfall" slope:
You significantly reduce the time for searching/selection/sorting, and the required calc. resources.
If you need a specific query 1 time only use the 1st option.
If you need to repeat a specific query repeatedly, we can reduce the search area based on the table DateRanges through the set time intervals.
July 2nd 19 at 17:35
If the current date is the current day, it is possible in the table to store only the active and the shift data sheduler.

Find more questions by tags PostgreSQL