Loading chats in Delta Chat Desktop got much slower after upgrade to core 1.156.0

[link2xt]

I have git bisect-ed the problem and it seems to be commit deltachat/deltachat-desktop@c60aa90

Core was updated from 1.155.6 to 1.156.0. Maybe it is the core issue.

[link2xt]

In dev console I get messages "renderer/stores/MessageListStore loadChat took 1770.0999999940395".

get_messages takes more than 1.5 s is not ok.

Will try to bisect the core.

[link2xt]

Maybe slowdown is due to #6554
Anyway, bisecting it.

[link2xt]

Saved messages were added in #5606

If the issue is that get_saved_msg_id is slow, then we need an index on starred column for quick search over it.

[link2xt]

I did not bisect, but checked that this simple patch speeds up chat loading visibly:

diff --git a/deltachat-jsonrpc/src/api/types/message.rs b/deltachat-jsonrpc/src/api/types/message.rs
index a60ee15a5..4f6bda940 100644
--- a/deltachat-jsonrpc/src/api/types/message.rs
+++ b/deltachat-jsonrpc/src/api/types/message.rs
@@ -267,10 +267,7 @@ pub async fn from_msg_id(context: &Context, msg_id: MsgId) -> Result<Option<Self
                 .await?
                 .map(|id| id.to_u32()),

-            saved_message_id: message
-                .get_saved_msg_id(context)
-                .await?
-                .map(|id| id.to_u32()),
+            saved_message_id: None,

             reactions,

So we need an index (or an explicit backlink, but if a single migration adding an index works then better an index).

[link2xt]

I manually did CREATE INDEX link2xt_starred_index ON msgs (starred) and it fixed the problem.

[link2xt]

I have made this testcase for SQLite query planner:

#!/bin/sh
{
cat <<EOF
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE msgs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    txt TEXT NOT NULL,
    starred INTEGER DEFAULT 0,
    chat_id INTEGER DEFAULT 0
);
EOF

for i in $(seq 300); do
cat <<EOF
INSERT INTO msgs (txt) VALUES('foobar');
EOF
done

cat <<EOF
CREATE INDEX my_index ON msgs (starred);
COMMIT;

-- At this point query planner decides to use the index.
EXPLAIN QUERY PLAN SELECT id FROM msgs WHERE starred=? AND chat_id!=?;

ANALYZE;

-- After analyzing, query planner stops using the index and scans the whole msgs table.
EXPLAIN QUERY PLAN SELECT id FROM msgs WHERE starred=? AND chat_id!=?;

-- Now we have to resort to forcing using the index because query planner makes wrong decision.
EXPLAIN QUERY PLAN SELECT id FROM msgs INDEXED BY my_index WHERE starred=? AND chat_id!=?;
EOF
} | sqlite3

With SQLite 3.49.1 2025-02-18 13:38:58 873d4e274b4988d260ba8354a9718324a1c26187a4ab4c1cc0227c03d0f1alt1 (64-bit) this prints out:

QUERY PLAN
`--SEARCH msgs USING INDEX my_index (starred=?)
QUERY PLAN
`--SCAN msgs
QUERY PLAN
`--SEARCH msgs USING INDEX my_index (starred=?)

[link2xt]

I have reported this to SQLite forum: https://sqlite.org/forum/forumpost/49fd145748

[link2xt]

I wrote more here: https://sqlite.org/forum/forumpost/edcd4ecddb
tl;dr this starred column should have been a separate table.