[
https://issues.apache.org/jira/browse/PHOENIX-7258?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Viraj Jasani reassigned PHOENIX-7258:
-------------------------------------
Assignee: Sanjeet Malhotra
> 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
> Assignee: Sanjeet Malhotra
> Priority: Major
>
> 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)
