Database response time

Hi, using 1.2.1, I have a collection with more than 100k documents. The collection is with document security. I observe that when i make a simple request to get 100 documents unsorted with no queries, the server response is a lot slowerthan if I make a request with a query that narrows the found set to a few dozens of documents (5 to 10 seconds instead of 1 second) . Is it a normal behavior ? I would have expected that requests without query or sort, the response would be fast. Thank you.

It is a normal behaviour, because the load increases but depends on how much time are you getting the reply in - is it a lot of minutes? - querying is easier with indexing (and a lot faster)

https://appwrite.io/docs/databases#indexes

Everythings is relative when time is under consideration. I'm talking here about 5 to 10 seconds of respons time which is not much in certain circonstances but can be very long when the user waits for an answer on this app. There are multiple indexes on that table. We can't anyway make a query without indexes. However, I just observe that a simple listDocuments request with NO query, is a lot slower than a query on the same collection with fields using indexes where the foundset is something like a few dozens of documents.

I understand - yes we require indexes for querying.

Thank you for your feedback. My english is certainly not good enough for me to be clear, but I feel that we miss the point here. This is understood that we require indexes for querying. But what about the request performances with more than 100k documents with document security when we DO NOT make a query on attributes. Something like : await databases.listDocuments(databaseId: databaseId,collectionId: collectionId, queries: [Query.limit(100)]). returns 100 documents but is a lot slower than the following request await databases.listDocuments(databaseId: databaseId,collectionId: collectionId, queries: [Query.limit(100), Query.search('search', searchFilter)]) when the filter on the search attribute reduces the found set to let's say about 100 documents. Is there something I miss about this topic? Thank you

I understand this part surely clearly - listDocuments with no filter queries should use index for sorting and probably should give you newest first.
When generating collection, we automatically create this index. We can't understand why such a query would be slower than same one with search filter 🤔

It might be related to your setup probably in some way? (taking a guess) Query on 100k documents should not take 2 seconds. Can you share details about your server? (To check for CPU and RAM)

My setup is 4 CPUs and 4Gb of RAM and this is dev server so almost no other users

I'm wondering if the issue comes from the document security. If I remove the document security and set the permission on the collection to all users then the response without attribute query is a lot faster

This is definitely coming from the document permission.

We go from 0.18 seconds for 100 records with no attribute query with collection permission to almost 13 seconds for 100 records with no attribute query with document permission activated.

this is 70 times slower...

But when I add a query on the 'search' attribute in such a way that it returns only 1 document, then the performances are very similar between collection permission (0.12 seconds) and document permission (0.16 seconds)

Document security do make queries slower but.. not this much ..Might be worth to try to reproduce.. Spin up local instance, create 100k documents, enable documentSecurity and see how quick queries are compared to documentSecurity=false

Also, can you try and share a bit more details about the kind of data you're dealing with and/or share the data with us via DMs (if not confidential) so we can try and reproduce it

Thank you for your proposals. I'll work on this once I manage to free some time.

ok sure, let us know!