Comment by dspillett | Hacker News Reader

Comment by dspillett | Hacker News Reader

Parent Full thread

> "from" should come first to help auto-complete

I may be in a minority, but I like the projection list being first in simple queries. To help with auto-complete just type the other parts first then fill in the select list after? This falls apart with CTEs though as they have to come first syntactically, so now they exist maybe at the end is the best place for the output list so it is easy to find in a complex statement.

> Many developers end up writing huge SQL statements

Compossibility can definitely be a significant problem with SQL. There are structures that can help significantly, views, CTEs, etc, but they all have either potential maintainability issues or potential performance issues.

Partly, from a performance PoV, this is an issue with the query optimisers. On the maintainability matter I think the syntax for CTEs and sub-queries generally could have been much better thought out – I've seen a few attempts to beautify (or replace) SQL that essentially amount to moving parts around so you can make CTEs & sub-queries look more like functions/procedures in imperative languages (transpiling the result back to SQL for submission to the database after).

> Regular programming languages don't require "query optimizers".

Because regular programming languages are “lower level” in that regard and require you to do that optimisation yourself. The point of a language like SQL is that you describe the shape and content of what you want and don't have to worry about the most efficient way to retrieve that information.

And come to think of it, many regular languages do have optimisers. Think of all the work a compiler is doing or the JIT compiler in JS engines and their ilk – they don't just blindly interpret each line of C/other into a distinct selection of CPU instructions. Consider declarative frameworks for those languages too, like many used for SPAs which deal with DOM manipulation optimisation for you, and the many attempts at no-code or low-code development solutions which seek to abstract such concerns away from the solving of problems that require programming (by doing what SQL attempts: trying to translate what you want, stated as simply as possible, into what is needed to do that efficiently).

> regular programming languages are “lower level” in that regard and require you to do that optimisation yourself. The point of a language like SQL is that you describe the shape and content of what you want and don't have to worry about the most efficient way to retrieve that information.

Yes! But I wonder: wouldn't it be better if the developer has to specify how he wants to retrieve the information? In a regular programming language, typically people don't complain too much about this "missing feature" of a query optimizer. It's about modularization / abstraction. In SQL, the data model is king; in a regular programming language, the API is king. Yes, compilers have optimizers, but they optimize the machine instructions, not (so much) the algorithms that are used (scan vs using an index / using which index).

Maybe I just have seen too many cases where developers didn't create the necessary indexes, or databases did't use the (obviously) best indexes, or developers writing 10'000 lines SQL statements (that I then re-wrote in Visual Basic, and the resulting program was much faster). Or 1000 line GraphQL query that returns 50 MB of JSON. Yes, I have also seen Java developers writing exponential algorithms or worse, and running into memory issues - but I personally found it easier to analyze and optimize then SQL. Maybe I have some bias thought.

> Yes! But I wonder: wouldn't it be better if the developer has to specify how he wants to retrieve the information? In a regular programming language, typically people don't complain too much about this "missing feature" of a query optimizer.

On the other hand, as you noted, SQL is forever while programs and programming languages come and go. Maybe having a declarative data mindset is appropriate since data is more valuable and durable than code.

> > regular programming languages are “lower level” in that regard and require you to do that optimisation yourself.

> Yes! But I wonder: wouldn't it be better if the developer has to specify how he wants to retrieve the information?

I'd be against making SQL lower level in that sense – part of its purpose is to be back-end agnostic and once you start letting the dev get too details you are locking code to one implementation. Of course this isn't how things generally work anyway beyond the basics, with engine specific hints and such, but let us not intentionally make that more of an issue!

Though if we do go deliberately back-end specific, perhaps a way of describing a query plan more directly and saying “use this” is what you are wanting. Something like plan forcing in SQL Server's query store, but with you actually dictating the plan not picking one the engine has created from SQL. That way SQL stays purer but you have the option of something more direct, like mixing bits of (obviously platform specific) assembler into your C or other higher-level code. There will still be terrible code created that way though – possibly much worse.

> part of its purpose is to be back-end agnostic

Low-level languages can be backend agnostic. For example C or Rust allows to write low-level code that is back-end agnostic, and doesn't cause cause vendor or technology lock-in.

