to start you could set log levels for connection bus and queries to Trace/Debug
This will give you a lot insight in how XPath queries are converted into SQL queries
Also gives insight in which queries are actually executed, as this might be more then what you think off in case of pages
Your response is helpful in a general sense. In my case I have done those things already.
The goal is to profile a query. Nothing short of a query profiler will give me what I need.
A query profiler answers the question “what path did the database take in terms of scans and indexes to return the results and what was the cost of each component of the query path?”.
The answers to those questions allow me to target optimization solutions with hard knowledge of what needs to be optimized.
Is there a way to a profile a query when using Mendix Cloud?
I wish I could do that. There is offcourse an addon ( https://docs.mendix.com/addons/apd-addon/ ) but that will cost you. What I do is creating XPath test flows where I use the community commons TimeMeasureStart and TimeMeasureEnd and inbetween the XPath I would like to test. And do not use one retrieve but iterate over a set so you already have an average. Then play with indexes or with a different build up of the XPath and check those times again.