Dmitry Slabko

In our case with MS SQL the latter query (select with joins from select) could not complete at all. Either PostgreSQL and MS SQL use very different ways to optimize...

@roji - I can send to you the two complete queries, as they are a bit larger then I initially posted with their execution plans.

This is the full linq query: ```c# from navObject in Context.NavObjects join vessel in Context.Vessels on navObject.VesselId equals vessel.VesselId from passage in Context.Passages .Where(x => x.VesselId == navObject.VesselId && x.ActualDepartureTime.HasValue...

Ok, some more input on this problem. Here is the linq: ```c# from vessel in Context.Vessels.Where(...) from position in Context.Positions .Where(t => t.VesselId == vessel.VesselId && t.Time s.Time) .Take(1) .DefaultIfEmpty()...

I tried to execute the innermost query, and its performance issue comes from this part: ROW_NUMBER() OVER(PARTITION BY [t].[VesselId] ORDER BY [t].[EventDateTimeUTC] DESC) AS [row] Most likely, bad performance here...

One more note: the table even has an index on VesselId + EventDateTimeUTC columns, yet it does not help.

Ok, yet another note :) I created a composite index in VesselId ASC and EventDateTimeUTC DESC, and with this index the query finally got to behave. However, I do not...

The main difference between the queries as they are now (with 'over partition by') and how they used to be in preview 5 (with 'cross apply') is that now they...

WHERE ([p0].[ObjectId] = [v].[ObjectId]) AND ([p0].[Time]

Well, basically I see only two options for us to work-around this problem, as it is a show-stopper, - either create a stored procedure with the query that works well...