[SERVER-55972] Query planner should compete regular indexes against FTS indexes Created: 09/Apr/21  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: Query Language, Text Search
Affects Version/s: None
Fix Version/s: None

Type: Question Priority: Major - P3
Reporter: Ian Hannah Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
depends on SERVER-17648 Implement matcher for text predicates Backlog
Assigned Teams:
Query Optimization
Participants:

 Description   

Once we are able to execute text predicates outside of an index (SERVER-17648), this ticket tracks the work to compete plans using an fts index against plans using 'normal' indexes applying the text filter afterwards.

Original Description

We have a collection of items with approx. 1.5 million documents. When we run this query:

db.items.find({ "PId" : ObjectId("5ffc8133d1cd2617a4dadbf6") }).count()

A value of 12 is returned i.e. there are 12 documents with the PId specified in the query. This query pretty much returns instantly.

However, running the following query takes around 45 seconds to run and returns a value of 2:

db.items.find({
  "PId" : ObjectId("5ffc8133d1cd2617a4dadbf6"), 
  "$text" : { "$search" : "\"average rainfall\"" } 
}).count() 

So of the 12 matching documents, 2 have average rainfall in the properties.

So it appears that the first query does not do anything and that the text search is taking priority. Is this the case? Is this a bug or is there a way to prioritise the order in which the queries are executed?

 

 



 Comments   
Comment by Charlie Swanson [ 28/Apr/21 ]

Hi ihannah@meniscus.co.uk,

I think I understand the problem here. I think the most fundamental problem here is SERVER-17648 and so this is almost a duplicate of that request. However, I think your example is interesting and takes that work a step further than it might go if just tracked as SERVER-17648, so I re-titled the ticket appropriately. If I'm not mistaken, this ticket now describes what you want more generally, and tracks the dependency on SERVER-17648.

Thanks for the report!

Comment by Ian Hannah [ 13/Apr/21 ]

Has there been an update to this issue?

Generated at Thu Feb 08 05:37:57 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.