[PHOENIX-7258] Query Optimizer should pick Index hint even for point lookup queries

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 5.2.0, 5.1.3
• Fix Version/s: 5.2.0
• Assignee: Sanjeet Malhotra
• Reporter: Viraj Jasani
• Created: 05/Mar/24 23:00
• Updated: 09/Mar/24 05:35
• Resolved: 09/Mar/24 05:35

Description

For better performance, user can create covered indexes such that the indexed columns are same as composite primary key of the data table, but with different order. For instance, create data table with columns PK1, PK2, PK3, C1, C2 columns with primary key being PK1, Pk2, PK3. In order to get better performance from HBase block caching, if the data with same PK3 values are going to reside as close to each other as possible, we can create an index on PK3, PK2, PK1 and also include columns C1 and C2.

For point lookups on the data table, it might still be helpful to query index table depending on the usecase. We should allow using index hint to query the index table for point lookup.

When the query optimizer identifies that the query is point lookup for the data table and if the "stop at best plan" is true, then it immediately returns without checking the hint. We should check for hint and if applicable index based hint plan is identified, use it.

 

Assuming getHintedPlanIfApplicable() retrieves hinted plan, it can look something like:

if (dataPlan.getContext().getScanRanges().isPointLookup() && stopAtBestPlan
    && dataPlan.isApplicable()) {
    if (indexes.isEmpty() || select.getHint().getHint(Hint.INDEX) == null) {
        return Collections.singletonList(dataPlan);
    }
    QueryPlan hintedPlan = getHintedPlanIfApplicable(dataPlan, statement, targetColumns,
        parallelIteratorFactory, select, indexes);
    if (hintedPlan != null) {
        PTable index = hintedPlan.getTableRef().getTable();
        if (hintedPlan.isApplicable() && (index.getIndexWhere() == null
            || isPartialIndexUsable(select, dataPlan, index))) {
            return Collections.singletonList(hintedPlan);
        }
    }
    return Collections.singletonList(dataPlan);
} 

We still need to be optimal i.e. if the hinted index plan is not applicable or useful, we still need to immediately return the data plan of point lookup.

Issue Links

• links to: GitHub Pull Request #1851