> "oh you don't have to refer to the table name except when you do because of ambiguity issues"

Maybe it's easier if you think of it like helpful syntactic sugar?

> I think there's a beautiful space for some SQL-like language that just operates a bit more like a general-purpose language in a more regular fashion. Bonus points for ones where you don't query tables but point at indexes or table scans and the like

That sounds like imperative programming, which is fine for most things, but [generally] not RDBMS (or IaC, but that's a completely separate topic). You can't possibly know the state of a given table or index – the cardinality of columns, the relative grouping of tuples to one another, etc. While you can hint at index usage (natively with MySQL, via extension with Postgres), that's as close as the planner will let you get, because it knows these things better than you do.

> resolving the "programmer writes query that is super non-performant because they assume an index is present when it's not"

More frequently, I see "programmer writes query that is super non-performant because they haven't read the docs, and don't know the requirements for the planner to use the index." A few examples:

* Given a table with columns foo, bar, baz, with an index on (foo, bar), a query with a predicate on `bar` alone is [generally] non-sargeable. Postgres can do this, but it's rare, and unlikely to perform as well as you'd want anyway.

* Indices on columns are unlikely to be used for aggregations like GROUP BY, except in very specific circumstances for MySQL [0] (I'm not sure what limitations Postgres has on this).

* Not knowing that a leading wildcard on a predicate (e.g. `WHERE user_name LIKE '%ara'`) will, except under two circumstances [1], skip using an index.

> despite people intuitively understanding B-Trees or indexes.

You say that, but the sheer number of devs I've talked to who are unaware that UUIDv4 is an abysmally bad choice WRT performance for indices – primary or secondary – says otherwise.

[0]: https://dev.mysql.com/doc/refman/8.4/en/group-by-optimizatio...

[1]: Postgres can create trigram indices, which can search with these, at the expense of the index being quite large. Both MySQL and Postgres can make use of the REVERSE() function to create a reverse index on the column, which can then be used in a query with the username also reversed.

In the universe in which I "know" what index to use (an assumption that can be contested!), you saying "well silly you, the planner is too stupid to figure this out" is not a great defense of the system!

My serious belief is that all the SQL variants are generally great, but I just want this to be incremented with some lower-level language that I can be more explicit with, from time to time. If only because sometimes there are operational needs.

The fact that the best we get with this is planner _hints_ is still to this day surprising to me. Hints! I am in control of the machine, why shouldn't it just listen to me! (and to stop the "but random data analyst could break thing", this is why we have invented permission systems)

> you saying "well silly you, the planner is too stupid to figure this out" is not a great defense of the system!

I don’t think that’s what I said. At least, it wasn’t what I meant.

The planner needs accurate stats on the tables to produce an optimal output. So would you, as a human, if you were writing a query to be executed precisely as written.

It also needs to know information about the system it’s operating in – the amount of RAM it can expect to use being the most important one, but also things like the relative performance of a random disk seek vs. sequential.

The planner also has a huge leg up on most people, in that it is aware of all of the kinds of joins and lookups it can do. Off the top of my head, Postgres can scan in the following ways: sequential scan, parallel sequential scan, index scan, parallel index scan, index-only scan, and bitmap scan. For joins, it can use: nested loop, merge, hash. Do you know when all of those are ideal? What about when you have a query joining multiple tables, keeping in mind that the intermediate contents of a table that has been joined once may look nothing like what it started as?

> Off the top of my head, Postgres can scan in the following ways: sequential scan, parallel sequential scan, index scan, parallel index scan, index-only scan, and bitmap scan.

I don't think that last one is a thing. There is both "bitmap heap scan" and "bitmap index scan" though.

It’s the generalized parent of both of those (since you will not see one without the other). You’re technically correct, but I’m also not the only one [0] who uses this term.

[0]: https://www.cybertec-postgresql.com/en/postgresql-indexing-i...

> * Given a table with columns foo, bar, baz, with an index on (foo, bar), a query with a predicate on `bar` alone is [generally] non-sargeable. Postgres can do this, but it's rare, and unlikely to perform as well as you'd want anyway.

The one I see many times more often than this is having one index on (foo) and one index on (bar), then querying on both foo and bar, and assuming the database uses both indexes for best performance. It can be done, even by ancient mysql, but it's most likely going to be a lot slower than expected so they're heavily biased against doing it and will most likely just choose one of the two indexes.

SQL separates query definition from implementation because there is a planning phase between them that can be sometimes quite complex.

To choose the best path to retrieve data, you have to know what are the possible paths (using the underlying data structures, indexes but also different algorithms to filter, join…), but you should also know some data metrics to evaluate if some shortcuts are worth it (a seq scan can be the best choice with a small table..).

And the thing that will trip most humans, is that you need to reevaluate the plan if the underlying assumptions change (data distribution has become something that you never expected).

Note that the planner is also NOT always right, it heavily relies on heuristics and data metrics that can be skewed or not up to date. Some databases allow the use of hints to choose an index or a specific path.

My honest experience in a skilled team has been that people more or less start off thinking "OK, what indices do we need to make this performant", work off of that, and then in the end try to have queries that hit those ones.

I understand the value of full declarative planning with heuristics, but sometimes the query writers do in fact have a better understanding of the data that will go in.

And beyond that, having consistent plans is actually better in some ideologies! Instead of a query suddenly changing tactics in a data- and time-dependent way, having consistent behavior at the planning phase means that you can apply general engineering maintenance tactics. Keep an eye on query perf, improve things that need to be improved... there are still the possibility of hitting absolutely nasty issues, but the fact that every SQL debugging session starts with "well we gotta run EXPLAIN first after the fact" is actually kind of odd!

> I think it's still super straightforward to sit down and learn it, but it's really unfortunate that we spend a bunch of time in school learning data structures and then SQL tries really hard to hide all that, making it pretty opaque despite people intuitively understanding B-Trees or indexes.

That's one of the best things about SQL, it's declarative nature. I describe the end result, data I want to receive — not the instructions on how it should be done. There's no control flow, there's no program state, which means that my mental model of it is so much simpler.

Yes but for performance you need to know how it is done, which defeats the declarative point.

I've read countless articles on how to rearrange the "declaration of what you want" in order to get the database to do it in a fast way.

Sometimes you do, yes. Often times, though, the issue is that the statistics for the table are wrong, or the vacuum (for Postgres) hasn’t been able to finish. Both of these are administrative problems which can be dealt with by reading docs and applying the knowledge.

I think of RDBMS like C: they’re massively capable and performant, but only if you know what you’re doing. They’re also very eager to catch everything on fire if you don’t.

> I've read countless articles on how to rearrange the "declaration of what you want" in order to get the database to do it in a fast way.

While this is doubtlessly true, in many cases the “rearranging” also involves a subtle change in what you are asking the database to do, in ways which allow the database to actually do less work.

SELECT 1 WHERE EXISTS vs WHERE ID IN (SELECT ID FROM mytable WHERE …) is a great example. The former is a much simpler request despite functionally doing the same thing in the common use-cases.

Yes, although both MySQL and Postgres will automatically rewrite the latter to the former, in most circumstances. I still find it good practice to explicitly write the semijoin, to demonstrate the intent.

MySQL, but not Postgres, will also convert the inverse to an antijoin.

As an aside, this is also often a great example to demonstrate why you shouldn’t use SELECT *, since you can’t write a semijoin if you need the predicate column in the result.

I don't see how that is supporting "SQL is declarative and therefore simple". You've kept the declarative part, but it's not simple anymore. You need to know multiple declarative ways to ask for the same thing, and the consequences of each. Might as well just tell the DB how you want to do it.

There's an interesting experiment in this direction here:

https://github.com/permazen/permazen

It maps Java objects to a scalable transactional K/V store of your choice, and handles things like indexing, schema migrations and the rest for you. You express query plans by hand using the Java collections and streams framework.

I wanted to jump in here and say that what we're working on at typedb.com, in our 3.0 version (coming soon in alpha!), is that we're taking our earlier database query language and making it much more Programming-like: functions, errors containing stack traces, more sophisticated type inference, queries as streams/pipelines... I think it's super exciting and has a huge horizon for where it could go by meshing more ideas from PL design :)

Incidentally I think it also addresses what a lot of the comments here are talking about: not learning JOINs, indexing, build-in relation cardinality constraints, etc, but that's a separate point!

> TypeDB models are described by types, defined in a schema as templates for data instances, analogous to classes. Each user-defined type extends one of three root types: entity, relation, and attribute, or a previously user-defined type.

This sounds like an EAV table, which is generally a bad idea.

Re: types, Postgres allows you to define whatever kind of type you’d like. Also re: inheritance, again, Postgres tables can inherit a parent. Not just FK linking, but literally schema inheritance. It’s rare that you’d want to do this, but you can.

In general, my view is that the supposed impedance mismatch is a good thing, and if anything, it forces the dev to think about less complicated ways to model their data. The relational model has stuck around because it’s incredibly good, not because nothing better has come around.

EDIT: this came across as quite harsh, and I’m sorry for the tone. Making a new product is hard. I’m just very jaded about anything trying to replace SQL, because I love it, it’s near-universal, and it hasn’t (nor is likely to) gone anywhere for quite some time.