Why your query language should be explicit | Hacker News Reader

Why your query language should be explicit

blog.hiphipjorge.com

44 pointshiphipjorge46 comments

> In this query, we get all the users with the name 'jorge' are queried and then ordered in descending order by age.

> SELECT * FROM users WHERE name = 'jorge' ORDER BY age;

> If we wanted to dig deeper into this query, we might want to know if the "WHERE" is getting executed before the "ORDER BY". Can we tell from the query if this is the case? No, we can't. You'd have to look it up.

No I wouldn't. I can look right at that query and tell you what order those two things occur in. The order makes sense -- of course you don't sort the results before you filter the results, the SQL database is not a moron. In fact, in this weird "explicit" query language, you have to remember the SAME ORDER -- put the WHERE in front of the ORDER BY (or the filter in front of the orderBy, in RethinkQL logic). Except if you forget the order, you can end up creating a query that performs many times worse than it has to. Whereas in SQL, even if you forget the most basic information about the query plan possible, the query planner will choose a pretty good execution strategy for your query.

And if you can't figure out how that simple SQL query is going to be executed by the server just by reading it, why on Earth do you need a query language that does not just allow but requires you to to set your own query plan? The ability to shoot yourself in the foot isn't a feature.

Also important thing to note is that optimal query plan can be different based on the data you have.

To get a good performance you will chose a different strategy when your query has 3 jorges out 1M records vs if you have 900k jorges in 1M records or when the table only contains 100 entries.

Why indexes are not always a good idea? To use an index you need to make at least two lookups per entry, one for index and one to fetch the data. If your query will fetch 900k rows out of 1m rows table, much faster is just to read the data and just filter out values we are not interested in.

In terms of order, it's not clear that the order by happens "after" the where clause, for instance, what if the results are pre-ordered? If the index used guarantees an order, and that order is the same as your order by, it is redundant and no ordering will need to occur.

However, I agree with the thrust of your statement, and if you cant figure out SQL's query plans, writing your own from scratch may be a tall order.

The SQL version reads left-to-right, similar to how ReQL's version reads top to bottom, and both reflects the execution order (in terms of a common way of text flow). From my understanding, ReQL does not do any form of optimization based on the complete query, the `.()` punctuates the flow of command. Whereas and SQL statement is just one compound thing made up of different commands where the SQL engine can optimize, which makes it hard to tell.

Every so often the optimizer does it wrong. Instead of reading query plans [new language] and adding a layer of hints [yet another language], how about having a language that allows you to specify the precise order when you eventually care, and optimizes anyway by default. As opposed to three languages layered over each other in a fairly ad-hoc way.

    val q = Q.from(table).select(foo).filter(foo > 3)
    val q = Q.from(table).select(foo).filter(foo > 3).verbatim()

PS. Also, the query plan language is now the same as the query specification language.

This is a great idea. It's usually known as the relational algebra.

Ability to shoot yourself in the foot IS a feature (or inevitability, depends on how you look at it). Requirement to shoot between feet is not, though.

The problem here is that SQL is declarative and there is nowhere to dig deeper - data set is described. That's what SQL does - describes data sets. And any attempt to dig deeper will lead to imperative code.

With SQL I would actually hope that I cannot (statically) look up execution order without taking indexes, partitions and what-not into account.

I don't agree with this conclusion. I've worked with Postgres, MySQL and Oracle, and found that it is important to have a good understanding of the possible execution plans for a query. I will sometimes construct a query very carefully to achieve a particular plan. And when things go wrong, I run EXPLAIN PLAN, examine statistics, etc., and tweak my query to do what I want. You really have to do that to obtain good performance in some cases. It does undercut the claim that SQL has to being non-procedural, but that's life.

I do NOT want to have to construct the query execution plan manually for every single query. Usually, the optimizer will do a fine job, assuming the database designer has made good choices regarding indexes and other physical design issues. But there are always a few complex, tricky queries where you do have to understand internals. The "cognitive load" is the same, and having a high-level query language means that when you do make your subtle and deft change to rescue performance, you often just tweak a query, instead of rewriting a detailed query plan.

