Query syntax is limited. Queries are unlimited.

Predicate pushdown, group by pushdown, offset pagination, keyset pagination, join optimization, search optimization, we’ve discussed it all. Still, it’s important to understand the default, simple — albeit slow — execution flow of the query.

Lukas Eder has explained the true order of SQL execution.  Because N1QL is inspired from SQL and follows it closely, that explanation holds here as well.  I highly recommend reading it.

With N1QL, you can use the visual explain to see the plan structure and the data flow. This is an easy way to understand the order of execution before you optimize for performance by creating indexes.  I’ve used the built-in travel sample and hence forced the primary scan with the USE INDEX hint.

Here’s the visual plan.  The plan execution and the data flow is bottom up. Starts with primary index scan and a secondary index scan, ends up with the LIMIT pagination operator before returning the results to the application.

The visual explain is interactive.  You can click on each operator to see the parameters set for that operator by the optimizer, as shown for some of the operators below.

The query plan starts with this, logically.  Every optimization on top of this is thought by people/tools and selected by the optimizer to construct a dataflow machine for every query.  The goal of the optimizer is create machines that do as little work as possible and still deliver the correct results.

Posted by Keshav Murthy

Keshav Murthy is a Senior Director at Couchbase R&D. Previously, he was a Senior Director at MapR, Senior Architect for IBM, with more than 20 years experience in database design & development. He lead the SQL and NoSQL R&D team at IBM Informix. He has received two President's Club awards at Couchbase, two Outstanding Technical Achievement Awards at IBM. Keshav has a bachelors degree in Computer Science and Engineering from the University of Mysore, India, holds eight US patents and invented databases for systems of engagement.

Leave a reply