@public_image_limited Sorry I didn’t read the whole thread and my comment might be not very useful, but wanted to share something with you regarding efficiency of query operations.
Recently I added links to vulpea-note. And quickly noticed that performance degraded to the point where my day-to-day workflow started to suffer. So I decided to take a path of least resistance and provided some specialized queries (because they kind of solve the problem for my most used patterns). You can find more details in this discussion, but in short the idea was to narrow down the list of notes that I need to query using that horrific SQL with multiple joins, so that performance overhead is not that bad.
I just thought that might not be the most efficient way to do it, but actually it works pretty fine unless you retrieve all nodes.
Just as you say. It does not really scale. Limiting scope is kind of a hack. It turned out that it take 4 seconds to query ~9500 notes. What? 4 seconds??? So I decided to play with completely different approach - a view table, where all the data is already there. So no need to multiply several tables/matrices during query operation, instead build everything on sync. It reduced that time to 1 second! And it still enables Emacs Lisp filtering (e.g. it’s very powerful)! You can find more information in this PR. It is already merged and I am using it for few days and everything become really snappy. Insertion right now has penalty because I am kind of parsing the buffer for the second time together with org-roam, but that will be fixed in future iterations.
Unless, of course, @d12frosted has a magic SQL query which also solves that problem
Not sure if it’s a magic you are talking about, but I hope that helps. Funnily, this all started with discussion around links - Query all nodes that contain links to nodes A __and__ B · d12frosted/vulpea · Discussion #106 · GitHub.
Let me know if you have any questions or ideas 