(Extending this position a little: I have come around 180 degrees and consider ORMs to be a bad idea overall. When you do need to be very careful about writing a query, the ORM adds a layer of complexity -- not only do you need to control the SQL, but you now need to ensure that your ORM can produce that SQL.)

In theory, if you had an explicit query language, you could write the query planner in that language. You could then rely on the default query plan by calling functions invoking it, or make part or all of that plan explicit by invoking the lower-level functions directly.

When you do need to be very careful about writing a query, the ORM adds a layer of complexity -- not only do you need to control the SQL, but you now need to ensure that your ORM can produce that SQL

A good ORM will let you run manually written SQL queries and map the results to objects. For example in Django: https://docs.djangoproject.com/en/1.8/topics/db/sql/

Or even better, an ORM can be layered on top of an SQL abstraction that maps directly to what the database understands: http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html

It then becomes easy to go one level below the ORM without giving up all the nice things.

Right, so an ORM makes the easy things easier. The hard things are either harder or you need the escape hatch.

I've both created an ORM (long ago, back in Java 1.0 days) and used a few. My conclusion is that the real pain in interacting with a database isn't writing SQL, it's managing the API (e.g. JDBC). Managing PreparedStatements, managing ResultSets, handling exceptions, getting rid of warnings, mapping to objects (if that's what you want), extracting each field and converting appropriately, binding parameters (again, with appropriate conversions).

I eventually decided that my ideal ORM just managed all that crap and let me write the SQL. (Of course, it's only approximately 0.5 of an ORM at that point, but I don't care if it no longer deserves the title.) It did the book-keeping, and let me focus on 1) the application (written in Java), and 2) the SQL. I then discovered that iBatis is based on this idea, (I think the name is slightly different now).

> Right, so an ORM makes the easy things easier

It certainly makes migrating from MySQL to Postgres a lot easier.

I guess it depends on your needs. On the platform I work on, the user can configure a set of security restrictions on tables (e.g. users of group 1 can only read records of table X which have public = TRUE; users of group 2 can only update records of table Y which have user_id = [their own user id]), and the ORM automatically inserts those restrictions into any query made to those tables.

Doing this with manually written queries would be, if nothing else, a PITA. Then again, it's a specific need; I suppose most applications have static, not dynamic rules.

It sounds like a VIEW would handle this restriction also. And then you should be able to use any ORM or lower level of abstraction.

The real trouble comes when your carefully manually tuned version of the query which works optimally for the data and loading and database version you have on date X, is still running on date X + 5 years. When the DB engine has upgraded its optimizer, and the data patterns have changed, and the usage levels are very different. And the original, unoptimized, declarative query would now be able to run faster.

But you're not in any worse position than those who did it all manually in the first place. In an ideal world the query planner would take care of everything and as the shape of the data changes it could create the appropriate optimal queries.

Doing that manually is just wasteful you'd spend all day tweaking queries that are now running suboptimally because of data changes. It would be pretty upsetting to have to change all your rethink queries to use a new index you didn't put in initially.

I think this article is just making excuses for rethink not having a query planner/optimizer and my guess is that one day they'll end up having to create one. I guess Postgres didn't have one once upon a time either and now it's incredibly sophisticated.

separation of concerns - a declarative query and the plan to execute it are 2 different artifacts. Thus DBA can upload new plans or regenerate plans for the same declarative query when things change like you described. If your database doesn't support it - well, choose your database carefully :)

SQL is declarative: you tell the RDBMS what you want and its job is to do it. That's how SQL is and how it's always been. A better RDBMS will optimise the execution plan for you. The point about indexing is somewhat valid, but that's an integral part of schema design and something one should define from the outset based on the data's intended use.

By 'explicit', what I presume the author to mean is 'transparent'. I agree that development processes should be transparent, but I don't necessarily agree that imperative is better than declarative. Indeed, the use case for SQL is data manipulation and analysis; arguably that doesn't come under the remit of 'development', even though programming is involved. Hence the prerequisite of a properly setup schema by someone who knows what they're doing!

Declarative languages definitely have their place.

Exactly. I don't want to micromanage how the RDBMS executes each and every query. My job is to tell it what kind of results I want, and its job is to get me those results. I don't care how it does its job as long as its gets the job done quickly and efficiently.

