[
https://issues.apache.org/jira/browse/PHOENIX-7258?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17824652#comment-17824652
]
ASF GitHub Bot commented on PHOENIX-7258:
-----------------------------------------
sanjeet006py commented on code in PR #1851:
URL: https://github.com/apache/phoenix/pull/1851#discussion_r1517375166
##########
phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorOffsetIT.java:
##########
@@ -1221,4 +1222,29 @@ public void rvcOffsetTrailingNullKeyTest() throws
Exception {
}
}
+ // Test point lookup over data table with index hint and hinted plan is
not applicable
+ @Test
+ public void testRVCOffsetWithNotApplicableIndexHint() throws Exception {
+ String sql = String.format("SELECT /*+ INDEX(%s %s)*/ %s FROM %s "
+ + "WHERE t_id = 'b' AND k1 = 2 AND k2 = 3 OFFSET
(%s)=('a', 1, 2)",
+ TABLE_NAME, INDEX_NAME,
TABLE_ROW_KEY,TABLE_NAME,TABLE_ROW_KEY);
+ try (Statement statement = conn.createStatement()){
+ ResultSet rs = statement.executeQuery("EXPLAIN " + sql);
+ String actualQueryPlan = QueryUtil.getExplainPlan(rs);
+ // As hinted plan is not applicable so use data plan which is
point lookup
+ assertTrue(actualQueryPlan.contains("POINT LOOKUP ON 1 KEY OVER "
+ TABLE_NAME));
+ }
+ }
+
+ @Test
+ public void testRVCOffsetWithNotApplicableAndPointLookup() throws
Exception {
Review Comment:
Yes, I added this test as I am doing changes for point lookup case and
wanted to assert that I am not breaking the scenario where its point lookup but
no query plan is applicable. Just wanted to be extra sure.
> 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
> Affects Versions: 5.2.0, 5.1.3
> 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)
