Optimize query with JOIN

Hello @vsr1! The query you provided has similar execution times to the first one, so no noticeable improvements (yesterday the system was under high load, now both queries run between 1.5-3s).

I guess there must be something we’re not doing correctly. With a fairly low cardinality of documents (in the tens of thousands), should we expect a query to take this long with proper indices?

As for your last comment, I am not sure what you mean by “original queries”, could you elaborate? Also, the 4.6.0 is a Developer Preview (Community Edition), meaning that may not be possible to upgrade at this time. Thank you for your help, looking forward for your feedback!

Hello @manusyone, The left side of JOIN produces 17225 items (from post 5). Left side uses covered index but not right side of Join. It needs to fetch almost 17225 documents and it taking time due to nested loop join.

I meant “original queries” are the query in post 19. In 4.6.x we have block nested loop join.

I see. We’ll be trying 4.6.x to check if we can improve query performance. Thank you for your help!