DELETE query performance

[selfy]

I have the following query

DELETE FROM my-db where shopInfo.shopId in [ “uuidxx123” ]

that uses the index

Index Currently Used
CREATE INDEX adv_shopInfo_shopId ON my-db((shopInfo.shopId))

Existing indexes are sufficient.

This query took 9 mins to complete for a total mutation of around 150k records.

Why does this query perform so poorly in spite of having an index and no other suggested indexes?

[mreiche]

I believe the index should be on the individual shopids, not on the whole array. See Example 1 in Array Indexing | Couchbase Docs. Note that the predicate must match the index definition. If you write your query like this, Index Advisor will show you the index to create

DELETE FROM my-db WHERE ANY sid IN shopInfo.shopId SATISFIES sid IN [“uuidxx123”] END;

[jon.strabala]

@selfy,

You might want to review the following post showing how Eventing can be used as a one-off tool to quickly select and remove documents: https://www.couchbase.com/forums/t/delete-1-billion-doc-having-docs-key-company-name-employeeid/

Best

Jon Strabala
Principal Product Manager - Server‌

[keshav_m]

The query system is designed for interactive applications to allow lots of concurrent queries and transactions. So, the resource and other defaults are designed for much smaller number of documents for each query. You’ll need to configure pipelinecap/scancap to improve the perf when handling large number of docs in a single query. See: Settings and Parameters | Couchbase Docs