The SQL your graphql implementation’s ORM middleware generates won’t optimize as well as hand-written SQL in many cases.

A decent system will provide the hooks you need to hand optimize certain cases somehow, but There are always limitations and hoops to jump through and additional complexity to manage. The extra layers that are meant to make your life easier are getting in the way instead. (May or may not still be worth it, but the point is, it’s not a foregone conclusion.)

But why wont it optimize as well as hand-written SQL? How can the query optimizer even tell the difference? Surely the SQL is transformed into some kind of syntax-independent abstract query tree before it is passed to the optimizer.

There are a lot of ways to write a SQL query against a set of tables, given a set of input parameters, to get the desired result. (Not just syntactic or other superficial differences, but differences in logic and how relationships are specified.)

And, of course, an optimizer will handle different SQL queries differently.

No such thing. The database interface is SQL. The query optimizer is something run in the database.

Yes I mean inside the database engine. The engine receives SQL and parses it and then pass the query tree to the optimizer which generate a query plan. My question is how the optimizer could have problems if the SQL is generated as opposed to hand-written? How could that make a difference?

As mentioned in another the comment, because SQL that is hand-written for non-trivial cases will too often be better than what an ORM generates.

This is so hand-wavy I can't really argue against it.

> then the query optimizer would optimize that just as effectively as if the query had been written in SQL originally

...and other lies we tell ourselves to sleep soundly at night.

But just like ORMs, they do work for the simple cases which tend to abound and you can hand-optimize the rest.

> ...and other lies we tell ourselves to sleep soundly at night.

So tell me how the query optimizer could possibly tell the difference between generated and hand-written SQL?

Because the SQL will not be the same. In complex cases, SQL generated by an ORM will do a combination of "more than it needs to" and plain inefficient queries. It can only do so much with what it knows about the database and the API used to call it.

Hand-written SQL gives the author a chance to be more precise in its needs, not only in the SQL but also by pre-generating obvious indexes to be performant from the get-go.

Appropriate indexes will be utilized (by the query planner) whether the SQL is hand-generated or generated from some other query languages. It doesn't make a difference. How could it?

And the whole point of GraphQL is that you specify exactly what data you need, so overfetching is avoided. This is in contrast to traditional REST API's where you get a fixed resource.

> Appropriate indexes will be utilized

If present. And for that they need to be created, and which are the right ones is less obvious when working higher in the abstraction staircase.

> It doesn't make a difference. How could it?

Because the SQL generated by ORMs can be wildly stupid in many cases. Here's one blogpost with an example, where regular SQL and query builder that maps to SQL almost directly generate a decent query on a simple relation, while a full ORM does something stupid: https://blog.logrocket.com/why-you-should-avoid-orms-with-ex...

The article shows one query generator which generates SQL which are logically equivalent to the hand written SQL, and compares it to another tool which generate inefficient queries. So it is not an inherent problem with generated SQL, just with some particular tool. So...use the right tool?

GraphQL allows you to specify what data you need. Obviously, if you use some middleware which throws this information away and just fetches everything from the database, then you have an inefficient system. But this is not a problem inherent to GraphQL or generated SQL in general.