Poor query performance as traversal width increases

[e-compton-bb]

Poor query performance as traversal width increases

[e-compton-bb]

After some more digging, the main problem here seems to be the breadth-first search in checkExpandSubject. It grabs every Project tuple from the DB and then it starts looking through each one for the user. Then each lookup is a separate query which has it's own round trip time. Which explains why it's so much slower in a real database.

[aeneasr]

Hey Edward, I believe that this is not yet optimized properly. If you have good ideas how this could be optimized we're happy to hear them!

[e-compton-bb]

Could switch from a breadth-first search to a depth-first search. In this case the relationship chain we're trying to find is: Clip -> Project -> User, so by enumerating all the Projects first we always get worst-case time.

There's also a lot of chattiness between the container and the DB, so if more work could be offloaded to the DB it could make a big difference. This seems like a really good use-case for Postgres's WITH RECURSIVE clauses, although the current implementation seems to be independent of particular SQL versions
https://www.dylanpaulus.com/posts/postgres-is-a-graph-database