firebase-android-sdk icon indicating copy to clipboard operation
firebase-android-sdk copied to clipboard
verified publisher icon firebase

[Firestore Performance Issue] Android query.get() takes over 20s while iOS completes under 1s for identical query

Open sana-20 opened this issue 5 months ago • 17 comments

[READ] Step 1: Are you in the right place?

Issues filed here should be about bugs in the code in this repository. If you have a general question, need help debugging, or fall into some other category use one of these other channels:

  • For general technical questions, post a question on StackOverflow with the firebase tag.
  • For general Firebase discussion, use the firebase-talk google group.
  • For help troubleshooting your application that does not fall under one of the above categories, reach out to the personalized Firebase support channel.

[REQUIRED] Step 2: Describe your environment

  • Android Studio version: Android Studio Narwhal | 2025.1.1 Patch 1
  • Firebase Component: Firestore
  • Component version: Bom 34.0.0 (firestore 26.0.0)

[REQUIRED] Step 3: Describe the problem

Steps to reproduce:

On both Android and iOS, we fetch Firestore chat messages using the same query logic. However, on Android, calling query.get() takes over 20 seconds, while on iOS it returns in under 1 second.

Here’s how the flow works on Android:

  1. We connect to Firestore using the given database ID.
  2. Then we call observeChat(), which attaches a snapshot listener to a collectionGroup("messages").
  3. Next, we call fetchUsersMeta() to get metadata for a user.
  4. Once that completes, we perform fetchMessages() in multiple chat rooms.

All Firestore indexes are properly set. There are no other signs of network or device issues. No Firebase exceptions or warnings are thrown. The exact same document set is returned quickly on iOS but takes an abnormally long time on Android.

Example log output:

✅ Test 1: Parallel fetchMessages() calls

getUsersMeta duration: 147ms
fetchMessages - chatroom-id-1  → 21.20s, size=4
fetchMessages - chatroom-id-2  → 21.20s, size=24
fetchMessages - chatroom-id-3  → 21.22s, size=89
fetchMessages - chatroom-id-4  → 21.25s, size=638
fetchMessages - chatroom-id-5  → 21.46s, size=100
fetchMessages - chatroom-id-6  → 21.49s, size=77
fetchMessages - chatroom-id-7  → 21.51s, size=0 ⚠️
fetchMessages - chatroom-id-8  → 21.51s, size=6,790 ⚠️
fetchMessages - chatroom-id-9  → 22.99s, size=1,523
fetchMessages - chatroom-id-10 → 23.39s, size=4,308
fetchMessages - chatroom-id-11 → 24.46s, size=230
fetchMessages - chatroom-id-12 → 24.49s, size=2,181
fetchMessages - chatroom-id-13 → 24.96s, size=417
fetchMessages - chatroom-id-14 → 25.04s, size=57
fetchMessages - chatroom-id-15 → 25.04s, size=927
fetchMessages - chatroom-id-16 → 25.22s, size=120
fetchMessages - chatroom-id-17 → 25.26s, size=574
fetchMessages - chatroom-id-18 → 25.40s, size=691
fetchMessages - chatroom-id-19 → 25.51s, size=452
fetchMessages - chatroom-id-20 → 25.59s, size=76
fetchMessages - chatroom-id-21 → 25.61s, size=0 ⚠️
fetchMessages - chatroom-id-22 → 25.60s, size=468
fetchMessages - chatroom-id-23 → 25.69s, size=527
fetchMessages - chatroom-id-24 → 25.84s, size=618
fetchMessages - chatroom-id-25 → 25.96s, size=26
fetchMessages - chatroom-id-26 → 25.96s, size=58
fetchMessages - chatroom-id-27 → 25.97s, size=0 ⚠️
  • Total execution time exceeds 25 seconds, which is unexpectedly long for parallel processing.
  • Some requests with 0 results (e.g., chatroom-id-7, chatroom-id-21, chatroom-id-27) took over 21–25 seconds, indicating inefficient request execution or blocking behavior.
  • Fetching a large dataset (e.g., chatroom-id-8 with 6,790 messages) also took over 21 seconds, which is suspicious given its read-only nature and Firestore’s indexing performance.

