Performance problems in queries

[abul4fia]

What happened?

My Graph was becoming more and more unusable over time, with lags up to 2 or 3 seconds when typing, specially when moving the cursor (either using arrow keys or clicking with the mouse in a different part of the paragraph). The lag is also noticeable in the UI animations for "fold/unfold" the outline. The little triangle starts the animation and freezes in the last frame for two seconds (the rest of the GUI is also frozen and do not respond to mouse or keyboard events). When the GUI "unfreezes", the pending events are processed, causing sometimes unexpected results (editions in the unintended paragraph, etc). This is extremely annoying and makes the program almost unusable.

After some research (using Developer Tools Performance panel), I found that the culprit of these "freezes" were the different queries present in my graph (a call to the function $frontend$db$react$run_custom_queries_when_idle_BANG__$_state_machine__27207__auto____1$$ takes most of the time).

For some reason all my queries are run even if the page being edited has no relationship with those queries. This is in itself a problem that could be optimized. In my view, the queries should be run only if the GUI is showing a query result, otherwise there is no point in running them.

But in addition it looks like those queries take too much time due probably to a different bug. My graph is not so big (326 pages at the moment, and around 10 blocks per page, and the assets folder contains only 35 images, less than 1MB each one). This graph was not built to stress logseq, but it is instead the result of my regular use over the last two and a half months.

Reproduce the Bug

To better assess this problem I created a much smaller graph that can be downloaded from the attachment
Mini_test.tar.gz

It contains only 4 pages and 6 journa files. No assets. No embeds. One of the pages (Already watched.md) contains the following custom query in a nested bullet:

	  #+BEGIN_QUERY
	    {
	        :query [
	            :find (pull ?b [:block/properties])
	            :where
	                       (property ?b :status "watched")
	                       (property ?b :icon "▶️")
	        ]
	        :breadcrumb-show? false
	        :collapsed? false
	    }
	  #+END_QUERY

If the main bullet starts collapsed and I click in the triangle to unfold it, the lag in the animation is noticeable. Also the lag happens again when the bullet is folded. The lag is small, only around 700ms, but is noticeable. This mini-test contains this single query, but my actual graph contains around 10 queries. Since all of them are (unnecessarily) run at each cursor movement, the lag is much worse.

You can use the Performance profiler to discover this:

It can be seen how the framerate drops at two different times, which correspond to the end of the "unfold" and "fold" animations respectively. At those points, a query is run which takes around 700ms to complete.

Are those times normal? I find them extremely large taking into account that the "database" currently contains only 14 blocks.

I also attach the performance profiling data

Profile-20220504T191744.json.gz

Expected Behavior

In a database so small, the time to run a query should be negligible, below the 100ms

In general, queries should not be run if not needed, and in particular they should be postponed to times in which the interface is idle, and not interfere with animations nor typing. They should probably not be run in the UI thread.

Screenshots

Desktop Platform Information

MacOS 12.1.1
Logseq 0.6.7

Mobile Platform Information

No response

Additional Context

No response

[Zengor]

Just want to comment that I'm experiencing issues that appear to be related, on Windows, initially version 0.6.3 then I updated to 0.6.8 to confirm it's still happening.

What I've found is that what makes the queries themselves slow seems to be related to using the "property" rule twice in an advanced query.

That is, just adding the following query in my relatively small graph creates performance issues that wouldn't go away even after deleting the query, until I restarted Logseq:

#+BEGIN_QUERY
{
:title "Active playthroughs"
:query [
	:find (pull ?p [*])
	:where 
	(property ?p :reftype "Videogame") 
	(property ?p :status "Active")]
}
#+END_QUERY

Having just one "property" clause or rewriting the above query with the simple form (and (property reftype "Videogame") (property status "Active")) did not appear to cause the same problems as far as I could tell.

[abul4fia]

@Zengor I think you are right! My queries also used property twice, and as soon as I removed one of the property access (and rebooted logseq) the terrible lag disappeared

Unfortunately in my case the query was a simple query, in which I already had the and syntax. This was my query:

  {{query (and (property status visto) (property icon ▶️))}}

If I remove one of the (property) expressions, the query is completed in microseconds. If I leave the two (property) expressions, it takes around 2 sec.

EDIT. I've found a workaround. I rewrote my simple query as the following advanced query:

  #+BEGIN_QUERY
  {
  :query [
    :find (pull ?p [*])
    :where
      [?p :block/properties ?pr]
      [(get ?pr :status) ?t1]
      [(get ?pr :icon) ?t2]
      [(= "[[visto]]" ?t1)]
      [(= "▶️" ?t2)]
    ]
  }
  #+END_QUERY

This produces the desired results IN 13 ms vs 2070 ms!!

This is a bug that needs to be fixed, but fortunately, now I have this workaround and logseq is useable again.

[Ravenclawer]

is this issue still exist? I'm already an advanced user in obsidian, but recently i got smaller screen so i think i might switch to logseq , it's this performance issue still exist?

[AmeerTaweel]

I use logseq and I have a similar performance issue. Not exactly the same, but similar.

I have my Anime watch-list on logseq, split across multiple files. I also have a main file that queries all of them and shows them in a table. It takes more than 10 seconds to generate the table, hangs the entire application while doing so, and I only have 270 animes split across 30 files.

So definitely the queries are slow.

[alexanderteves]

From my perspective the issue still exists. I am running v0.8.15 on Linux x64 (AppImage) and Android. I have a single page with 300 blocks, each representing a single book. Each block consists of four properties:

type:: book
title:: Adventures of John Doe
year:: 1970
rating:: 4/10

When I use a query for a single property everything is fine, if I run a query for two properties Logseq hangs and needs to be killed:

{{query (and (property type book) (property year 2022))}}

[cpackard]

@abul4fia @Zengor those examples were a lifesaver! I ran into the same issue.

I'm running v0.8.18 on a mac m2 air (macOS v12.6.3). My graph has 1490 pages and 13158 blocks. Running this query leads to the white-screen-of-death, requiring a force-kill of the app:

#+BEGIN_QUERY
{:title [:h2 "Clojure literature notes on a given day"]
 :query [:find (pull ?b [*])
         :in $ ?in-date ?in-tag ?in-type
         :where
         (property ?b :date ?in-date)
         (property ?b :type ?in-type)
         (property ?b :tags ?in-tag)]
 :inputs ["2023-03-25" "clojure" "literature-note"]
 :table-view? true}
#+END_QUERY

Whereas using the advanced-query syntax renders in about half a second:

#+BEGIN_QUERY
{:title [:h2 "Clojure literature notes on a given day"]
 :query [:find (pull ?b [*])
         :in $ ?in-date ?in-tag ?in-type
         :where
         [?b :block/properties ?props]
         [(get ?props :date) ?date]
         [(get ?props :type) ?type]
         [(get ?props :tags) ?tags]
         [(contains? ?date ?in-date)]
         [(= ?type ?in-type)]
         [(contains? ?tags ?in-tag)]]
 :inputs ["2023-03-25" "clojure" "literature-note"]
 :table-view? true}
#+END_QUERY

I made a PR to the docs so people can find this faster in the future.

[logseq-cldwalker]

@abul4fia Hi. This should be fixed in 0.9.3. Can you check?

[cnrpman]

I can remember some fixes toward this.
Feel free to re-open if this issue still exists on latest version.

[cpackard]

On 0.9.4 these two queries now run at roughly the same speed 🚀