What I like to have is guarantees similar to what a RTOS (real-time operating system) has. Maybe using hints, or something like that. This could be standardized. Languages like C or Rust have such guarantees naturally: statements have some kind of guaranteed time constraints (that's why RTOSes can be mostly written in C). Sure, loops can be endless, but loops, e.g. in Rust, can't become 1 million times slower suddenly because the backend changed.

> But I wonder: wouldn't it be better if the developer has to specify how he wants to retrieve the information? In a regular programming language, typically people don't complain too much about this "missing feature" of a query optimizer.

Why not use a key–value store or just a file system, if you want to write everything yourself?

I don't want to write everything myself. (Well, I did write 3 relational database engines, and 4 query optimizers... Hypersonic SQL, PointBase Micro, H2 database, and the Apache Jackrabbit Oak query engine.)

I want a good abstraction (API) that allows to write programs that access and manipulate data in an efficient and simple way. But I argue it shouldn't be a declarative set manipulation language like SQL, but a language that is more low-level and makes it hard to (by mistake) retrieve or update millions of rows, or makes it hard to write statements that are very inefficient, and makes it hard to have systems that lack encapsulation, so you end up having hundreds of tables that are entangled in a big mess.

I do like the fact that network roundtrips can be reduced: you can send SQL (or GraphQL) statements from the client to the server, where the statements are processed and the result is returned. But it's too easy for the result to become large, or statements too slow to be processed. With a key-value store (like Redis) you can't do that. Or can you send a list of commands to Redis (or some other key-value store) that are executed in a safe and efficient way (with guaranteed runtime) and the result is returned? That would be what I look for.

Existing query optimisers are very weak. You can write multiple semantically equivalent queries but some will be orders of magnitude slower. I would pay good money for a Postgres that I could tell to spend several minutes analysing a query before caching the query plan and reusing it for the future, instead of having to spend programmer time tweaking things.

query plans are data-dependent. mssql does cache query plans in some cases and it's a major caveat in using e.g. stored procedures (you have to make sure a good-enough plan is cached.)

What I’m saying is the plans have a tiny budget devoted to their optimisation. For something like a view, I would happily let the optimiser run for hours finding a speedup if it were then reliably delivered. As it is, none of that happens and you often have to rewrite to a functionally identical but faster query. The furthest you can go on something like Postgres is tweaking some config and turning off stuff like GEQO in favour of brute force etc.

> if it were then reliably delivered

This is the limiting factor there. The best plan is going to vary depending on data patterns and (if variables are involved) input parameters. You would have to spend a potentially infinite amount of time testing for different patterns and store which plan is best for each, or do the analysis for every possible parameter each time the referenced data changes.

> plans have a tiny budget devoted to their optimisation

The reason planners get minimal time for planning is that in the general you quickly hit the point of diminishing returns. Their job is to find something good enough and find it quickly. They are helped by pre-computed hints like index stats, so you could perhaps spend more time building other helpful pre-computed data but you would need to reassess this data regularly (as index stats are reconsidered under certain conditions) and again you hit a point of diminishing returns in terms of the amount of time you spend on this as data it modified compared to the potential benefits from better plans.

> For something like a view

Databases do not create query plans for views when the view is created. They create query plans for overall statements that use the views when those statements are executed (or chose a cached one if the statement is sufficiently similar to a previous one for which there is a cached plan).

This is a very pessimistic argument, undermined by the fact that every single day, humans rewrite queries - in the supposedly declarative language SQL - to be identical but faster. I want a query planner that can be told to go away and try to do better with more resources. I don't know why this is unthinkable.

Yes, just like in browsers where one API is faster in Chrome and slower in Firefox or vice versa. Then Safari drops in and completely upends the notion of "best strategy".

The underlying implementation will always affect top level access performance. "Best" will always be implementation dependent.

That said, the DBs I use most (Postgres, MySQL, SQLite, and their derivatives) have pretty consistently improved performance from version to version with surprisingly few regressions.

Also there have been more "knobs and dials" added to get what you want.

  WITH foo MATERIALIZED AS …

springs immediately to mind. The ability to set or remove an optimization fence does wonders while still maintaining SQL's declarative roots.

Cache multiple and choose the one that fits the expected data distribution based on the inputs.

Reply on news.ycombinator.com