Ranges not used on primary index for Meta().id

jonathonl · February 5, 2014, 3:21pm

It would be highly beneficial if startKey and endKey ranges were used for document ids when a primary key exists. Currently the input ranges are null.

All of my documents follow an ID structure of {DocType}::{uuid} (e.g., User::3b49dd18-1d56-478e-8ab1-fb38e31ce7e2). So a common query would look like:
SELECT * FROM default WHERE Meta().id LIKE “User::%” AND …

UPDATE
cbq> EXPLAIN SELECT * FROM gamesim-sample WHERE Meta().id LIKE “Aaron%” { “resultset”: [ { “input”: { “expr”: { “left”: { “left”: { “distinct”: false, “name”: “META”, “operands”: [ { “expr”: { “path”: “gamesim-sample”, “type”: “property” }, “star”: false } ], “type”: “function” }, “right”: { “path”: “id”, “type”: “property” }, “type”: “dot_member” }, “right”: { “type”: “literal_string”, “value”: “Aaron%” }, “type”: “like” }, “input”: { “as”: “gamesim-sample”, “bucket”: “gamesim-sample”, “ids”: null, “input”: { “as”: “”, “bucket”: “gamesim-sample”, “cover”: false, “index”: “#primary”, “pool”: “default”, “ranges”: null, “type”: “scan” }, “pool”: “default”, “projection”: null, “type”: “fetch” }, “type”: “filter” }, “result”: [ { “as”: “”, “expr”: { “path”: “gamesim-sample”, “type”: “property” }, “star”: true } ], “type”: “projector” } ], “info”: [ { “caller”: “http_response:152”, “code”: 100, “key”: “total_rows”, “message”: “1” }, { “caller”: “http_response:154”, “code”: 101, “key”: “total_elapsed_time”, “message”: “3.976014ms” } ] }

geraldss · February 5, 2014, 6:21pm

Please run

CREATE PRIMARY INDEX

and then post the output of EXPLAIN for us to inspect.

Thanks.

jonathonl · February 5, 2014, 6:52pm


cbq> EXPLAIN SELECT * FROM gamesim-sample WHERE Meta().id LIKE "Aaron%"
{
    "resultset": [
        {
            "input": {
                "expr": {
                    "left": {
                        "left": {
                            "distinct": false,
                            "name": "META",
                            "operands": [
                                {
                                    "expr": {
                                        "path": "gamesim-sample",
                                        "type": "property"
                                    },
                                    "star": false
                                }
                            ],
                            "type": "function"
                        },
                        "right": {
                            "path": "id",
                            "type": "property"
                        },
                        "type": "dot_member"
                    },
                    "right": {
                        "type": "literal_string",
                        "value": "Aaron%"
                    },
                    "type": "like"
                },
                "input": {
                    "as": "gamesim-sample",
                    "bucket": "gamesim-sample",
                    "ids": null,
                    "input": {
                        "as": "",
                        "bucket": "gamesim-sample",
                        "cover": false,
                        "index": "#primary",
                        "pool": "default",
                        "ranges": null,
                        "type": "scan"
                    },
                    "pool": "default",
                    "projection": null,
                    "type": "fetch"
                },
                "type": "filter"
            },
            "result": [
                {
                    "as": "",
                    "expr": {
                        "path": "gamesim-sample",
                        "type": "property"
                    },
                    "star": true
                }
            ],
            "type": "projector"
        }
    ],
    "info": [
        {
            "caller": "http_response:152",
            "code": 100,
            "key": "total_rows",
            "message": "1"
        },
        {
            "caller": "http_response:154",
            "code": 101,
            "key": "total_elapsed_time",
            "message": "2.917415ms"
        }
    ]
}

geraldss · February 6, 2014, 11:24pm

Thanks for posting this. You are right, this should be a range scan.

We’ve create ticket http://www.couchbase.com/issues/browse/MB-10148 for DP4.