Optimization for filtering on long joins

[jackfischer]

Developers just cannot get enough of writing queries like this

select Agent {
    id
} filter (
    .editVersion.customPrompt.questionPresets.id = <uuid>$questionPresetId
)

versus queries like this

select QuestionPreset {
  agentId := .prompt.agentVersion.agent.id
}
filter .id = <uuid>$questionPresetId

This one was 800ms vs 0.1ms. Unless there's a deep reason I'm not seeing that it should materialize the full agent -> editVersion -> customPrompt -> questionPresets join, we should find a way to prevent that.

[scotttrinh]

I'm 100% for good optimizations that improve a large number of queries easily, so 👍 if this is one of those cases.

I think we need to find a good way to instill the principle of "find the smallest set possible first", since that is what is happening here. We should build on this as the foundation of a future optimizations/best-practices documentation push. In this case, a single QuestPreset object is the smallest possible set (at most one), and then going from there to find the agent ID. But I think most people's instinct is "I want the Agent.id, so select Agent...".

I wonder if pedagogically this is a good way to structure this, since it keeps the "I'm selecting an Agent", but demonstrates the "start with the smallest set" mentality:

with
  QUESTION_PRESET := (select QuestionPreset filter .id = <uuid>$questionPresetId),
  AGENT := QUESTION_PRESET.prompt.agentVersion.agent,
select AGENT {
  id,
};

[jackfischer]

Turns out the UI really conceptually wants to do this too.

[scotttrinh]

Yeah, although to be fair to the UI, it isn't going to rewrite your query in a more optimal way. I think my same suggestion applies here: when you go to the UI looking for information about an object where you're trying to find the set of objects filtered by some link is you go first to the filtering object (so the target object of .collections in this case), add the filter there and then follow the backlink (it exists right? 😅 ) to the set of objects. So same internal optimization: start your search with the smallest set first.

If we could eventually have the original query just fast-path to this more optimized version without having to rewrite the query, definitely and unconditionally better, but I do think there is value in learning this discipline of narrowing the search space as early and aggressively as possible.