[SERVER-23038] Why index dosn't working? Created: 10/Mar/16  Updated: 14/Apr/16  Resolved: 16/Mar/16

Status: Closed
Project: Core Server
Component/s: Index Maintenance, WiredTiger
Affects Version/s: 3.2.3
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: raylynn Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

WINDOWS 2008 R2


Attachments: PNG File match.png    
Issue Links:
Duplicate
duplicates SERVER-15086 Allow for efficient range queries ove... Closed
Operating System: Windows
Steps To Reproduce:

index:

{
    "uid" : -1,
    "infor.orderStatus" : 1,
    "exts.post_his.unpostids" : 1,
    "infor.gmtModified" : -1,
    "exts.dvy_sign.issigned" : 1,
    "infor.issueInfo.issueStatus" : 1
}

when I run the finding code:

db.getCollection('w_trade_aliexpress').find({
                    "uid" : 3630,
                    "infor.gmtModified" : {
                        "$gt" : ISODate("2016-02-03T00:30:08.000+08:00"),
                        "$lt" : ISODate("2016-03-06T15:56:07.670+08:00")
                    },
                    "exts.post_his.unpostids" : "56c5666bef68fc0e28524dd5",
                    "exts.dvy_sign.issigned" : true,
                    "infor.orderStatus" : "FINISH"
                }).explain()

the explain:

/* 1 */
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "wxwdb.w_trade_aliexpress",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "exts.dvy_sign.issigned" : {
                        "$eq" : true
                    }
                }, 
                {
                    "exts.post_his.unpostids" : {
                        "$eq" : "56c5666bef68fc0e28524dd5"
                    }
                }, 
                {
                    "infor.orderStatus" : {
                        "$eq" : "FINISH"
                    }
                }, 
                {
                    "uid" : {
                        "$eq" : 3630
                    }
                }, 
                {
                    "infor.gmtModified" : {
                        "$lt" : ISODate("2016-03-06T15:56:07.670+08:00")
                    }
                }, 
                {
                    "infor.gmtModified" : {
                        "$gt" : ISODate("2016-02-03T00:30:08.000+08:00")
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "filter" : {
                "$and" : [ 
                    {
                        "exts.post_his.unpostids" : {
                            "$eq" : "56c5666bef68fc0e28524dd5"
                        }
                    }, 
                    {
                        "infor.gmtModified" : {
                            "$lt" : ISODate("2016-03-06T15:56:07.670+08:00")
                        }
                    }, 
                    {
                        "infor.gmtModified" : {
                            "$gt" : ISODate("2016-02-03T00:30:08.000+08:00")
                        }
                    }
                ]
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "uid" : -1,
                    "infor.orderStatus" : 1,
                    "exts.post_his.unpostids" : 1,
                    "infor.gmtModified" : -1,
                    "exts.dvy_sign.issigned" : 1,
                    "infor.issueInfo.issueStatus" : 1
                },
                "indexName" : "posting2",
                "isMultiKey" : true,
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 1,
                "direction" : "forward",
                "indexBounds" : {
                    "uid" : [ 
                        "[3630.0, 3630.0]"
                    ],
                    "infor.orderStatus" : [ 
                        "[\"FINISH\", \"FINISH\"]"
                    ],
                    "exts.post_his.unpostids" : [ 
                        "[MinKey, MaxKey]"
                    ],
                    "infor.gmtModified" : [ 
                        "[MaxKey, MinKey]"
                    ],
                    "exts.dvy_sign.issigned" : [ 
                        "[true, true]"
                    ],
                    "infor.issueInfo.issueStatus" : [ 
                        "[MinKey, MaxKey]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ 
            {
                "stage" : "FETCH",
                "filter" : {
                    "$and" : [ 
                        {
                            "exts.dvy_sign.issigned" : {
                                "$eq" : true
                            }
                        }, 
                        {
                            "exts.post_his.unpostids" : {
                                "$eq" : "56c5666bef68fc0e28524dd5"
                            }
                        }, 
                        {
                            "infor.orderStatus" : {
                                "$eq" : "FINISH"
                            }
                        }, 
                        {
                            "infor.gmtModified" : {
                                "$lt" : ISODate("2016-03-06T15:56:07.670+08:00")
                            }
                        }, 
                        {
                            "infor.gmtModified" : {
                                "$gt" : ISODate("2016-02-03T00:30:08.000+08:00")
                            }
                        }
                    ]
                },
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "uid" : -1,
                        "infor.id" : -1
                    },
                    "indexName" : "uid_-1_infor.id_-1",
                    "isMultiKey" : false,
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 1,
                    "direction" : "forward",
                    "indexBounds" : {
                        "uid" : [ 
                            "[3630.0, 3630.0]"
                        ],
                        "infor.id" : [ 
                            "[MaxKey, MinKey]"
                        ]
                    }
                }
            }, 
            {
                "stage" : "FETCH",
                "filter" : {
                    "$and" : [ 
                        {
                            "exts.dvy_sign.issigned" : {
                                "$eq" : true
                            }
                        }, 
                        {
                            "exts.post_his.unpostids" : {
                                "$eq" : "56c5666bef68fc0e28524dd5"
                            }
                        }, 
                        {
                            "infor.orderStatus" : {
                                "$eq" : "FINISH"
                            }
                        }, 
                        {
                            "infor.gmtModified" : {
                                "$lt" : ISODate("2016-03-06T15:56:07.670+08:00")
                            }
                        }, 
                        {
                            "infor.gmtModified" : {
                                "$gt" : ISODate("2016-02-03T00:30:08.000+08:00")
                            }
                        }
                    ]
                },
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "uid" : 1,
                        "exts.eval.evaluatetime" : -1
                    },
                    "indexName" : "uid_1_exts.eval.evaluatetime_-1",
                    "isMultiKey" : false,
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 1,
                    "direction" : "forward",
                    "indexBounds" : {
                        "uid" : [ 
                            "[3630.0, 3630.0]"
                        ],
                        "exts.eval.evaluatetime" : [ 
                            "[MaxKey, MinKey]"
                        ]
                    }
                }
            }
        ]
    },
    "serverInfo" : {
        "host" : "jstw4vbjuh7q7c",
        "port" : 3005,
        "version" : "3.2.3",
        "gitVersion" : "b326ba837cf6f49d65c2f85e1b70f6f31ece7937"
    },
    "ok" : 1
}

Participants:

 Description   

I have a collection w_trade_aliexpress.
the index cannot match find item,why?



 Comments   
Comment by Kelsey Schubert [ 16/Mar/16 ]

Hi tianwei02,

Thank you for opening this ticket. As I mentioned in SERVER-23079, an improvement for this behavior is tracked in SERVER-15086.

For advice on schema design please post on the mongodb-users group or Stack Overflow with the mongodb tag. A question like this involving more discussion would be best posted on the mongodb-users group.

Thank you,
Thomas

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