I wonder if there's a large intersection between people who like to micromanage their query plans and people who like to micromanage their employees.

The biggest problem with a procedural/explicit query is a dynamic system. Without a query planner, you don't have the luxury of a system rewriting your queries. When Table A ~ Table B, but then Table B >> Table A, your queries are going to be radically suboptimal.

Of course, if you're never joining, maybe that's not such a big problem, but you'd have the same issue of a specific range in your table grows disproportionally to another range in the same table and the index you are using is incorrect.

With SQL you can often be very explicit using Common Table Expressions as well in DBs that support it. Otherwise, using subqueries, GROUP BY with HAVING, and several other features often, but not always, prevent radically rewritten queries.

Finally, with SQL on many DBMSs you can still get the explicit wiggle room you need using optimizer hints. No, it's not very portable, but neither is ReQL.

Edit: A few examples of being explicit in SQL (a lá Oracle):

    SELECT /*+ INDEX(name) */ *
      FROM users
      WHERE users.name = 'jorge';


    WITH users_by_name AS (
      SELECT /*+ INDEX(name) */ *
        FROM users
        WHERE users.name = 'jorge'
    )
    SELECT * 
      FROM users_by_name
      JOIN profile using (user_id)
      ORDER BY age;

Even if you're not doing any joins, the selectivity of your data matters. It's more efficient to perform a table scan than using an index if the selectivity of your filter is low. I wouldn't want to have to manually rewrite a procedural query when my data changes.

I feel like this is one of those "X is categorically better than Y" or "X is better than Y because Z" articles. The problem with these types of articles is that they tend to take an extreme position when the reality is much more nuanced. For the subset of relatively simple problems the author has encountered, perhaps the procedural query is easier to reason about, but there are vastly complex SQL queries that wouldn't benefit from an explicit query like this.

Yeah that's definitely true, I tried to allude to that with the ranges example, "Elvis" would be good for an index, whereas "Chris" or "Matt" you might as well do a full table scan.

> The biggest problem with a procedural/explicit query is a dynamic system. Without a query planner, you don't have the luxury of a system rewriting your queries. When Table A ~ Table B, but then Table B >> Table A, your queries are going to be radically suboptimal.

This was exactly my thinking. Worse yet, a good query optimizer will rewrite your queries as your data changes. So when tables grow or shrink, indexes are added or removed, and even as the distribution of the data within each table changes, you'll get execution plans that roll with the punches.

If you've explicitly codified your execution plan, you don't get any of these advantages. Any changes, and you've got to re-write all your queries.

So many of these NoSQL databases leave me shaking my head. Maybe I just don't get it, but I feel like once you understand how RA, DRC, TRC, and SQL are equivalent, you'll never want to write relational algebra again.

Cost models aren't perfect but they're pretty good. System R-esque systems can evaluate like, A LOT of join orderings. You don't wanna just throw that power out on the whim that the developer might know better.

He says he's been using the explicit query language for a couple of months. On that timescale, I can see how it might still feel okay. But as the months become years and your code grows in complexity to meet an ever lengthening requirements list, it should become apparent why SQL is far superior.

Need to add an index to speed up some queries?

In SQL, you add the index and you're done.

In an explicit query language, you add the index and... oops, that doesn't do anything. You've got to go back and inspect every single query, anywhere in your program, that could potentially benefit from that index, to see whether it actually will, and if so, modify it by hand.

Switching from SQL to an explicit query language converts certain types of programming effort from O(N) to O(N^2). This is one of the reasons SQL was invented in the first place.

Jorge writes:

> Does this increase cognitive load? Yes, it does. But this is outweighed by the ability to understand how your query is being executed. … Hence, when you see a query you immediately know that it's using an index…

This reminds me of something I read in a paper once:

> Accordingly, it provides a basis for a high level data language which will yield maximal independence between programs on the one hand and machine representation and organization of data on the other.

It seems like this Jorge dude is claiming that it's great that, if you use his company's product, you have to change your program when you change the representation and organization of data on your disk, and that there are no real disadvantages to this. I think maybe he should read the paper I'm quoting from above, which is Codd 1970, introducing the relational database: https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf — Codd explains why the 1960s CODASYL systems similar to RethinkDB made programs unmaintainable.

