JAVA SDK> REST API having N1QL query search on a field(LASTNAME having GSI created for it) with Like Clause as binding parameter in the prepare statement taking long time to respond

Java SDK
1

Couchbase Version: 4.5.0-2601 Enterprise Edition (build-2601)
couchbase-client-version: 2.3.5

Java code: Using prepare statement with positional parameters to form the search query.

final N1qlParams params = N1qlParams.build().consistency(ScanConsistency.REQUEST_PLUS);
final ParameterizedN1qlQuery query = ParameterizedN1qlQuery.parameterized(statement.toString(), values, params); (statement -> below is the statement formed, values -> set by JsonArray)
final N1qlQueryResult queryResult = Constants.CUSTOMER_BUCKET.query(query);

{“args”:[“userprofile::%”,“shaik%”],“statement”:“SELECT meta().id, lastName, firstName, birthday, emails, phones, addresses[0] AS address FROM cpm-prosodie WHERE meta().id LIKE $1 AND lower(lastName) LIKE $2 ORDER BY lastName LIMIT 20 OFFSET 0”,“scan_consistency”:“request_plus”}

executionTime=‘28.215477072s’ (which is obviously not considerable as a REST API service response)

**Whereas the same above query when hard-coded in java / run through CB query window: **

{“statement”:“SELECT meta().id, lastName, firstName, birthday, emails, phones, addresses[0] AS address FROM cpm-prosodie WHERE meta().id LIKE ‘userprofile::%’ AND lower(lastName) LIKE ‘shaik%’ ORDER BY lastName LIMIT 20 OFFSET 0”}

** executionTime=‘12.839962ms’** (GOOD TIME RESULT)

Index on lastName: Global secondary index

CREATE INDEX lastName_lower ON cpm-prosodie(lower(lastName)) USING GSI
WITH {“nodes”:[“10.64.201.4:8091”]}; (one node from CB cluster(contains 3 nodes, currently using only this node))

Primary Index:

CREATE PRIMARY INDEX #primary1 ON cpm-prosodie

Can anyone please guide what is going wrong in this…

Kuddos :slight_smile:

2

Hi @gauri.phadtare

I don’t believe the N1QL engine will be using your built index because your query columns do not match the indexed columns.

You could try adding meta().id to your index definition like this:
CREATE INDEX lastName_lower ON cpm-prosodie(meta().id, lower(lastName)) USING GSI;

Alternatively, if you include a type field in your JSON documents you can narrow down the result set further but using a WHERE predicate in the index definition.

CREATE INDEX lastName_lower ON cpm-prosodie(lower(lastName)) WHERE (type = "user_profile") USING GSI;

3

Thank you @MikeGoldsmith for replying!

Still no change with the new index : :frowning:

CREATE INDEX lastName_lower ON cpm-prosodie(meta().id, lower(lastName)) USING GSI

earlier atleast on the CB server this query giving response in ms…

{“statement”:“SELECT meta().id, lastName, firstName, birthday, emails, phones, addresses[0] AS address FROM cpm-prosodie WHERE meta().id LIKE ‘userprofile::%’ AND lower(lastName) LIKE ‘shaik%’ ORDER BY lastName LIMIT 20 OFFSET 0”}

** executionTime=‘12.839962ms’** (GOOD TIME RESULT)

and now this query itself taking time…in seconds

One more thing to note that the previous index was used correctly by the query when I try to explain the the query:

EXPLAIN SELECT meta().id, lastName, firstName, birthday, emails, phones, addresses[0] AS address FROM cpm-prosodie
WHERE meta().id LIKE ‘userprofile::%’ AND lower(lastName) LIKE ‘abachin%’ ORDER BY lastName LIMIT 20 OFFSET 0

      "#operator": "IndexScan",
                      "index": "lastName_lower",
                     "index_id": "6491d435994f8763",
                     "keyspace": "cpm-prosodie",
                    "namespace": "default",

      "#operator": "IndexScan",
              "index": "#primary1",
              "index_id": "ff147e2df18d40cb",
              "keyspace": "cpm-prosodie",
              "namespace": "default",

but still the service is taking time to respond back…

Waiting for more guidelines :slight_smile:

4

lower(lastName) LIKE ‘shaik%’ is same as (lower(lastName) >= ‘shaik’ AND lower(lastName) < ‘shail’), so in constant case like predicate is transformed and pushed to indexer.

In case of query parameter or position parameter the values will not be known until execution, optimizer generate the plan for all possible values. i.e full scan on index and query engine applies predicate.

more details can be found in example 17 and 18 https://dzone.com/articles/understanding-index-scans-in-couchbase-n1ql-query

You can rewrite the query as (lower(lastName) >=$1 AND lower(lastName) < $2)
and in application calculate values and supply both values. This will give same performance as constant value.

5

This might be fixed in 4.5.1…

6

@geraldss, It is fixed in 4.5.1, Explain has the info.

 "spans": [
                            {
                                "Range": {
                                    "High": [
                                        "like_stop($1)"
                                    ],
                                    "Inclusion": 1,
                                    "Low": [
                                        "like_prefix($1)"
                                    ]
                                }
                            }
                        ]