✅ Test 2: Sequential fetchMessages() calls

getUsersMeta duration: 188ms

fetchMessages - chatroom-id-8  → 7.69s, size=6,790 ⚠️
fetchMessages - chatroom-id-14 → 109ms, size=57
fetchMessages - chatroom-id-9  → 1.41s, size=1,523
fetchMessages - chatroom-id-25 → 132ms, size=26
fetchMessages - chatroom-id-1  → 56ms, size=4
fetchMessages - chatroom-id-13 → 411ms, size=417
fetchMessages - chatroom-id-20 → 127ms, size=76
fetchMessages - chatroom-id-7  → 52ms, size=0
fetchMessages - chatroom-id-11 → 281ms, size=230
fetchMessages - chatroom-id-17 → 615ms, size=580
fetchMessages - chatroom-id-23 → 518ms, size=527
fetchMessages - chatroom-id-4  → 632ms, size=638
fetchMessages - chatroom-id-21 → 36ms, size=0
fetchMessages - chatroom-id-24 → 585ms, size=625
fetchMessages - chatroom-id-2  → 104ms, size=24
fetchMessages - chatroom-id-16 → 202ms, size=120
fetchMessages - chatroom-id-5  → 145ms, size=100
fetchMessages - chatroom-id-15 → 842ms, size=927
fetchMessages - chatroom-id-6  → 131ms, size=77
fetchMessages - chatroom-id-18 → 611ms, size=691
fetchMessages - chatroom-id-3  → 160ms, size=89
fetchMessages - chatroom-id-22 → 434ms, size=468
fetchMessages - chatroom-id-10 → 4.58s, size=4,308 ⚠️
fetchMessages - chatroom-id-19 → 472ms, size=452
fetchMessages - chatroom-id-26 → 137ms, size=58
fetchMessages - chatroom-id-27 → 33ms, size=0
fetchMessages - chatroom-id-12 → 2.07s, size=2,181 ⚠️
  • Sequential calls showed better performance than parallel, which is counterintuitive.
  • Large datasets like chatroom-id-8 (6,790 messages) took 7.69s, and chatroom-id-10 (4,308 messages) took 4.58s — performance scales linearly with message count.
  • iOS fetches the same 6,790 messages in under 1 second, highlighting abnormal slowness in Android Firestore.

✅ Test 3: Parallel fetchMessages() with All Results = 0

getUsersMeta duration: 3.370s

fetchMessages duration: 731.563ms, size=0
fetchMessages duration: 726.474ms, size=0
fetchMessages duration: 729.928ms, size=0
fetchMessages duration: 735.729ms, size=0
fetchMessages duration: 740.561ms, size=0
fetchMessages duration: 746.807ms, size=0
fetchMessages duration: 1.368s, size=0
fetchMessages duration: 1.365s, size=0
fetchMessages duration: 1.362s, size=0
fetchMessages duration: 1.364s, size=0
fetchMessages duration: 1.365s, size=0
fetchMessages duration: 1.372s, size=0
fetchMessages duration: 1.366s, size=0
fetchMessages duration: 1.368s, size=0
fetchMessages duration: 1.368s, size=0
fetchMessages duration: 1.371s, size=0
fetchMessages duration: 1.372s, size=0
fetchMessages duration: 1.372s, size=0
fetchMessages duration: 1.372s, size=0
fetchMessages duration: 1.374s, size=0
fetchMessages duration: 1.373s, size=0
fetchMessages duration: 1.373s, size=0
fetchMessages duration: 1.370s, size=0
fetchMessages duration: 1.355s, size=0
fetchMessages duration: 1.369s, size=0
fetchMessages duration: 1.363s, size=0
fetchMessages duration: 1.371s, size=0
  • Even though all results are empty (size=0), fetch durations range from 730ms to over 1.37s, which is inefficient.
  • Moreover, requests toward the end of the list consistently take longer than earlier ones — e.g., first fetch is ~730ms, but the last ones are ~1.37s.
  • Also, getUsersMeta() took 3.37s, which is unusually slow.