If you don’t understand why relational databases got adopted in the first place, you aren’t qualified to “rethink databases” or to call yourself a “full-stack developer”. And your gullible customers, although they may get a prototype built quickly, will be outcompeted by their rivals who aren’t afraid of using query optimizers. Jorge must think we're all fucking idiots who don't know why we abandoned products like RethinkDB thirty or forty years ago.

Fortunately, the HN thread is much more intelligent and informed than the original article!

I don't agree with the essay's premise.

>Having your query language be explicit means that you've hit at exactly the right level of abstraction: not too much, but not too little.

I don't like the label of "explicit" as if it's an objective indicator on the continuum between low and high abstraction. It comes across as a value judgement that we'd all agree on and I don't think there's obvious consensus on architecting a data access language.

>If we wanted to dig deeper into this query, we might want to know if the "WHERE" is getting executed before the "ORDER BY". Can we tell from the query if this is the case? No, we can't. You'd have to look it up.

Not knowing the internals of execution is actually a deliberate design feature of SQL. The SQL is meant to be a declarative statement that expresses an algebraic set of rows. (But sometimes, the mathematical purity of this abstraction "leaks" and DBAs/Devs have to add HINTS or do SQL EXPLAIN PLAN to dig into what's happening under the hood -- but that's a separate issue.)

I suppose if one really wanted to affect the order of operations at the SQL syntax level, one could write a VIEW or a subquery with the ORDER BY and then write the outer query with the WHERE clause. I haven't tested this to see if any of the major SQL engines would rewrite this type of convoluted SQL of ORDER BY -then- WHERE clause.

Yes, with UNIX command line, you have different execution characteristics of "ls | grep | sort" vs "ls | sort | grep" but one can't translate that explicit-sequence-of-execution mental model to SQL.

* Does this increase cognitive load? Yes, it does. But this is outweighed by the ability to understand how your query is being executed.

I'm not convinced of this conclusion.

Also, I'm not sure RethinkDB works like this as a deliberately engineered advantage. The RethinkDB devs can clarify but it's possible for their engine to work like this because it's more straightforward to implement the parser and not because there is overwhelming inherent superiority to this approach. With traditional SQL (e.g. Oracle, MSSQL, etc), the query rewriting engines are very mature and can be more aggressive and thereby fulfilling the goal of a declarative mathematical purity. However, it takes lots of programmer man-hours to translate declarative SQL into optimal execution plans.

This is a great way to spin not having a query planner as a feature, but I'm glad to have one every day that I write and compose semantic bits of SQL that can and should have different execution plans depending on the context in which they're evaluated.

The whole article is like one long spin job. "Explicit" is somehow the happy medium between the query engine putting together a plan based on data statistics and... I'm not even sure what space this claims to be the middle of. Keeping the examples simple enough so that there is an unambiguously right order of operations - filter before you sort, that's genius! There's no way your granddad's RDBMS could have figured that one out! What if the optimal order depends on the values, or changes over time? Just rewrite and recompile?

As others have already mentioned, what the author is calling "explicit" would be more typically called "imperative". I'm going to go further though and say that I think "explicit" as used here is actually wrong. Take the example from the article:

  SELECT * FROM users WHERE name = 'jorge' ORDER BY age;

and assume that we have exactly one index on the table, namely a compound one on `name, age`. If that index is used, then _only_ the filtering need be done. In SQL, because the execution sequence is left unspecified, we can continue to write the query as is while still allowing the DB to skip the unneeded ordering step (whether it does so or not is a different question obviously).

If, however, the execution sequence must be "specified" (cf. "explicit") and you don't want to perform the unnecessary ordering step, then either the order must be left out of the query (and thus implicit), or the DB needs to be able to ignore what you tell it to do.

No. Learn to trust the query planner. It's a good thing. When you want explicitness, take a look at the explain plan.

I think this is a silly argument. It's much easier to write a database without query planner than with a query planner.

The reason why you would want to have implicit language is because an optimal query might be different depending on what data you have and even what are you querying.

For example if table has only 5 jorges it's probably better to use an index, but if majority of users are jorges or the table is very small it's far more efficient to just scan it.

