I even tried creating content packages from the environments where it is working fine and installing in my local environment without luck.
I would focus on debugging the code further(despite I already did without luck) but the fact that it does not return any result in the CRX query tool neither, indicates me the problem should not be in the code.
Also I already reviewd the query plans and there is not difference between it in a working environment and my local environment.
Also I tried an offline reindex with the oak tool and nothing changed.
Also, Execute your query in Query Performance tool(http://localhost:4502/libs/granite/operations/content/diagnosistools/queryPerformance.html) and arrive at Index used. Try to reindex only that specific index by updating the property "reindex" to true and wait for some time to see the property updated to false and reindexCount update to next incremented value upon successful reindexing. (Refresh the index node after some time to see the property values updated automatically upon successful reindex)
Example: For the sample query you posted, ntBaseLucene is the index used. You can reindex only that particular index - /oak:index/ntBaseLucene
Note : Given that it is your local environment, you can reindex directly like this.
Logs statements are displayed here and you can also check detailed trace from Sling Log as mentioned above.
Just realized from my screenshot in previous answer that the result count is 0. (After posting the answer :()
Ignore my previous answer with respect to Query Performance. (You can consider Logger entry)
Issue is with the query. path shouldn't be with jcr:content in this case because there won't be any further nodes (under jcr:content) which has the sling:resourceType to be the one(weretail/components/structure/page) that we are looking for.