Cannot push down offset + limit to the indexer

SQL++
1

I have an index like this:

(I would rather and have tried to leave out the partial index predicates from the indexes fields)

I query like this:
`esc
LIMIT 50 offset 10

I have also tried 

In both cases all the predicates are pushed down to the indexer but NOT the offest+limit

If I remove the order by completely, the offset+limit is pushed down to the indexer. 

What am I doing wrong?
2

What version of Couchbase are you using?

3

It should have. Post the explain. Index definition that used in EXPLAIN

4

deleted security reasons

5

deleted security reasons

6

Enterprise Edition 6.5.0 build 4960

@vsr1

7

I tried same build it uses index order and pushes the limit and offset.

CREATE INDEX ix1 on default(type,userType,status,createDate desc) WHERE ((type = "user-profile") and (userType = "contractor"));

explain SELECT p.userType a from default p WHERE p.type = 'user-profile' AND p.userType = 'contractor' AND p.status = 'approved' order by p.createDate desc LIMIT 50 offset 10;
{
    "requestID": "1f76fd66-e981-416b-974d-945dd6bdf270",
    "signature": "json",
    "results": [
    {
        "plan": {
            "#operator": "Sequence",
            "~children": [
                {
                    "#operator": "Sequence",
                    "~children": [
                        {
                            "#operator": "IndexScan3",
                            "as": "p",
                            "covers": [
                                "cover ((`p`.`type`))",
                                "cover ((`p`.`userType`))",
                                "cover ((`p`.`status`))",
                                "cover ((`p`.`createDate`))",
                                "cover ((meta(`p`).`id`))"
                            ],
                            "filter_covers": {
                                "cover ((`p`.`type`))": "user-profile",
                                "cover ((`p`.`userType`))": "contractor"
                            },
                            "index": "ix1",
                            "index_id": "b54412c49eba3425",
                            "index_order": [
                                {
                                    "keypos": 0
                                },
                                {
                                    "keypos": 1
                                },
                                {
                                    "keypos": 2
                                },
                                {
                                    "desc": true,
                                    "keypos": 3
                                }
                            ],
                            "index_projection": {
                                "entry_keys": [
                                    0,
                                    1,
                                    2,
                                    3
                                ]
                            },
                            "keyspace": "default",
                            "limit": "50",
                            "namespace": "default",
                            "offset": "10",
                            "spans": [
                                {
                                    "exact": true,
                                    "range": [
                                        {
                                            "high": "\"user-profile\"",
                                            "inclusion": 3,
                                            "low": "\"user-profile\""
                                        },
                                        {
                                            "high": "\"contractor\"",
                                            "inclusion": 3,
                                            "low": "\"contractor\""
                                        },
                                        {
                                            "high": "\"approved\"",
                                            "inclusion": 3,
                                            "low": "\"approved\""
                                        }
                                    ]
                                }
                            ],
                            "using": "gsi"
                        },
                        {
                            "#operator": "Parallel",
                            "maxParallelism": 1,
                            "~child": {
                                "#operator": "Sequence",
                                "~children": [
                                    {
                                        "#operator": "Filter",
                                        "condition": "(((cover ((`p`.`type`)) = \"user-profile\") and (cover ((`p`.`userType`)) = \"contractor\")) and (cover ((`p`.`status`)) = \"approved\"))"
                                    },
                                    {
                                        "#operator": "InitialProject",
                                        "result_terms": [
                                            {
                                                "as": "a",
                                                "expr": "cover ((`p`.`userType`))"
                                            }
                                        ]
                                    },
                                    {
                                        "#operator": "FinalProject"
                                    }
                                ]
                            }
                        }
                    ]
                },
                {
                    "#operator": "Limit",
                    "expr": "50"
                }
            ]
        },
        "text": "SELECT p.userType a from default p WHERE p.type = 'user-profile' AND p.userType = 'contractor' AND p.status = 'approved' order by p.createDate desc LIMIT 50 offset 10;"
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "4.123356ms",
        "executionTime": "4.010795ms",
        "resultCount": 1,
        "resultSize": 4977
    }
}
cbq> [root@localhost
[root@localhost ~]# rpm -q -a | grep couchbase
couchbase-server-6.5.0-4960.x86_64

Some reason it lost DESC index definition? Check system:indexes for that index and see what it has.
Try order by p.createDate ASC see if it uses?

1 Like
8

Ok I found the issue. I had created the index like this (I’ll use single quotes instead of backticks but note them)

I had created the index like this ‘app’(‘type’,‘userType’,‘status’,‘createDate desc’)

The problem was my including createDate and desc into a singe backticked expression.

Thank you for your help

1 Like