2 ms·
A minority of my sqlc queries use CASE expressions, but I've never had an issue with them. As far as I can see, Postgres is good at optimizing them away. Is tha
by atombender 1y ago
A minority of my sqlc queries use CASE expressions, but I've never had an issue with them. As far as I can see, Postgres is good at optimizing them away. Is that not generally the case?
I wonder if a sufficiently smart sqlc could do the optimization itself. Basically have an SQL parser that is able to identify dynamic parts statically and encoding an efficient representation that allows constant folding at runtime.
Another weak point in sqlc is the absence of any composability, so every query has to be self-contained. But views can help you with that.
- ncruces 1y agoPostgreSQL is probably smarter than SQLite. :) Anyway, at minimum you have prepared statements where a given plan may be great for one execution and terrible for the next. Maybe the database re-prepares for you, and then it's the same as a dynamic query.
- atombender 1y agoPostgres is smarter about that, too! It evaluates whether the prepared statement is sensitive to parameter changes, and will either use a "generic" plan that's shared across all invocations, or a custom one that is hardwired to the values. There's a setting that controls the behaviour. https://www.postgresql.org/docs/current/sql-prepare.html https://www.postgresql.org/docs/current/sql-prepare.html I've not compared the actual behaviour in the codebases I work on, however.