✅ Test 4: Sequential fetchMessages() with All Results = 0

getUsersMeta duration: 6.179s

fetchMessages duration: 397.815ms, size=0
fetchMessages duration: 46.808ms, size=0
fetchMessages duration: 130.414ms, size=0
fetchMessages duration: 39.804ms, size=0
fetchMessages duration: 46.340ms, size=0
fetchMessages duration: 73.890ms, size=0
fetchMessages duration: 48.975ms, size=0
fetchMessages duration: 38.574ms, size=0
fetchMessages duration: 66.986ms, size=0
fetchMessages duration: 77.339ms, size=0
fetchMessages duration: 68.151ms, size=0
fetchMessages duration: 79.475ms, size=0
fetchMessages duration: 45.685ms, size=0
fetchMessages duration: 91.039ms, size=0
fetchMessages duration: 41.715ms, size=0
fetchMessages duration: 46.727ms, size=0
fetchMessages duration: 66.391ms, size=0
fetchMessages duration: 100.449ms, size=0
fetchMessages duration: 43.368ms, size=0
fetchMessages duration: 92.011ms, size=0
fetchMessages duration: 47.804ms, size=0
fetchMessages duration: 74.602ms, size=0
fetchMessages duration: 288.936ms, size=0
fetchMessages duration: 62.520ms, size=0
fetchMessages duration: 56.893ms, size=0
fetchMessages duration: 58.928ms, size=0
fetchMessages duration: 166.459ms, size=0
  • Fetches are fast and mostly under 100ms, which is ideal for empty results.
  • However, getUsersMeta() took 6.18s, which is the slowest among all tests and lacks a clear reason.
  • In both Test 3 and 4, despite all fetch results being empty, getUsersMeta() becomes significantly slower — a behavior not observed in Test 1 and 2 —

Relevant Code:


fun connect() {
    firestore = Firebase.firestore(database = "$DB_CHAT$databaseId")
    observeChat()
} 

private fun observeChat() {
    val query = firestore.collectionGroup("messages")
        .whereGreaterThanOrEqualTo("updatedAt", Date().time)
        .where(Filter.inArray("roomIds", listOf("id1", "id2", "id3")))
        .where(
            Filter.or(
                Filter.equalTo("userId", "user-id-1"),
                Filter.inArray("userType", listOf("bot", "star"))
            )
        )
        .orderBy("updatedAt", Query.Direction.DESCENDING)

    query.addSnapshotListener { value, error ->
        // Snapshot updates handled here
    }
}

suspend fun fetchUsersMeta() {
    val query = firestore.collectionGroup("users")
        .whereEqualTo("userId", "user-id-1")

    val (snapshot, duration) = measureTimedValue { query.get().await() }
    println("getUsersMeta: $duration")
}

suspend fun fetchMessages() {
    val query = firestore.collection("chat/chatroom-id-1/messages")
        .where(
            Filter.or(
                Filter.equalTo("userId", "user-id-1"),
                Filter.inArray("userType", listOf("bot", "star"))
            )
        )
        .orderBy("createdAt", Query.Direction.DESCENDING)
        .whereGreaterThan("createdAt", Date().time - 24 * 60 * 60 * 1000)

    val (snapshot, duration) = measureTimedValue { query.get().await() }
    println("fetchMessages: $duration, size=${snapshot.documents.size}")
}

sana-20 avatar Jul 30 '25 00:07 sana-20

Thanks for reporting @sana-20. I'll take a look

ehsannas avatar Aug 07 '25 00:08 ehsannas

@sana-20 These numbers are surprising indeed. Could you enable debug logs (FirebaseFirestore.setLoggingEnabled(true);) and share the logs?

ehsannas avatar Aug 09 '25 00:08 ehsannas

