I having problems building a very simple query using the USE INDEX hint.
I’ve created this very simple index in order to optimize the queries: CREATE INDEX multi_type_index on CONTENT(main.sysAttrs.punblicationInstant DESC,main.type ASC)
If I execute this query, the system responds very slowly: select * from CONTENT USE INDEX (multi_type_index) WHERE main.sysAttrs.publicationInstant < NOW_MILLIS() AND main.type IN ['STORY', 'LIVE', 'INTERVIEW'] ORDER BY main.sysAttrs.publicationInstant DESC limit 10
The database has around 1M of documents and the explain shows that the system is not able to make an "order push down " so this seems the reason why this query is slow.
In Couchbase documentation explains that the structure in GSI index is B+Tree where the order of the elements within the index is based on the order of the declared fields in the index creation. In my case I’m respecting the order.
I’m coding an abstraction layer in JAVA in order to transform from QueryDSL to N1QL and this kind of “tricks” seems a poor workaround.
In my opinion, this problem could be resolved if partial indexes wouldn’t require the same WHERE predicate for the index as for the query (or a subset of predicates). If a user specifies the USE INDEX hint, why the system should check if the query predicate match with the index one? This should be responsible of the programmer because he is using “USE INDEX” hint. If he uses it then he has consciousness about the data allocated in the index. This way could be more flexible and it makes the queries less verbose and more lighter. I want to clarify that this should be the behaviour if and only if he is using “USE INDEX” hint.
In this way, I wouldn’t need to specify the IN clause at query time but at index creation so the index only would have the data in the IN clause and the system doesn’t need to scan multiple ranges and should be able to push down the order, offset and limit processes to the indexer.
In my opinion, the fact that the index qualifies the query should be responsible of the programmer in “USE INDEX” hint because he knows the data in the index. If the result is wrong he has to change the query as he has to change a SQL query if the query doesn’t return the data that he has looking for. The system shouldn’t take care about it if user specifies the index. Could be useful if the user doesn’t specify the index but just in this case.