request is eligible for IPE but does not meet thresholds
In TD14.10, I’m running an explain on a query and I’m getting the message “This request is eligible for incremental planning and execution (IPE) but does not meet thresholds. The following is the static plan for the request.” I am surprised that the query does not meet cost thresholds because it is a sizable/expensive query. Part of the reason the query is so expensive is because it is NOT using DPE. The value range for the partitioning column is derived from a non-correlated scalar subquery which, if I understand correctly, should allow the optimizer to use IPE. Also, when I replace the scalar subqueries with hard-coded values, the cost of the query is reduced significantly. I thought I understood the meaning of that explain message “This request is eligible for (IPE) but does not meet thresholds”, but maybe I’m overlooking some caveat. I want to run a test to prove that IPE is, in fact, working properly on my TD14.10 system, when the conditions are right. Toward that end, I have three questions:
1. If I get the explain message “This request is eligible for (IPE) but does not meet thresholds”, does that *prove* that IPE is enabled on my system?
2. Assuming the answer to the preceding question is YES, what *exactly* does the phase “but does not meet thresholds” mean? Does it mean the cost of the static plan is not high enough to consider IPE – or – does it mean the cost of the dynamic plan (when using IPE) does not show big enough improvement over the static plan, therefore IPE is not used?
3. Is there a way to adjust the cost threshold low enough (as a test) to persuade the optimizer to use IPE? If so, how? Also, can it be done via a diagnostic?
Lastly, here are some pertinent dbscontrol settings for my TD14.10 system:
SecureExplain = 0
CostProfileId = 0
EnableSetCostProfile = 1 (Enabled)
It is my understanding that the system’s default Cost Profile for my TD14.10 (Linux) system is “T2_TD13_0_LINUX64 - Default Type 2 formula parameter initialization values for TD13 on 64 bit Linux platforms.”