Viraj Jasani created PHOENIX-7258:
-------------------------------------
Summary: Query Optimizer should pick Index hint even for point
lookup queries
Key: PHOENIX-7258
URL: https://issues.apache.org/jira/browse/PHOENIX-7258
Project: Phoenix
Issue Type: Improvement
Reporter: Viraj Jasani
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:
{code:java}
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);
} {code}
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.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
