Re:Finding an authorization bypass on my own website
by hn_throwaway_99 on Sunday March 06, 2022 @11:04AM
(#30578445)
> I thought that when I send a parameterized query to PostgreSQL or MS SQL Server, most (or even all) of the query plan gets created without looking at any parameter values.
Your comment peaked my interest so I looked into what postgres does, and it's pretty ingenious in my opinion. It can create either a generic plan (not looking at parameter values) or a custom plan where it looks at parameter values and then uses statistics to find the best plan.
By default, for the first 5 executions of a prepared statement uses a custom plan. If the execution costs of these different plans are pretty close to each other, from then on it just uses the generic plan (idea being that the extra cost of generating a custom plan each time isn't worth it), but if they are different enough (meaning looking at statistics can have a big speedup), then custom plans are used going forward. This behavior can be tweaked using DB flags.
Your comment peaked my interest so I looked into what postgres does, and it's pretty ingenious in my opinion. It can create either a generic plan (not looking at parameter values) or a custom plan where it looks at parameter values and then uses statistics to find the best plan.
By default, for the first 5 executions of a prepared statement uses a custom plan. If the execution costs of these different plans are pretty close to each other, from then on it just uses the generic plan (idea being that the extra cost of generating a custom plan each time isn't worth it), but if they are different enough (meaning looking at statistics can have a big speedup), then custom plans are used going forward. This behavior can be tweaked using DB flags.