@ehsannas You can view and download the log file from the Google Drive link below. https://drive.google.com/drive/folders/10lDGY8JuufmKv0Yb_OC91j3OwvrtvNG6?usp=sharing

The log file contains sensitive information, including Firestore queries and other proprietary company details, which cannot be publicly disclosed. so please provide your email address, so that I can verify your request.

sana-20 avatar Aug 11 '25 07:08 sana-20

Thanks @sana-20 . You should get an access request from my [at][google][dot][com] account.

ehsannas avatar Aug 12 '25 17:08 ehsannas

Thanks, @ehsannas. I've just granted access to your account!

sana-20 avatar Aug 13 '25 04:08 sana-20

Thanks. I've downloaded it. I'll investigate.

ehsannas avatar Aug 13 '25 21:08 ehsannas

@sana-20 I looked through the log. Unfortunately nothing jumps out as wrong. It seems that the client is doing normal operations -- many of them.

For example, In the log I see 60 document_delete messages (the server notifying the client which documents were deleted), and 1731 document_change messages (the server sending new document content to the client) before the very first occurrence offetchMessages. The client uses all of this information to keep the local cache state up-to-date with respect to the existing queries/snapshot listeners.

If your client is in a blank state and it's receiving a large number of documents, it wouldn't be surprising that receiving 4000+ documents may take time. Do you observe the same behavior the second time you run your query? (presumably the second time should be much faster since the client's cache is already populated, so minimal bookkeeping will be needed). Similarly, it's possible that your iOS device's cache was in a more up-to-date state and therefore it didn't need to synchronize so many documents. Was that the case? (for example if you make new documents in the iOS device, its cache will have the new documents in it).

ehsannas avatar Aug 20 '25 18:08 ehsannas

@ehsannas Sorry, but it seems parts of the logs I sent earlier were missing. I’ve re-uploaded the full logs to Google Drive as 250825_log.txt—could you please take another look?

In addition to the Firestore logs, I also added my own debug logs with the FirestoreService tag (similar to the example log output above). If you search for FirestoreService, you’ll see, per hostChatRoomId, the fetch duration and the size of the documents retrieved.

For the worst case, fetching only 94 documents took more than 52 seconds:

fetchMessages: hostChatRoomId=4f84ebfd-59a7-4466-866a-6a3d2c08bdd7 duration=52.641079302s size=94

Also, as you can see from the logs, the first entry (app start) was at 15:59:33, and the last entry (completion of Firestore fetch operations for each chat room) was at 16:02:58. In other words, due to this Firestore issue, the initial app loading takes more than 3 minutes 30 seconds in my app.

Thanks in advance for reviewing the updated logs.

sana-20 avatar Aug 25 '25 07:08 sana-20

After reviewing this issue, I have 3 proposals:

1. Deleted Documents Performance Fix

The 26.0.1 (Sept 18, 2025, BoM version 34.3.0) release of the Android Cloud Firestore SDK contains PR #7301, which fixes a performance issue when a collection has many deleted documents (tombstones). If your collections contain a large number of deleted documents then this fix could mitigate that specific problem.

2. Isolate Network vs. Cache

To pinpoint if the slow query performance is backend/network-related or due to client-side cache management, please test with the network disabled:

  1. Run your app once normally to populate the local cache.
  2. Call FirebaseFirestore.disableNetwork() immediately after initializing FirebaseFirestore.
  3. Re-run the slow queries.
  • If still slow: The issue is likely client-side (cache management).
  • If performance improves: The issue suggests a problem with the remote end or communication.

3. Provide SQLite Database

Would you be willing to share a copy of the Firestore SQLite database from your Android app's "application data" directory?

  1. Run your app to reproduce the slow queries.
  2. Use adb to copy the database from the device/emulator to your local machine.
  3. Upload the files for @dconeybe or @ehsannas to download. This will allow us to run queries against your cached data to identify bottlenecks.

dconeybe avatar Sep 12 '25 14:09 dconeybe

Update: The performance fix https://github.com/firebase/firebase-android-sdk/pull/7301 for collections with a large number of deleted documents was just released on Sept 18, 2025 in the Android Cloud Firestore SDK version 26.0.1 (BoM version 34.3.0, https://firebase.google.com/support/release-notes/android#2025-09-18). If your collections contain a large number of deleted documents then this fix could mitigate that specific problem.

I also have a series of 4 PRs that should dramatically improve the query performance, but they were not ready for inclusion in the 26.0.1 (BoM version 34.3.0, Sept 18, 2025) release. They are expected to be included in the next release, planned for mid-October 2025 (3-4 weeks from now). I will post another update as a comment on this issue once those PRs are merged, and again once there is a release containing the fixes.

dconeybe avatar Sep 19 '25 21:09 dconeybe

@dconeybe Thank you for your detailed response. As you suggested, I upgraded to version 34.3.0, and I did notice a slight improvement in performance. The query time has decreased from 6 seconds to 3 seconds. However, 3 seconds is still slower compared to iOS, so I think we’ll need to wait for the next release next month and see if the performance drops further to meet our team’s expectations.

Also, as you recommended, I tried calling FirebaseFirestore.disableNetwork(), and the execution time was reduced by about half — from 3 seconds to around 1.5 second. How should we interpret this result? It doesn’t quite feel like a significant or dramatic improvement.

Lastly, I uploaded the SQLite database you requested to the following link for your review. Google Drive Folder

sana-20 avatar Sep 22 '25 02:09 sana-20

@sana-20 Thanks for the report! That 50% speed-up (6s to 3s) just from upgrading to v34.3.0 is great to see. I know 3s is still slow, but we're moving in the right direction.

As discussed, the next firebase-android-sdk release has more performance improvements that should get this under 1s. While Android will likely always be a bit slower than the C++ iOS SDK (due to differences such as sqlite vs. leveldb, utf-16 vs. utf-8, Java vs. C++), it's a useful benchmark.

The 1.5s improvement you see from disableNetwork() is very interesting. It suggests that the delay, at least in part, is waiting for results from the backend. Consider using a snapshot listener instead of get(). The listener will immediately return the cached data (letting you populate your UI instantly), and then provide a second update once it gets the results from the backend. You can check snapshot.getMetadata().isFromCache() to know which result you're handling. This usually gives the best user experience: fast initial data, followed by fresh data.

I'll also take a look at your provided sqlite database.

dconeybe avatar Sep 22 '25 15:09 dconeybe

@sana-20 I ran the following query on the sqlite database that you provided and the query ran in a blazingly-fast 7-10 milliseconds on a Pixel 7 Pro, with com.google.firebase:firebase-firestore:26.0.1 (the latest release as of Sept 22, 2025):

firestore
  .collection("chat/f46029c4-ec05-4f33-90ab-d46701322b12/messages")
  .where(
    Filter.or(
      Filter.equalTo("userId", "user-id-1"),
      Filter.inArray("userType", listOf("bot", "star")),
    )
  )
  .orderBy("createdAt", Query.Direction.DESCENDING)
  // Apply a filter that matches roughly half of the documents in the collection,
  // based on the query presented in the OP.
  .whereGreaterThan("createdAt", Timestamp(seconds = 1757657349, nanoseconds = 948000000))
  // Run against cache to eliminate effects of network latency,
  // and also because I don't have the API key to run against the real project.
  .get(Source.CACHE)
  .await()

I noticed in the sqlite database you provided that there appear to be 10 different chat messages each with 100 documents. This is a pretty small database. Is this the exact database that you can use to reproduce these 1.5-second to 3.0-second query times?

Also, make sure that you're building the "release" build configuration of your app. The performance of "debug" builds is generally substantially slower, and we generally don't address performance issues in "debug" builds unless they are egregiously slow and detract from developer experience.

Image

dconeybe avatar Sep 22 '25 20:09 dconeybe

Update: com.google.firebase:firebase-bom:34.4.0 (which includes com.google.firebase:firebase-firestore:26.0.2) was released a few days ago on Oct 09, 2025 (release notes) which includes 4 query performance improvements. Please re-run your performance tests using this latest version of the Android SDK and let us know how the new performance numbers look. Remember to use a "release" version of your app (as outlined in the previous comment) to get the most meaningful performance numbers.

dconeybe avatar Oct 14 '25 15:10 dconeybe

@dconeybe Thank you for your detailed feedback.

First of all, I apologize for the confusion caused by the SQLite database I previously provided — it was extracted from an incorrect environment. I’ve now uploaded a new one with this link. I hope this is what you were looking for. It contains the actual production data and should provide a more realistic view of the performance issue.

I also ran the performance tests again after updating to BoM 34.4.0 (Firestore 26.0.2) and building in release mode. Below are the results for comparison:

Name Version Query Type Min Max
Test1 34.0.0 Without whereGreaterThan (only limit 100 docs) 2s 7s
Test2 34.0.0 With whereGreaterThan 27s 32s
Test3 34.4.0 Without whereGreaterThan (only limit 100 docs) 1.4s 1.6s
Test4 34.4.0 With whereGreaterThan 6.5s 7.4s
log output

Test1 log output: chatroom-id-1 -> 2535ms, size=100 chatroom-id-2 -> 2549ms, size=21 chatroom-id-3 -> 2553ms, size=100 chatroom-id-4 -> 2566ms, size=100 chatroom-id-5 -> 2573ms, size=100 chatroom-id-6 -> 2581ms, size=100 chatroom-id-7 -> 2589ms, size=100 chatroom-id-8 -> 2596ms, size=100 chatroom-id-9 -> 2603ms, size=100 chatroom-id-10 -> 2613ms, size=100 chatroom-id-11 -> 2627ms, size=100 chatroom-id-12 -> 2637ms, size=100 chatroom-id-13 -> 2642ms, size=100 chatroom-id-14 -> 6885ms, size=100 chatroom-id-15 -> 6898ms, size=100 chatroom-id-16 -> 6910ms, size=100 chatroom-id-17 -> 6922ms, size=100 chatroom-id-18 -> 6934ms, size=100 chatroom-id-19 -> 6943ms, size=100 chatroom-id-20 -> 6954ms, size=100 chatroom-id-21 -> 6961ms, size=100 chatroom-id-22 -> 6966ms, size=100 chatroom-id-23 -> 6980ms, size=100 chatroom-id-24 -> 6986ms, size=100 chatroom-id-25 -> 6994ms, size=100 chatroom-id-26 -> 7000ms, size=100 chatroom-id-27 -> 7007ms, size=100

Test2 log output: chatroom-id-1 -> 31145ms, size=1002 chatroom-id-2 -> 31266ms, size=1795 chatroom-id-3 -> 31423ms, size=340 chatroom-id-4 -> 31445ms, size=187 chatroom-id-5 -> 31464ms, size=7430 chatroom-id-6 -> 32050ms, size=1 chatroom-id-7 -> 32052ms, size=107 chatroom-id-8 -> 32064ms, size=1125 chatroom-id-9 -> 32147ms, size=2442 chatroom-id-10 -> 32328ms, size=76 chatroom-id-11 -> 32335ms, size=1 chatroom-id-12 -> 32335ms, size=404 chatroom-id-13 -> 32364ms, size=1318 chatroom-id-14 -> 32466ms, size=18 chatroom-id-15 -> 32468ms, size=260 chatroom-id-16 -> 32486ms, size=1896 chatroom-id-17 -> 32613ms, size=174 chatroom-id-18 -> 32625ms, size=4 chatroom-id-19 -> 32625ms, size=840 chatroom-id-20 -> 32683ms, size=1 chatroom-id-21 -> 32684ms, size=270 chatroom-id-22 -> 32701ms, size=976 chatroom-id-23 -> 32781ms, size=223 chatroom-id-24 -> 32796ms, size=1 chatroom-id-25 -> 32796ms, size=83 chatroom-id-26 -> 32801ms, size=430 chatroom-id-27 -> 32826ms, size=1

Test3 log output: chatroom-id-1 -> 1445ms, size=100 chatroom-id-2 -> 1480ms, size=100 chatroom-id-3 -> 1492ms, size=100 chatroom-id-4 -> 1497ms, size=100 chatroom-id-5 -> 1513ms, size=100 chatroom-id-6 -> 1514ms, size=100 chatroom-id-7 -> 1522ms, size=100 chatroom-id-8 -> 1524ms, size=100 chatroom-id-9 -> 1532ms, size=100 chatroom-id-10 -> 1549ms, size=100 chatroom-id-11 -> 1552ms, size=100 chatroom-id-12 -> 1545ms, size=100 chatroom-id-13 -> 1559ms, size=100 chatroom-id-14 -> 1566ms, size=100 chatroom-id-15 -> 1558ms, size=100 chatroom-id-16 -> 1562ms, size=100 chatroom-id-17 -> 1565ms, size=100 chatroom-id-18 -> 1575ms, size=100 chatroom-id-19 -> 1575ms, size=100 chatroom-id-20 -> 1579ms, size=100 chatroom-id-21 -> 1584ms, size=100 chatroom-id-22 -> 1597ms, size=100 chatroom-id-23 -> 1603ms, size=21 chatroom-id-24 -> 1591ms, size=100 chatroom-id-25 -> 1600ms, size=100 chatroom-id-26 -> 1598ms, size=100 chatroom-id-27 -> 1603ms, size=100

Test4 log output: chatroom-id-1 -> 6910ms, size=1 chatroom-id-2 -> 6911ms, size=404 chatroom-id-3 -> 6938ms, size=1 chatroom-id-4 -> 6938ms, size=18 chatroom-id-5 -> 6942ms, size=1002 chatroom-id-6 -> 7021ms, size=187 chatroom-id-7 -> 7036ms, size=108 chatroom-id-8 -> 7032ms, size=1318 chatroom-id-9 -> 7094ms, size=1795 chatroom-id-10 -> 7172ms, size=340 chatroom-id-11 -> 7190ms, size=7430 chatroom-id-12 -> 7497ms, size=1125 chatroom-id-13 -> 7531ms, size=1 chatroom-id-14 -> 7531ms, size=1896 chatroom-id-15 -> 7629ms, size=4 chatroom-id-16 -> 7620ms, size=2443 chatroom-id-17 -> 7710ms, size=840 chatroom-id-18 -> 7745ms, size=223 chatroom-id-19 -> 7742ms, size=260 chatroom-id-20 -> 7761ms, size=430 chatroom-id-21 -> 7783ms, size=1 chatroom-id-22 -> 7774ms, size=76 chatroom-id-23 -> 7790ms, size=83 chatroom-id-24 -> 7795ms, size=1 chatroom-id-25 -> 7785ms, size=976 chatroom-id-26 -> 7846ms, size=270 chatroom-id-27 -> 7856ms, size=174

While the improvement with whereGreaterThan is significant in 34.4.0, the query is still quite slow compared to expectations. Please take a look at the new database when you have a chance. I’d really appreciate any further insights or suggestions you might have. Thanks again for your continued support and for taking the time to investigate this issue so thoroughly.

sana-20 avatar Oct 15 '25 09:10 sana-20

Thank you for the detailed response and the update sqlite database. I'm glad you at least noticed some substantial improvements, although, I see how this doesn't meet your expectations.

I will take a look again in the near future. I have some other work to wrap up first. I'll reply back with my updates. Thank you for your patience.

dconeybe avatar Oct 16 '25 19:10 dconeybe

I confirm to have similar performance slowness and it is pretty impactful for my production. I hope there will be some other performance improvements that can bring down the wait time.

RohovDmytro avatar Nov 08 '25 17:11 RohovDmytro