Parameterized query and LIKE statetement

SQL++
1

Hi,

I am trying to use PREPARED and PARAMETERIZED query for a query like

SELECT key FROM myBucket WHERE key LIKE $keyPattern AND value IS VALUED

But this does not achieve the gain of execution I am expecting as, the explain do not take into account (I think) the pattern…

The Explain end up showing
{"~children":[{“keyspace”:“core”,“spans”:[{“Range”:{“High”:["[]"],“Low”:["""",“true”],“Inclusion”:1}}],"#operator":“IndexScan”,“using”:“gsi”,“namespace”:“default”,“index”:“coreIndexOnKeyWithValue127-0-0-1-8091”,“covers”:[“cover((meta(core).id))”,“cover((core.key))”,“cover(((core.value) is valued))”]},{"~child":{"~children":[{"#operator":“Filter”,“condition”:"((cover((core.key)) like $keyPattern) and cover(((core.value) is valued)))"},{"#operator":“InitialProject”,“result_terms”:[{“expr”:“cover((core.key))”}]},{"#operator":“FinalProject”}],"#operator":“Sequence”},"#operator":“Parallel”}],"#operator":“Sequence”}

So in effect the range just goes through everything, defeating the use of an index… This makes sense, I guess, as the prepare of this query do not know the value of $keyPattern so can’t work out the right range…

Is there a way to change the query to be able to use PREPARED statement?

Many thanks.

2

Hi @lbertrand, Not sure I understand your question fully but you can prepare statements using prepare keyword on the N1QL REST API. or in the SDKs using QueryRequest and adhoc=false.
Is this what you needed?
-cihan

3

My issue is not on how to prepare a query but on the combination of using a parameterised query with a LIKE statement in the where clause.

In this case, it seems that the range query returned do not use the value of the parameter but will just do a full scan of the index… See the explain above returning “spans”:[{“Range”:{“High”:["[]"],“Low”:["""",“true”],“Inclusion”:1}}], and therefore not taking into account the named parameter $keyPattern.

If for example, $keyPattern was “prefix:%”, the explain will return something like “spans”:[{“Range”:{“High”:[prefix;]"],“Low”:[“prefix:”,“true”],“Inclusion”:1}}],

So my question is if there is a way to change the query to be able to parameterised it and use the right scan range of the index?

4

@geraldss Sorry to call on you directly but as the N1QL guru around here, I was wondering if you had suggestion for the above?

Many thanks.

5

Hi, as far as i understand it, @geraldss please correct me if i am wrong , the expression in the like clause will not be pushed down to the indexer. This means that the filtering will happen at the query engine level and it will be a complete index scan.

@cihangirb This is a good feature to have for future versions.

Cheers,
Manik

6

Hi @manik, this will be pushed down to the index if the expression begins with a constant prefix, e.g. LIKE “foo%”;

You can see this using EXPLAIN, which will show an IndexScan with lower and upper bounds.

7

Aah I see, thanks for the clarification. In this case since the like clause is parametrized it’s not going to show up in the High and Low Range attributes in the output of the explain.

cheers,
Manik

8

Yes, and as I have shown the explain, it does not even take into account the named parameter… It just go directly to do a full scan! Is this expected?

9

Yes, this is the expected behavior. Planning is done without knowledge of what the parameter value will be. If you use a string instead of a parameter, you will get the correct scan if your string does not begin with a wildcard. You can try it out and let us know what you get from EXPLAIN.

10

It does… This is what I am doing at the moment but I was wanting to replace it with named parameters but as the discussion shows it is not supported for the range scan…

I have seen EXPLAIN showing things like successor($namedParameter)… So maybe introducing a matched($namedParameter) to take the parameter into account at the indexer level and not do a full scan could be interesting to introduce.

11

In the general, we do use the correct index scans for parametrized queries. LIKE is a special case, because we need to know if the parameter value begins with a non-wildcard prefix. I suppose we could make this an execution time rather than a plan time decision. We will look into that. See https://issues.couchbase.com/browse/MB-17130

12

Fixed now on master branch.

13

I am trying to test this using Couchbase 4.5 as you mentioned it has been fixed… But it looks like I still have the same plan returned with the parameterised query which shows a full index scan…

If I do the following query

EXPLAIN SELECT key FROM myBucket WHERE key LIKE $keyPattern AND value IS VALUED

I have the following result
[
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexScan”,
“covers”: [
“cover ((myBucket.key))”,
“cover (((myBucket.value) is valued))”,
“cover ((meta(myBucket).id))”
],
“filter_covers”: {
“cover (((myBucket.value) is valued))”: true
},
“index”: “indexOnKeyWithValue”,
“index_id”: “74040767fb17d7dd”,
“keyspace”: “myBucket”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
“”
],
“Inclusion”: 1,
“Low”: [
“""”,
“true”
]
}
}
],
“using”: “gsi”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “((cover ((myBucket.key)) like $keyPattern) and cover (((myBucket.value) is valued)))”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “cover ((myBucket.key))”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
“text”: “SELECT key FROM myBucket WHERE key LIKE $keyPattern AND value IS VALUED”
}
]

So this explain still show a full scan range…

When I execute this query, I will pass the parameter keyPattern in the form prefix:%:suffix, so it should be able to restrict the scan of the index.

Maybe I misunderstood what has been fixed, but I would have expected to see a change in the execution plan to reflect not doing a full scan of the index…

@geraldss Could you confirm this is fixed and even if the plan shows a full scan of index, it will not do it?

Thanks.

14

Hi @lbertrand,

This was fixed June 19, so it will be in 4.5.1.

Gerald