Re: Adaptive Plan Sharing for PreparedStmt

Hi,

On 5/20/21 5:43 AM, Andy Fan wrote:
> Currently we are using a custom/generic strategy to handle the data skew
> issue. However, it doesn't work well all the time. For example:  SELECT *
> FROM t WHERE a between $1 and $2. We assume the selectivity is 0.0025,
> But users may provide a large range every time. Per our current strategy,
> a generic plan will be chosen, Index scan on A will be chosen. oops..
>

Yeah, the current logic is rather simple, which is however somewhat on
purpose, as it makes the planning very cheap. But it also means there's
very little info to check/compare and so we may make mistakes.

> I think Oracle's Adaptive Cursor sharing should work. First It calculate
> the selectivity with the real bind values and generate/reuse different plan
> based on the similarity of selectivity. The challenges I can think of
> now are:
> a). How to define the similarity.  b). How to adjust the similarity
> during the
> real run. for example, we say [1% ~ 10%] is similar. but we find
> selectivity 20%
> used the same plan as 10%. what should be done here.
>

IMO the big question is how expensive this would be. Calculating the
selectivities for real values (i.e. for each query) is not expensive,
but it's not free either. So even if we compare the selectivities in
some way and skip the actual query planning, it's still going to impact
the prepared statements.

Also, we currently don't have any mechanism to extract the selectivities
from the whole query - not sure how complex that would be, as it may
involve e.g. join selectivities.

As for how to define the similarity, I doubt there's a simple and
sensible/reliable way to do that :-(

I remember reading a paper about query planning in which the parameter
space was divided into regions with the same plan. In this case the
parameters are selectivities for all the query operations. So what we
might do is this:

1) Run the first N queries and extract the selectivities / plans.

2) Build "clusters" of selecitivies with the same plan.

3) Before running a query, see if it the selectivities fall into one of
the existing clusters. If yes, use the plan. If not, do regular
planning, add it to the data set and repeat (2).

I have no idea how expensive would this be, and I assume the "clusters"
may have fairly complicated shapes (not simple convex regions).

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company