4 ms·

The tradeoff in SingleStore is interesting. By default and unlike eg postgres, parameterized queries are planned ignoring the values of the parameters. This all

by jamii 2y ago

The tradeoff in SingleStore is interesting. By default and unlike eg postgres, parameterized queries are planned ignoring the values of the parameters. This allows caching the compiled query but prevents adapting the query plan - for the example in the post SingleStore would pick one plan for both queries. But you can opt out of this behaviour by wrapping each parameter in NOPARAM (https://docs.singlestore.com/cloud/reference/sql-reference/code-generation-functions/noparam/ https://docs.singlestore.com/cloud/reference/sql-reference/c...).

jsmith45 2y ago
If it ignores the parameter values, then how does it estimate cardinality? Does it optimize for the worst case scenario (which runs the risk of choosing join implementations that requiring far more data from other tables than makes sense if the parameters are such that only a few rows are returned from the base table), or does it assume a row volume for a more average parameter, which risks long running time or timeouts if the parameter happens to be an outlier? Consider that Microsoft SQL Server for example, does cache query plans ignoring parameters for the purposes of caching (and will auto-parameterize queries by default), but SQL Server uses the parameter value to estimate cardinalities (and thus join types, etc) when first creating the plan, under the assumption that the first parameters it sees for this query are reasonably representative of future parameters. That approach can work, but if the first query it sees has outlier parameter values, it may cache a plan that is terrible for more common values, and users may have preferred the plan for the more typical values. Of course, it can be the reverse, where users want the plan that handles the outliers well, because that specific plan is still good enough with the more common values.

by jamii 2y ago

jsmith45 2y ago

If it ignores the parameter values, then how does it estimate cardinality? Does it optimize for the worst case scenario (which runs the risk of choosing join implementations that requiring far more data from other tables than makes sense if the parameters are such that only a few rows are returned from the base table), or does it assume a row volume for a more average parameter, which risks long running time or timeouts if the parameter happens to be an outlier? Consider that Microsoft SQL Server for example, does cache query plans ignoring parameters for the purposes of caching (and will auto-parameterize queries by default), but SQL Server uses the parameter value to estimate cardinalities (and thus join types, etc) when first creating the plan, under the assumption that the first parameters it sees for this query are reasonably representative of future parameters. That approach can work, but if the first query it sees has outlier parameter values, it may cache a plan that is terrible for more common values, and users may have preferred the plan for the more typical values. Of course, it can be the reverse, where users want the plan that handles the outliers well, because that specific plan is still good enough with the more common values.