[SERVER-15600] Support for explaining query solution in the presence of hypothetical indexes Created: 10/Oct/14  Updated: 23/Oct/23

Status: Backlog
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: Andrew Morrow (Inactive) Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
is related to SERVER-15463 Exclude indexes from query plan (hint... Backlog
Assigned Teams:
Query Optimization
Participants:

 Description   

It would be useful to be able to ask the query planner to explain a query solution given some additional hypothetical index specification(s). This would allow a user to experiment with how a query would be planned with other indexes without needing to create those indexes.

Something like, in the shell::

find(...).explain().hypothesizing([indexSpec0, indexSpec1, ..., indexSpecn])



 Comments   
Comment by Matt Boros [ 23/Oct/23 ]

For skunkworks I created a rough POC of this: https://github.com/10gen/mongo/compare/master...hypothetical_indexes
It only works for Bonsai with sampling mode, explaining .find() queries. It assumes non-multikeyness and that the indexes are forward, but more details about the hypothetical indexes could easily be plumbed from command layer to cqf_get_executor.cpp.

The jstest I included shows:

  • The optimizer can pick a hypothetical index over a real one.
  • It can pick a real index over a hypothetical one.
  • It can choose a collscan over any hypothetical or real indexes.
  • It can choose the best hypothetical index to use, given several.
Comment by Andy Pavlo [ 10/Oct/14 ]

Per my discussion with Andrew yesterday, this would be similar to the "what-if" API used in RDBMSs:

http://dl.acm.org/citation.cfm?doid=276305.276337

Comment by Scott Hernandez (Inactive) [ 10/Oct/14 ]

You would also need to be able to specify if the potential index would be multi-key or not; Once/if we have stats that too would affect index ranking/selection.

Generated at Thu Feb 08 03:38:28 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.