I hate to criticize but this stood out to me:

> SELECT * FROM users WHERE name = 'jorge' ORDER BY age;

> Can we tell from the query if this is the case? No, we can't. You'd have to look it up.

and then:

> r.table('users').filter({ name: 'jorge' }).orderBy(r.desc('age'))

> Now, can you tell from the query if the users are filtered or ordered first? Yes! filter comes first.

The filter comes first in both queries. It's exactly the same.

The part about indexes is interesting though.

The author's point here is that in ReQL the order things happen in is the order you write them in, which is not the case in SQL. (In SQL the order things happen in is partially baked into the language -- see the page that "look it up" links to in the article -- and partially at the discretion of the query plan optimizer.)

What I want is a language with feedback.

In other words, a language that calculates all of the various optimizations behind-the-scenes, and sees what ones it thinks would be good and suggests them to you. And you can add annotations to allow it to do specific optimizations.

It has control and transparency, but keeps it relatively easy to optimize. And you can hide the annotations if you really wish.

This seems like a massive step backwards. And it's not for the benefit of the user (the programmer, she needs to do more work). It's for the benefit of the RethinkDB programmer (query planners are hard work!). Add an index in SQL? Existing queries work and can make use of it. Add an index in RethinkDB? Now go rewrite all your code if you want to take advantage of it. That's an improvement? (And do you think the average javascript programmer will do a better job at it than 30 years of database research?)

If you're trying to spin shit into gold, maybe you should try a rethink preprocessor. Just write normal SQL in your code and the pre-processor verifies the tables and columns, checks for indices, and writes the best "explicit" query for you.

I think explicit languages can make things more clear however disagree with the notion that implicit behaviors are necessarily a bad thing.

I've found that the hybrid approach in the MongoDB aggregation framework works really well.

It optimizes things around the first $match to create an optimized initial read (the selectivity of your initial stages is really important). Once you're past the initial read the rest of the pipeline is fully imperative.

This makes things really nice when debugging complex aggregation pipelines. For example, you can simply omit the rest of your pipeline at any point to debug (with a $limit), see what you're dealing with, fix them, and move on to the next one.

Have you built any really big data projects with RethinkDB or just demo apps? There are some big advantages to having query planners and optimizers in the database. Also in SQL you can be explicit. You can tell it what index to use.

> But RethinkDB won't optimize the query for you or tell you it's wrong. It'll just run it. It's up to the developer to understand what's going on and optimize accordingly. This might sound like a huge deal, but the simplicity of the language makes it easy to spot these inefficiencies and fix them accordingly.

That's the kind of thing that's only true until it's not. The bigger a query gets, the less likely that you'll be able to eyeball it to see what you screwed up.

Besides the fact that I don't agree with his conclusion - he ignores any of the advantages of an implicit language and therefore the article suffers from the "everybody must be stupid" syndrome...

"Jorge Silva, Dev Evangelist @ RethinkDB. Full-Stack JavaScript Developer."

That final sentence makes me cringe slightly.

"There are two types of people in this world. Those who agree with me and those that are stupid."

Programming has a lot of areas where it is split down the middle into two camps (usually two, although occasionally more...)

Not equally, mind you, but two very strongly opinionated camps.

Semicolons come to mind.

5 years later there will be an article about great new feature in XX-DB - a query planner.

A lot of people are saying 'just trust the query planner'.

The best of both worlds would be the ability to explicitly define how to get what you want just as easily as you can define what you want.

That should be the goal.

SQL gives you all of the tools you need to hang yourself if you're bent on it. FORCE ORDER will force joins to happen in the order specified, index / join hints will force the optimizer to do things the way you'd like.

The reason these things aren't seen often is because they are a terrible code smell. An index that works today may not be the best bet tomorrow. The beauty of the optimizer is it takes the (estimated) statistics available to it on execution to build a performant plan.

The author is basically taking a huge no-no (forcing specific index use / order of operations) and somehow trying to sell that as a positive.

"In this query, we get (sic) all the users with the name 'jorge' are queried and then ordered in descending order by age."

Am I missing something, or should that say "ASCending" ?

Descending is probably a better default.

Reply on news.ycombinator.com