Query with embedded parameters 10x faster than parameterized query

I’m trying to figure out why on CB server 5.5.1 this parameterized query:

{                               
    "statement": "SELECT COUNT(type) AS count, type, MAX(content.updatedAt) AS lastUpdatedAt FROM master WHERE type IS NOT MISSING AND ANY c IN channels SATISFIES c IN $channels END GROUP BY type ORDER BY type",
    "$channels": ["denim_all_stores", "denim_location_161", "denim_tax_retail_location_group_33741"]
}

Takes 10x longer to execute than this query with the same parameters embedded:

{               
    "statement": "SELECT COUNT(type) AS count, type, MAX(content.updatedAt) AS lastUpdatedAt FROM master WHERE type IS NOT MISSING AND ANY c IN channels SATISFIES c IN ['denim_all_stores', 'denim_location_161', 'denim_tax_retail_location_group_33741'] END GROUP BY type ORDER BY type"
}

The index is:

CREATE INDEX `idx_document_metrics` ON `master`((all (`channels`)),`type`,(`content`.`updatedAt`))

I also tried creating a prepared statement and executing it, which had about the same performance as the parameterized query.

The queries return exactly the same results. I’ve attached the execution plan from EXPLAIN for both.

explain.zip (2.5 KB)

I’d appreciate any pointers on this. I need to be able to use parameterized queries, but the performance is inadequate.

1 Like
  1. Query parameters are not know at the time prepare time, right side of the IN clause parameter array. So prepare time must account all possible values. This result in extra work. More details can be found in https://blog.couchbase.com/in-list-handling-improvements-in-couchbase-server-6-5/

  2. Query using ARRAY index and part of the ANY clause using the parameters. In that situation it can’t use implicit covering index MB-33009

As you described embedded the parameters (In SDks form query string after you know parameters and execute it) and execute query better performance.

OR

Try the following query with adhoc=true same query parameters.

SELECT COUNT(DISTINCT META(m).id) AS count, m.type, MAX(m.content.updatedAt) AS lastUpdatedAt
FROM master AS m
UNNEST m.channels AS c
WHERE m.type IS NOT MISSING AND c IN $channels
GROUP BY m.type
ORDER BY m.type;