Queries plan phases and prepare doubts


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)

Have you worked through: https://developer.couchbase.com/learn/n1ql-query-performance-guide perhaps?

The operators guide https://developer.couchbase.com/learn/n1ql-query-performance-guide should explain both stream and project operators. There is an explanation of phaseSwitches there too.

Basically stream is sending data to the client; you can influence it a bit with the speed the client consumes the results but possibly the greatest influence is if you have the PRETTY option set to false.
(See for example https://docs.couchbase.com/server/current/tools/cbq-shell.html#available-command-line-options-and-shell-commands if using the cbq tool.) If you’re using an SDK or the UI this won’t apply though (unless you’ve explicitly set it to true).

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.


Most Couchbase SDKs uses prepare statements except adhoc=true