mirror of
https://github.com/FerretDB/documentdb.git
synced 2026-11-07 19:42:46 -05:00
Jayanta Mondal
6b95258b24
### Does this PR have any customer impact?
### Type (Feature, Refactoring, Bugfix, DevOps, Testing, Perf, etc)
### Does it involve schema level changes? (Table, Column, Index, UDF, etc level changes)
### Are you introducing any new config? If yes, do you have tests with and without them being set?
### ChangeLog (Refer [Template](../oss/CHANGELOG.md))
### Description
In this PR, for TTL indexes, created as a composite index, we force an ordered scan while querying for eligible rows using index hints.
Composite indexes support ordered scan on the index allowing the scan to be really efficient on the index. Without composite index, the planner uses a bitmap index scan which requires fetching all index pages and creating a bitmap. If there are a lot of documents to deleted, this can put a lot of pressure on the disk IOPS due to repeated fetching of large amount of index pages.
When TTL index is created as a composite index, we effectively bypass the planner via the use of index hints and forces postgres to use the composite index. When index hint is provided the planner pins an ordered scan on the index provided as a hint.
Below is an example of how an additional condition is added, as an index hint, to the query for fetching purge eligible rows:
```sql
SELECT ctid FROM mongo_data.documents_1963502_19635007
WHERE bson_dollar_lt(document, '{ "ttl" : { "$date" : { "$numberLong" : "1657900030775" } } }'::bson)
AND helio_api_internal.bson_dollar_index_hint(document, 'ttl_index'::text, '{"key": {"ttl": 1}}'::bson, isSparse)
```