[Bug]: selector-filtered _changes is slower the first time after creating a Mango index

[jcoglan]

Version

3.5.1, and main branch

Describe the problem you're encountering

Despite the fact that _changes does not use indexes to speed up selector filters, the first time it is requested after creating a Mango index, it is slower, which possibly indicates it's spending time updating the index.

Expected Behaviour

The time taken for _changes to respond should not depend on the existence or otherwise of Mango indexes.

Steps to Reproduce

To demonstrate this, I created a DB containing a million docs of the form { "_id": "doc-N", "value": N } for N from 1 to 1,000,000. Then I timed how long it took to run this filtered changes request:

# uses this shell function:
cdb () {
  curl -gsu 'admin:admin' "http://127.0.0.1:5984$1" "${@:2}"
}

time ( cdb '/db/_changes?filter=_selector' -X POST -d '{ "selector": { "value": { "$lt": 100 } } }' )

This would take around 12 seconds.

Then I created an index covering the filtered field and re-timed:

cdb '/db/_index' -X POST -d '{ "index": { "fields": ["value"] }, "name": "by-value", "type": "json" }'
time ( cdb '/db/_changes?filter=_selector' -X POST -d '{ "selector": { "value": { "$lt": 100 } } }' )

The first time after creating an index, _changes takes over 30s to complete. On later requests it's back to around 12s, possibly very slightly slower.

This effect shows up regardless of which fields the index covers; even if it does not cover the _changes selector, the _changes request is still slow.

Your Environment

macOS 15, CouchDB 3.5.1, single node, database created with q=2, n=1

Additional Context

No response

[nickva]

That's an interesting bug @jcoglan. It's quite a large difference.

To eliminate this being a case of background indexing adding extra load the cluster, ensure to wait for the index to complete building then query the _changes endpoint.

Another experiment might be to try it with a JS index. Index documents also have an autoupdate option default to true but you can try to set it to false to avoid triggering automatic index updates. (You can trigger it by hand then by querying it).