Re:Composable SQL (Functors)
by SigmundA on Wednesday January 29, 2025 @03:25PM
(#42870687)
Parameterized sql is a good thing regardless of plan caching, but it also helps with systems that do plan caching.
That the client must explicitly prepare the statement and the preparation is tied to the connection is pretty clunky and still means the statement must be replanned for each separate connection.
Also since it is assumed by the devs of PG that prepared statements are not the norm they seem to avoid making the optimizer take its time and shoot for a fast optimizer rather than the fastest query.
DB's like MSSQL and Oracle concern themselves much less with how long it take to optimize since repetitive queries will all use the same plan automatically it also allows interesting feature like saving and loading plans from different systems and freezing them so they don't all the sudden go bad in production, but those systems also supports hints unlike PG.
That the client must explicitly prepare the statement and the preparation is tied to the connection is pretty clunky and still means the statement must be replanned for each separate connection.
Also since it is assumed by the devs of PG that prepared statements are not the norm they seem to avoid making the optimizer take its time and shoot for a fast optimizer rather than the fastest query.
DB's like MSSQL and Oracle concern themselves much less with how long it take to optimize since repetitive queries will all use the same plan automatically it also allows interesting feature like saving and loading plans from different systems and freezing them so they don't all the sudden go bad in production, but those systems also supports hints unlike PG.