[MB-64752] Prepared statement slower than ad-hoc query when containing duplicate WHERE condition

Product: Couchbase Server
Component: query-service
Issue Link: MB-64752
Affects Version(s): 7.2.x, 7.6.x
Fix Version(s): 7.2.7, 7.6.6, 8.0.0

Summary

If a query statement contains a duplicate WHERE (filter) condition in a Prepared Statement, this may prevent aggregation from being pushed down to the indexer, meaning the aggregation happens within the Query Service, resulting in slower query execution time. Ad-hoc queries automatically de-duplicate filter conditions, so they aren’t affected. This means that the same query may take longer as a prepared statement compared to an ad-hoc query if it contains a duplicate WHERE (filter) condition.

Symptoms 

• Slower query execution time when executed as a prepared statement, compared to as an ad-hoc query.

Triggers

• The query statement is executed as a prepared statement.
• The query statement contains a duplicated WHERE (filter) condition.

The following example shows the condition cno = $cno duplicated:

PREPARE p1 AS SELECT RAW SUM(amt)
FROM default as d
WHERE date BETWEEN $sdate AND $edate
      AND cno = $cno
      AND (cno = $cno AND amt >=1 AND cid IN ["a"])
      AND META().id != $mid;

Verification

• Check the WHERE clause of the affected prepared statement.
• Verify whether any conditions are duplicated, like in the above example.

Workarounds

• Remove the duplicated condition from the statement.
  • This allows aggregation to be pushed down the indexer, reducing the amount of work needed to be done by the Query Service and resulting in a faster execution time.
• Use adhoc = true when executing the statement.