[SERVER-26589] make the mongodb support invisible index Created: 12/Oct/16  Updated: 15/Nov/21  Resolved: 15/Nov/16

Status: Closed
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: 3.4.0-rc0
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: zhihui fan Assignee: David Storch
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-9306 Ability to temporarily forbid query o... Closed
Related
related to SERVER-17625 Enable index filters to persist acros... Backlog
Participants:

 Description   

I'd mongodb support the invisible index. the invisible index is still maintained like other index, but the optimizer would ignore it.

Basic api:

db.runCommand( { collMod: "sessions",
                 index: { keyPattern: { lastAccess: 1 },
                          invisible: true
                        }
})
 
 
db.runCommand( { collMod: "sessions",
                 index: { keyPattern: { lastAccess: 1 },
                          invisible: false
                        }
})

*Why this is needed*

Most developer like freedom, they may not request DBA to review their queries and they feel free to create many indexes and Then database runs with many unnecessary indexes. However, it is hard for DBA to make sure which index is really unnecessary. Sometimes, even PD is not sure about that also. like the main developer has left company and so on or lack of document and so on. So no one can make decision to drop these indexes since the rollback may take a very long time. these index is there for impacting DML and make the optimizer harder sometime.

If we have the invisible index, DBA can make the index invisible first, and watch for 2 weeks. if we knows any query still need it, we can simply make it invisible false. if no query impacts, we can drop it confidently.

If you are ok with this requirement and are lacking of resource to work on this, I'd like to work on this.

looking forward your feedback.



 Comments   
Comment by David Storch [ 15/Nov/16 ]

Hi zhihuifan,

If I understand correctly, an "invisible index" would be ignored by the query optimizer. It would be physically present but not used to answer queries. This feature request is tracked by SERVER-9306, so I am closing this ticket as a duplicate. Please re-open if I have misunderstand your suggestion.

Best,
Dave

Comment by zhihui fan [ 13/Oct/16 ]

yes, I have voted that ticket yesterday Since I was a Oracle DBA before and In oracle 8i, it has the outline feature (same as the index filter here), and In oracle 11g, Oracle supports the invisible feature.

So even without this feature, we still have workaround here. if we have it, it will make life better.

Comment by Kelsey Schubert [ 12/Oct/16 ]

Hi zhihuifan,

In addition to $indexStats, we have an open ticket, SERVER-17625, which I believe provides the solution for the case you describe. Would you please take a look and let us know?

Thank you,
Thomas

Comment by zhihui fan [ 12/Oct/16 ]

Thanks ramon! this is very useful for my case and I will try it after we upgrade database to this version. But this still can't covers the following case.
1. there are many indexes on the table
2. optimizer choose the wrong index X for a query.
3. DBA find the query use the wrong index X and think the X should be unnecessary and want to drop it. however if it use $indexStats, DBA still find the index is used

currently I didn't find out a easy way to make sure the query not use a index. Asking PD to add hint to use the desired index usually make pd unhappy. if DBA use index filter, the index filter will be missed after a restart.

so I'd like to leave this open but at a P4 level unless we can find a method to fix the above issue easily.

Comment by Ramon Fernandez Marina [ 12/Oct/16 ]

zhihuifan, please take a look at the $indexStats aggregation operator, which provides information about index access – I think this will meet your needs nicely.

Thanks,
Ramón.

Generated at Thu Feb 08 04:12:35 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.