nodejs-firestore: [Firestore] Throwing 14 UNAVAILABLE: Query timed out on "simple" query

Thanks for stopping by to let us know something could be better!

PLEASE READ: If you have a support contract with Google, please create an issue in the support console instead of filing on GitHub. This will ensure a timely response.

  1. Is this a client library issue or a product issue? Yes its the client library

  2. Did someone already solve this?

  • The problem is very weird, dont know if just my case
  1. Do you have a support contract? No

Environment details

  • OS: Linux and Mac
  • Node.js version: 18
  • npm version: 10.5.0
  • @google-cloud/firestore version: 7.6.0

Steps to reproduce

  1. Am just making a “simple” query that uses the OR operator and the new inequality operator on multiple fields, the query is searching over an Id that its repeated on my database with aprox 27k items:

This is an example of the query:

            .where('myId', '==', 123)
            .where('createdAt', '<=', new Date())
            .where(
                Filter.or(
                    Filter.where('deletedAt', '>=', new Date()),
                    Filter.where('deletedAt', '==', null)
                  )
            )
            .orderBy('createdAt', 'desc')
            .limit(500)
  1. The query just hangs out and never finished, throwing the error 14 UNAVAILABLE: Query timed out. Please try either limiting the entities scanned, or run with an updated index configuration..

What may be happening here?, maybe am missing some weird index?, I tried to run the explain function but with the analyze flag the result is the same, without it, the plan summary its this one:

 PlanSummary {
  indexesUsed: [
    {
      properties: '(myId ASC, createdAt DESC, deletedAt DESC, __name__ DESC)',
      query_scope: 'Collection'
    },
    {
      properties: '(myId ASC, deletedAt ASC, createdAt DESC, __name__ DESC)',
      query_scope: 'Collection'
    }
  ]
}

Thanks in advance!

About this issue

  • Original URL
  • State: closed
  • Created 2 years ago
  • Comments: 30 (11 by maintainers)

Most upvoted comments

Yes, a fix has been submitted and we need to roll it out to the backend servers. The ETA for the fix to actually be fully rolled out is 2-4 weeks. Thanks again for all your help diagnosing this!

We have received the debug logs and have been able to reproduce the issue. This is great news because it means we can investigate and test out fixes. We will update on the progress by commenting on this GitHub issue.

Hi @abarone-btf , could you please help us confirm that by running the following queries, if the number of results returned on your end matches below? These are the numbers we observed from our logs.

Query Num of results
where(‘myId’, ‘==’, 123).where(‘createdAt’, ‘<=’, new Date()).where(‘deletedAt’, ‘>=’, new Date()).orderBy(‘createdAt’, DESC).limit(500) 0
where(‘myId’, ‘==’, 123).where(‘createdAt’, ‘<=’, new Date()).where(‘deletedAt’, ‘==’, NULL).orderBy(‘createdAt’, DESC).limit(500) 500

While we wait for the support ticket to be created and surfaced, can you also help us populate the table below by testing on your end (also correct me if I record the behavior incorrectly)?

query behavior
where(‘myId’, ‘==’, 123).where(‘createdAt’, ‘<=’, new Date()).where(or(where(‘deletedAt’, ‘>=’, new Date()), where(‘deletedAt’, ‘==’, null))).orderBy(‘createdAt’, DESC).limit(500) timeout
where(‘myId’, ‘==’, 123).where(or(where(‘deletedAt’, ‘>=’, new Date()), where(‘deletedAt’, ‘==’, null))).orderBy(‘createdAt’, DESC).limit(500) timeout
where(‘myId’, ‘==’, 123).where(‘createdAt’, ‘<=’, new Date()).where(‘deletedAt’, ‘>=’, new Date()).orderBy(‘createdAt’, DESC).limit(500) succeed
where(‘myId’, ‘==’, 123).where(‘createdAt’, ‘<=’, new Date()).where(‘deletedAt’, ‘==’, NULL).orderBy(‘createdAt’, DESC).limit(500) succeed
where(‘myId’, ‘==’, 123).where(‘createdAt’, ‘<=’, new Date()).where(‘deletedAt’, ‘==’, NULL).orderBy(‘createdAt’, DESC).orderBy(‘deletedAt’, DESC).limit(500) ?
where(‘myId’, ‘==’, 123).where(or(where(‘deletedAt’, ‘>=’, new Date()), where(‘deletedAt’, ‘==’, null))).orderBy(‘deletedAt’, DESC).limit(500) ?

Thank you @abarone-btf. The backend engineers have investigated and found a potential query planner bug in the backend (Googlers see b/346321260). I’ll keep you posted on developments as investigation continues.

One question though… you mentioned in the OP that

the query is searching over an Id that its repeated on my database with aprox 27k items

When you say “aprox 27k items” is “27k” referring to the number of documents that match the query, or the total number of documents in the collection? If the former, can you give an estimate on the total number of documents in the collection?

Would you be willing to provide your Firebase project number so we can check the backend logs?

You can find your project number in the Firebase Console (screenshot)

Hi @dconeybe !, of course, this are the results for each of the steps (I am applying this individually, starting each remove from the base query described above, not a permutation of removes):

  • Without limit: UNAVAILABLE error ❌
  • Without orderBy: Needed a new index, after creation the result is UNAVAILABLE error ❌
  • Without the OR clause: Needed a new index, after creation the result is correct ✅
  • Without the createdAt condition (keeping the OR of course): Needed a new index, after creation the result is UNAVAILABLE error ❌

One thing I tried that “works” is applying an order by deletedAt first (asc), then by createdAt, the query runs smoothly without problems (maybe because the data has many null values in that field, in contrast createdAt has a lot more cardinality), also in that example the plan summary uses a single index, in contrast with the base query that uses 2 “almost identical” indexes.

In my case that re orderding does not work because I might miss some entries in the pagination flow, but its worth mention it.

Thanks again for your help.