On Wed, 27 Apr 2022 at 19:54, Emil Iggland <emil.iggland@xxxxxxxxxxx> wrote: > > > You've got the wrong column order (for this query anyway) in that > > index. It'd work a lot better if dataview were the first column; > I might be misunderstanding you, but I assume that you are suggesting an > index on (dataview, valuetimestamp). > We have that index, it is the primary key. For some reason it isn't > being selected. I don't think that index can be used for your original query. It could only be used if "channel" is unique in "valueseries" and you'd written the query as: select * from datavalue where dataview = (select id from valueseries where channel = 752433) ORDER BY VALUETIMESTAMP DESC FETCH FIRST ROW only; that would allow a backwards index scan using the (dataview, valuetimestamp) index. Because you're using the IN clause to possibly look for multiple "dataview" values matching the given "channel", the index range scan does not have a single point to start at. What you've done with the LATERAL query allows the index to be scanned once for each "valueseries" row with a "channel" value matching your WHERE clause. I guess "channel" must not be the primary key to "valueseries" and that's why you use an IN(). The above query would return an error if multiple rows were returned by the subquery. David
