I find that often the big gains come from applying knowledge about the data. Take a classic, which order should the DB perform the required operations (scan a.foo, scan b.bar, match keys between a and b) for the simple join:

  SELECT * FROM a INNER JOIN b ON b.id = a.bid WHERE a.foo = 'foo' AND b.bar = 'bar'

The correct answer depends on what the tables contain. The query planner makes some educated guesses with what knowledge it has available, but in some cases it guesses consistently wrong.

RDBMS have statistics to try addressing that.

IMHO just like machine learning is statistics on steroids, I would not be surprised to see some neural networks and embeddings try to model statistics for RDBMS in the future.

These models could just as well be these that can be used to infer if a user row is bound to add another order row, and which kind of product row to go with that.

Synthetic data vault is getting there with some of the recent model implementations. A blue ocean project if I ever saw one. Not ready for prod, but is able to learn relationships between tables.

Yep agreed, and this is the main argument for using functional query languages (like LINQ) as you can control the eval order explicitly.

I'm also a fan of using CTEs in a template, so all template arguments can be grouped together at the top of the query.

Additionally, multiple CTEs in a query execute as a single transaction, and in order (well, for Postgres anyway and probably SQL server) which can allow for multiple (different dependent tables) updates in a query without the roundtrips to say the API layer.

How do you rewrite your linq query when the data in your tables change, and the eval order is no longer correct?

Well you switch to a different linq query as your heuristic changes.

This isn't a linq vs SQL debate. I'm pointing out that linq allows explicit ordering, and if you know the shape of your data, you can account for changes in it.

As with most coding, right tool for the job, YMMV for SQL or linq.

This aspect of SQL is my favorite example of a "leaky abstraction".

It's not a leaky abstraction, because it doesn't make any promises about performance. If you still call it a "leaky abstraction" then _every_ abstraction is leaky and we can simply alias "leaky abstraction" to "abstraction" and move on.

With any language, the more you tell the compiler information it doesn't know the better. Want to create an list of a million items but the default constructor is 50, have to tell the compiler.

Same with SQL. I know X is a great start to a filter since it's unique in this one use case I'm working on, for everything else us the stats for the column.

SQL has a huge number of ways to read the data, and rewritting the sql query is reprogramming how your code reads and writes data from memory/disk. Every language has the same exact problem.

> It’s interesting how much SQL, a declarative language,

I think this is the main mistake. SQL is not declarative language, is a high-level DSL:

    1 + 1 = 2 //most PLs
    SELECT 1 + 1 = 2 //SQL
    SET 1 + 1 = 2    //TCLish
    [1, 2] | sum = 2 //Functional
    <span>1</span><span>+</span><span>1</span> = NOT 2! //HTML, a TRUE declarative language!

The main issues that causes this is that SQL is most of the time a little piece disconnected here and there. You don't see how is so imperative and functional until you write manually a big .sql file.

Also, is crippled intentionally, so you don't use it for "regular" programming task (ie, not real way to do print("hello world")!.

I start with FoxPro/dBASE and never develop this disconnect because for me, in Fox, SQL was just another sub-dialect of Fox, that was a "full" programming language. So every-time I do:

    SELECT * FROM customer WHERE code = 1
    SCAN customer WHILE code = 1 //Equivalent Fox CMD
        ?customer
    ENDSCAN 

And similar how in Fox you know that your filters and sort depend on indexes then the same with SQL.

I still look SQL and see it imperatively and have a good grasp on how everything execute (ie: at least until the query planner disagree with me!).