Q:

how to efficiently locate a value from one table among values from another table, with SQL

I have a problem in Postgresql which I find even difficult to describe in the title: I have two tables, containing each a range of values very similar but not identical.   Suppose I have values like 0, 10, 20, 30, ... in one, and 1, 5, 6, 9, 10, 12, 19, 25, 26, ... in the second one (these are milliseconds).   For each value of the second one I want to find the values immediately lower and higher in the first one.   So, for the value 12 it would give me 10 and 20.   I'm doing it like this :
SELECT s.*, MAX(v1."millisec") AS low_v,  MIN(v2."millisec") AS high_v
FROM "signals" AS s, "tracks" AS v1, "tracks" AS v2 
WHERE   v1."millisec" <= s."d_time"  
    AND v2."millisec" > s."d_time" 
GROUP BY s."d_time", s."field2";     -- this is just an example

And it works ... but it is very slow once I process several thousands of lines, even with indexes on s."d_time" and v.millisec.   So, I think there must be a much better way to do it, but I fail to find one.   Could anyone help me ?  

A:

Try:
select s.*, 
      (select millisec 
         from tracks t 
        where t.millisec <= s.d_time 
        order by t.millisec desc 
        limit 1
      ) as low_v,

      (select millisec 
         from tracks t 
        where t.millisec > s.d_time 
        order by t.millisec asc  
        limit 1
      ) as high_v
 from signals s;

Be sure you have an index for track.millisec;
If you had just created
the index, you'll need to analyze the table to take advantage of it.