4 ms·

> A library to parse and optimize SQL, That's like saying "a library to parse and optimize computer programs", except probably even harder, since a compiler an

> A library to parse and optimize SQL, That's like saying "a library to parse and optimize computer programs", except probably even harder, since a compiler and runtime library can't make any assumptions about the programs they need to make run, so they're limited in the potential of utilizing all that context information. Countless person-years have been spent on this and it's still a very active fields of research and engineering. > 2x SparkSQL performance Ah, ok, so it can be slow. Never mind then, carry on :-P

marsupialtail_2 4y ago
SQLGlot is really great, check it out!
captaintobs 4y ago
I'm the author of SQLGlot. Optimizing SQL is quite common and means things like projection / predicate push downs and logical simplification. SQL can be optimized relatively easily compared to a programming language because it is declarative. For example SELECT * FROM (SELECT * FROM x) WHERE z = 1 can be optimized into SELECT * FROM x where z = 1
- asadawadia 4y ago
  sql can be arbitrarily nested/deep - so how does the code 'know' what to do
  - captaintobs 4y ago
    recursion! https://github.com/tobymao/sqlglot/tree/main/sqlglot/optimizer https://github.com/tobymao/sqlglot/tree/main/sqlglot/optimiz...
    - asadawadia 4y ago
      Does this only optimize things like `select * from table where false` type stuff or can it optimize access paths as well? such as whether an index can be used? and what ranges? or should a sequential scan be used
      - captaintobs 4y ago
        it can do everything that can be done with the query and schema. it doesn't yet do indices or physical statistics
- einpoklum 4y ago
  Oh, you mean optimizing the SQL as SQL, not optimizing the query execution? Well, some of these transformations are useful, like the one you presented. But any non-trivial transformation may be either beneficial or detrimental, depending on a myriad of factors including memory layout, compression, distribution of data, computing hardware etc.

marsupialtail_2 4y ago

SQLGlot is really great, check it out!

captaintobs 4y ago

I'm the author of SQLGlot. Optimizing SQL is quite common and means things like projection / predicate push downs and logical simplification. SQL can be optimized relatively easily compared to a programming language because it is declarative. For example SELECT * FROM (SELECT * FROM x) WHERE z = 1 can be optimized into SELECT * FROM x where z = 1

asadawadia 4y ago
sql can be arbitrarily nested/deep - so how does the code 'know' what to do
- captaintobs 4y ago
  recursion! https://github.com/tobymao/sqlglot/tree/main/sqlglot/optimizer https://github.com/tobymao/sqlglot/tree/main/sqlglot/optimiz...
  - asadawadia 4y ago
    Does this only optimize things like `select * from table where false` type stuff or can it optimize access paths as well? such as whether an index can be used? and what ranges? or should a sequential scan be used
    - captaintobs 4y ago
      it can do everything that can be done with the query and schema. it doesn't yet do indices or physical statistics
einpoklum 4y ago
Oh, you mean optimizing the SQL as SQL, not optimizing the query execution? Well, some of these transformations are useful, like the one you presented. But any non-trivial transformation may be either beneficial or detrimental, depending on a myriad of factors including memory layout, compression, distribution of data, computing hardware etc.