3 ms·
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 optimiz
by 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 agosql can be arbitrarily nested/deep - so how does the code 'know' what to do
- captaintobs 4y agorecursion! https://github.com/tobymao/sqlglot/tree/main/sqlglot/optimizer https://github.com/tobymao/sqlglot/tree/main/sqlglot/optimiz...
- asadawadia 4y agoDoes 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 agoit can do everything that can be done with the query and schema. it doesn't yet do indices or physical statistics
- einpoklum 4y agoOh, 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.