Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why your query language should be explicit

blog.hiphipjorge.com

Post reply on HN
Print view View original

21–30 of 47 posts

Re: Why your query language should be explicit

#21

Post by cwyers »

> 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.

Re: Why your query language should be explicit

#22

Post by aidos »

jameshart wrote: post #18
geophile wrote: post #5

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 pe…

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.

Re: Why your query language should be explicit

#23

Post by trhway »

jameshart wrote: post #18
geophile wrote: post #5

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 pe…

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.

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 :)

Re: Why your query language should be explicit

#24

Post by takeda »

cwyers wrote: post #21

> 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…

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.

Re: Why your query language should be explicit

#25

Post by orf »

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.

Re: Why your query language should be explicit

#26

Post by takeda »

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.

Re: Why your query language should be explicit

#27

Post by kijin »

Xophmeister wrote: post #3

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 a…

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.

Re: Why your query language should be explicit

#29

Post by hobs »

cwyers wrote: post #21

> 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…

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.

Post reply on HN