Down to 160ms, amazing!
Thank you @vsr1 , @deepkaran.salooja
select count(*) as count
use index (idx_count_posts)
where t = 'post' and topic = 'test'
Follow up on this question, to share my eventual resolution if someone has similar issue.
As fast and as awesome as Couchbase is - 160ms query absolutely kills it if this request (via REST) is spammed by holding F5 in a browser. 100% cpu usage and eventual time outs as requests exceed 30 seconds. An example average query against Couchbase on my janky dev box is 4-18ms.
So in combination with the above optimization I’ve also used Couchbase Expiration to temporarily cache the query result in a document.
Using Couchbase doc with expiration is handy because it centralizes this operation to Couchbase when used by multiple REST clients.
The client app simply queries to see if there is a cache present, if not it issues a temporary CountCache doc while running full count(*) query.
with some Expiry which is less than a second.
Since many more requests may come before count(*) … where… is done - other requests check if CountCache is present and if counting == true and sleep themselves (non-blocking) for a few ms.
When the query finishes, the request that initiated the count(*) … where… upserts the same document but with correct count and counting = false.
Eventually all requests get the cached count.
In a few seconds the CacheCount doc expires prompting new query on next request.
So far, testing this - I have not had double query run on a single couchbase node (double queries in a cluster are ok). Performance is the same as with stress testing on any other query.
Its one solution, hope it helps someone. If you have better idea please let me know!