I’m learning about Couchbase plans in order to identify bottlenecks and find out an explanation for high query timings, and I have some doubts…
What is STREAM phase? What does it mean that most of the time on the planing is spent on that phase? How to get it improved?
What is PROJECT phase? What does it mean that most of the time on the planing is pent on that phase? How to get it improved?
The documentation refers to phaseSwitches and indicates that if they are low could point to a problem? Why? What does it mean actually (what are switches between executing, waiting, … etc) and what are “low values” for that one?
And I also have doubts about how to use PREPARE statement. I undersand that I as admin should setup a correct PREPARE statement, i.e. using parameters that will fit with the queries that will be used by the application and giving it a name, and later on the application could use it doing an EXECUTE of such preparation and indicating the parameters. Is that correct? Or is there any other way to run prepared statements (i.e. run directly prepare select… from the application each time a query is run)
The projection is influenced by what you are selecting and the operations performed on the elements. If you have complex operations in the projection (the list of elements in the SELECT) then this can impact the performance.
Low phase switches could mean that some portion of the plan is slow meaning others are backed up/blocked and are not changing state (items flowing through a plan will result in state changes). Of course this is relative to the volume of items being processed.
Normally an application will prepare the statements it is going to use up front (during say start-up) and then will execute the individual prepared statements as and when needed. The idea is that if the statement is the same (just with different parameters) and is executed many times, you can save on the expense of parsing the SQL++ text and plan creation/optimisation every time - do it once and reuse the work.