3 ms·

> The core idea of the project is to get us the convenience of specifying the "what question I want answered," but without the inconvenience of "how is the answ

> The core idea of the project is to get us the convenience of specifying the "what question I want answered," but without the inconvenience of "how is the answer computed / with which specific set of queries / where did the data come from?" So...exactly like SQL, then?

ghusbands 6y ago
The post you're replying to directly addresses that. When you write SQL: > It's no longer a simple "just works" query language the moment you have to worry about predicate flattening, join decomposition, CTEs that introduce optimization barriers, and "IN()" being faster than equivalent "JOINs". Though it doesn't seem to address how to optimize things using the GraphQL compiler, when there's a need, without massaging the queries, as with SQL.
- random_savv 6y ago
  I think the parent's point might have been: SQL tried with mixed success. What makes the GraphQL compiler different?
- obi1kenobi 6y ago
  Just like how GCC and Clang/LLVM know all the quirks of various CPUs and can optimize accordingly, GraphQL compiler aims to know the quirks of various databases (down to individual database versions: e.g., in Postgres 12 certain kinds of CTEs are no longer an optimization barrier) and optimize accordingly. This is clearly a massive challenge, but one made easier by the fact that GraphQL compiler queries (unlike SQL queries) operate at a much higher level of abstraction. In SQL, you write "here's a CTE, now recursively JOIN X.foo to Y.bar" where X and Y could be just about anything, even something where a recursive JOIN is nonsensical. If you want to put a WHERE clause, you have to decide whether it goes in the recursive CTE itself, in a separate CTE that is ordered before the recursive CTE, or if you want to wrap the recursive CTE into another SELECT and put the WHERE there. The correct answer varies from database to database, and as a function of the size, layout, and index coverage of your data. In GraphQL compiler, your queries are much more declarative in comparison: your query would say "find all subgraphs where vertex A's field 'foo' has value 123, and where A has a recursively-expanded edge (i.e. 0+ hops along that edge) to a vertex with field 'bar' with value 456". It's then the compiler's job to figure out which of the many equivalent SQL statements (or other db language queries, if you aren't using SQL) is going to be the best way to compute the result you asked for. Here's an example from our test suite: input query: https://github.com/kensho-technologies/graphql-compiler/blob/main/graphql_compiler/tests/test_input_data.py#L941 https://github.com/kensho-technologies/graphql-compiler/blob... Microsoft SQL Server-flavored compiled SQL output: https://github.com/kensho-technologies/graphql-compiler/blob/main/graphql_compiler/tests/test_compiler.py#L2449 https://github.com/kensho-technologies/graphql-compiler/blob... I'm writing a blog post about this with more detail, follow me on Twitter if you'd like to see it when it comes out.
  - darksaints 6y ago
    If you know all of the quirks of the various databases, why aren't you hacking on their optimizers? Why write a compiler that knows what's slow and what's not when you can just fix what's slow?
    - obi1kenobi 6y ago
      It's an "and" rather than an "either-or" :) When the database is open-source, and I spot something that's broken that I know how to fix, I try to fix it. Here's a fix for a severe database query planner correctness bug I contributed to an open-source database called OrientDB: https://github.com/orientechnologies/orientdb/pull/7015 https://github.com/orientechnologies/orientdb/pull/7015 Unfortunately, Microsoft SQL Server, Oracle, and many other databases are not open-source, and I can't hack on their query planners. And even if they were, SQL is an absolutely massive language (the complete spec is 100,000+ pages). The GraphQL compiler query language is tiny in comparison, the spec is maybe 10 pages: https://graphql-compiler.readthedocs.io/en/latest/language_specification/definitions.html https://graphql-compiler.readthedocs.io/en/latest/language_s... It's a lot easier to intelligently map a small language to a big one than it is to optimize the big language outright. In a sense, SQL is just not designed to be easy to optimize — it's too broad, and there are too many equivalent-ish ways of doing the same thing. This is why even after incredibly smart people cumulatively spent engineer-millennia on the query execution and optimization systems in SQL databases, we still keep having issues and there are still plenty of areas for improvement. More info and more concrete examples of "why not just write SQL" in my upcoming blog post!
      - petergeoghegan 6y ago
        > In a sense, SQL is just not designed to be easy to optimize — it's too broad, and there are too many equivalent-ish ways of doing the same thing. This is why even after incredibly smart people cumulatively spent engineer-millennia on the query execution and optimization systems in SQL databases, we still keep having issues and there are still plenty of areas for improvement. The main reason is the inherent difficulty of cardinality estimation, as the paper says. Not every optimization is worth having. There is typically a distributed cost, paid in extra planner cycles for queries that don't benefit from the optimization. This is one of the main reasons why it's hard to contribute new optimizations to the Postgres planner. Naturally, it's possible that a marginal optimization will be incredibly important to one particular query or user. It's a value judgement in each case. Frankly, I find the suggestion that SQL is not designed to be easy to optimize baffling.
- coolgeek 6y ago
  The user you're replying to presumably understands a bit about the cited inconveniences, as well as SQL, in general > user: petergeoghegan > about: PostgreSQL major contributor, committer.

ghusbands 6y ago

The post you're replying to directly addresses that. When you write SQL: > It's no longer a simple "just works" query language the moment you have to worry about predicate flattening, join decomposition, CTEs that introduce optimization barriers, and "IN()" being faster than equivalent "JOINs". Though it doesn't seem to address how to optimize things using the GraphQL compiler, when there's a need, without massaging the queries, as with SQL.

random_savv 6y ago
I think the parent's point might have been: SQL tried with mixed success. What makes the GraphQL compiler different?
obi1kenobi 6y ago
Just like how GCC and Clang/LLVM know all the quirks of various CPUs and can optimize accordingly, GraphQL compiler aims to know the quirks of various databases (down to individual database versions: e.g., in Postgres 12 certain kinds of CTEs are no longer an optimization barrier) and optimize accordingly. This is clearly a massive challenge, but one made easier by the fact that GraphQL compiler queries (unlike SQL queries) operate at a much higher level of abstraction. In SQL, you write "here's a CTE, now recursively JOIN X.foo to Y.bar" where X and Y could be just about anything, even something where a recursive JOIN is nonsensical. If you want to put a WHERE clause, you have to decide whether it goes in the recursive CTE itself, in a separate CTE that is ordered before the recursive CTE, or if you want to wrap the recursive CTE into another SELECT and put the WHERE there. The correct answer varies from database to database, and as a function of the size, layout, and index coverage of your data. In GraphQL compiler, your queries are much more declarative in comparison: your query would say "find all subgraphs where vertex A's field 'foo' has value 123, and where A has a recursively-expanded edge (i.e. 0+ hops along that edge) to a vertex with field 'bar' with value 456". It's then the compiler's job to figure out which of the many equivalent SQL statements (or other db language queries, if you aren't using SQL) is going to be the best way to compute the result you asked for. Here's an example from our test suite: input query: https://github.com/kensho-technologies/graphql-compiler/blob/main/graphql_compiler/tests/test_input_data.py#L941 https://github.com/kensho-technologies/graphql-compiler/blob... Microsoft SQL Server-flavored compiled SQL output: https://github.com/kensho-technologies/graphql-compiler/blob/main/graphql_compiler/tests/test_compiler.py#L2449 https://github.com/kensho-technologies/graphql-compiler/blob... I'm writing a blog post about this with more detail, follow me on Twitter if you'd like to see it when it comes out.
- darksaints 6y ago
  If you know all of the quirks of the various databases, why aren't you hacking on their optimizers? Why write a compiler that knows what's slow and what's not when you can just fix what's slow?
  - obi1kenobi 6y ago
    It's an "and" rather than an "either-or" :) When the database is open-source, and I spot something that's broken that I know how to fix, I try to fix it. Here's a fix for a severe database query planner correctness bug I contributed to an open-source database called OrientDB: https://github.com/orientechnologies/orientdb/pull/7015 https://github.com/orientechnologies/orientdb/pull/7015 Unfortunately, Microsoft SQL Server, Oracle, and many other databases are not open-source, and I can't hack on their query planners. And even if they were, SQL is an absolutely massive language (the complete spec is 100,000+ pages). The GraphQL compiler query language is tiny in comparison, the spec is maybe 10 pages: https://graphql-compiler.readthedocs.io/en/latest/language_specification/definitions.html https://graphql-compiler.readthedocs.io/en/latest/language_s... It's a lot easier to intelligently map a small language to a big one than it is to optimize the big language outright. In a sense, SQL is just not designed to be easy to optimize — it's too broad, and there are too many equivalent-ish ways of doing the same thing. This is why even after incredibly smart people cumulatively spent engineer-millennia on the query execution and optimization systems in SQL databases, we still keep having issues and there are still plenty of areas for improvement. More info and more concrete examples of "why not just write SQL" in my upcoming blog post!
    - petergeoghegan 6y ago
      > In a sense, SQL is just not designed to be easy to optimize — it's too broad, and there are too many equivalent-ish ways of doing the same thing. This is why even after incredibly smart people cumulatively spent engineer-millennia on the query execution and optimization systems in SQL databases, we still keep having issues and there are still plenty of areas for improvement. The main reason is the inherent difficulty of cardinality estimation, as the paper says. Not every optimization is worth having. There is typically a distributed cost, paid in extra planner cycles for queries that don't benefit from the optimization. This is one of the main reasons why it's hard to contribute new optimizations to the Postgres planner. Naturally, it's possible that a marginal optimization will be incredibly important to one particular query or user. It's a value judgement in each case. Frankly, I find the suggestion that SQL is not designed to be easy to optimize baffling.
coolgeek 6y ago
The user you're replying to presumably understands a bit about the cited inconveniences, as well as SQL, in general > user: petergeoghegan > about: PostgreSQL major contributor, committer.