[DOCS-15091] Investigate changes in SERVER-63078: Show when clustered index is used in explain and slow query logs Created: 04/Feb/22  Updated: 22/Jan/24

Status: Backlog
Project: Documentation
Component/s: manual, Server
Affects Version/s: None
Fix Version/s: 5.3.0

Type: Task Priority: Major - P3
Reporter: Backlog - Core Eng Program Management Team Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: backlog, feature, query
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Documented
documents SERVER-63078 Show when clustered index is used in ... Closed
Participants:
Days since reply: 2 years, 5 days ago
Epic Link: DOCSP-19447

 Description   
Original Downstream Change Summary

Bounded scans on clustered collections will display a CLUSTERED_IXSCAN stage in the slow query logs and explain output. Clustered collection scans without bounds will continue to display a COLLSCAN.

This only affects the displayed name of the query plan for time-series collections. For example, say I have a time-series collection without any indexes:

db.createCollection('ts', {timeseries: {timeField: 't'}})

If I perform a scan with a filter on time, a CLUSTERED_IXSCAN will be displayed in the explain output:

db.ts.find({t: {$lt: new Date()}).explain()
...
          winningPlan: {
            stage: 'CLUSTERED_IXSCAN',
            filter: {
              '$and': [
                {
                  _id: { '$lt': ObjectId("61fcedcd0000000000000000") }
                },
                {
                  'control.max.t': {
                    '$_internalExprLt': ISODate("2022-02-04T10:11:40.610Z")
                  }
                },
                {
                  'control.min.t': {
                    '$_internalExprLt': ISODate("2022-02-04T09:11:40.610Z")
                  }
                }
              ]
            },
            direction: 'forward',
            maxRecord: ObjectId("61fcedcd0000000000000000")
          },
...

And in the slow query logs:

{"t":{"$date":"2022-02-04T04:14:24.826-05:00"},"s":"I",  "c":"COMMAND",  "id":51803,   "ctx":"conn2","msg":"Slow query","attr":{"type":"command","ns":"test.ts","appName":"mongosh 1.1.9","command":{"find":"ts","filter":{"t":{"$lt":{"$date":"2022-02-04T09:14:24.819Z"}}},"lsid":{"id":{"$uuid":"7d08265d-f56b-4ba5-a35e-578d99fdbe3b"}},"$db":"test"},"planSummary":"CLUSTERED_IXSCAN","resolvedViews":[{"viewNamespace":"test.ts","dependencyChain":["ts","system.buckets.ts"],"resolvedPipeline":[{"$_internalUnpackBucket":{"timeField":"t","bucketMaxSpanSeconds":3600}}]}],"keysExamined":0,"docsExamined":1,"cursorExhausted":true,"numYields":0,"nreturned":9,"queryHash":"8B3F27D2","planCacheKey":"8B3F27D2","queryExecutionEngine":"classic","reslen":420,"locks":{"ParallelBatchWriterMode":{"acquireCount":{"r":1}},"ReplicationStateTransition":{"acquireCount":{"w":1}},"Global":{"acquireCount":{"r":5}},"Database":{"acquireCount":{"r":1}},"Collection":{"acquireCount":{"r":1}},"Mutex":{"acquireCount":{"r":5}}},"storage":{},"remote":"127.0.0.1:53820","protocol":"op_msg","durationMillis":3}}

Description of Linked Ticket

Slow queries on clustered collections report a COLLSCAN, but it's not clear that this is actually a bounded scan over a clustered collection..

Additionally, the explain output reports minRecord and maxRecord, but we should include a flag that specifically indicates that this was a ranged scan over a clustered collection,


Generated at Thu Feb 08 08:11:59 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.