You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by vj...@apache.org on 2024/03/09 05:34:28 UTC
(phoenix) branch 5.2 updated: PHOENIX-7258: Query Optimizer should pick Index hint even for point lookup queries (#1851)
This is an automated email from the ASF dual-hosted git repository.
vjasani pushed a commit to branch 5.2
in repository https://gitbox.apache.org/repos/asf/phoenix.git
The following commit(s) were added to refs/heads/5.2 by this push:
new e9a74463e3 PHOENIX-7258: Query Optimizer should pick Index hint even for point lookup queries (#1851)
e9a74463e3 is described below
commit e9a74463e3454095051c3fbb1498aaf0acc8f485
Author: sanjeet006py <36...@users.noreply.github.com>
AuthorDate: Sat Mar 9 11:03:29 2024 +0530
PHOENIX-7258: Query Optimizer should pick Index hint even for point lookup queries (#1851)
---
.../apache/phoenix/optimize/QueryOptimizer.java | 5 +-
.../end2end/RowValueConstructorOffsetIT.java | 26 ++++++++++
.../phoenix/end2end/TenantSpecificTablesDDLIT.java | 57 ++++++++++++++++++++++
.../phoenix/end2end/index/PartialIndexIT.java | 47 ++++++++++++++++++
.../index/UncoveredGlobalIndexRegionScannerIT.java | 43 ++++++++++++++++
5 files changed, 176 insertions(+), 2 deletions(-)
diff --git a/phoenix-core-client/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java b/phoenix-core-client/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
index 7560f75229..faec49322b 100644
--- a/phoenix-core-client/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
+++ b/phoenix-core-client/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
@@ -210,8 +210,9 @@ public class QueryOptimizer {
private List<QueryPlan> getApplicablePlansForSingleFlatQuery(QueryPlan dataPlan, PhoenixStatement statement, List<? extends PDatum> targetColumns, ParallelIteratorFactory parallelIteratorFactory, boolean stopAtBestPlan) throws SQLException {
SelectStatement select = (SelectStatement)dataPlan.getStatement();
- // Exit early if we have a point lookup as we can't get better than that
- if (dataPlan.getContext().getScanRanges().isPointLookup()
+ String indexHint = select.getHint().getHint(Hint.INDEX);
+ // Exit early if we have a point lookup w/o index hint as we can't get better than that
+ if (indexHint == null && dataPlan.getContext().getScanRanges().isPointLookup()
&& stopAtBestPlan && dataPlan.isApplicable()) {
return Collections.<QueryPlan> singletonList(dataPlan);
}
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorOffsetIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorOffsetIT.java
index 8e42d76515..8ee6de18bc 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorOffsetIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorOffsetIT.java
@@ -42,6 +42,7 @@ import org.apache.phoenix.schema.PTableType;
import org.apache.phoenix.schema.RowValueConstructorOffsetNotCoercibleException;
import org.apache.phoenix.util.PhoenixRuntime;
import org.apache.phoenix.util.PropertiesUtil;
+import org.apache.phoenix.util.QueryUtil;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
@@ -1221,4 +1222,29 @@ public class RowValueConstructorOffsetIT extends ParallelStatsDisabledIT {
}
}
+ // 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 testRVCOffsetWithNotApplicableDataPlanAndPointLookup() throws Exception {
+ //'ab' is not an integer so this fails
+ String failureSql = String.format("SELECT %s FROM %s "
+ + "WHERE t_id = 'b' AND k1 = 2 AND k2 = 3 OFFSET (%s)=('a', 'ab', 2)",
+ TABLE_ROW_KEY,TABLE_NAME,TABLE_ROW_KEY);
+ try (Statement statement = conn.createStatement()){
+ statement.execute(failureSql);
+ fail("Should not allow non coercible values to PK in RVC Offset");
+ } catch (RowValueConstructorOffsetNotCoercibleException e) {}
+ }
}
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificTablesDDLIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificTablesDDLIT.java
index 5d82e9da8c..5a66f9c5d4 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificTablesDDLIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificTablesDDLIT.java
@@ -39,6 +39,7 @@ import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
+import java.sql.Statement;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashSet;
@@ -47,6 +48,7 @@ import java.util.Properties;
import java.util.Set;
import java.util.regex.Pattern;
+import org.apache.hadoop.hbase.TableName;
import org.apache.hadoop.hbase.client.Admin;
import org.apache.phoenix.coprocessor.TaskRegionObserver;
import org.apache.phoenix.exception.SQLExceptionCode;
@@ -58,10 +60,13 @@ import org.apache.phoenix.schema.ColumnNotFoundException;
import org.apache.phoenix.schema.PTableType;
import org.apache.phoenix.schema.TableAlreadyExistsException;
import org.apache.phoenix.schema.TableNotFoundException;
+import org.apache.phoenix.util.MetaDataUtil;
import org.apache.phoenix.util.PhoenixRuntime;
import org.apache.phoenix.util.PropertiesUtil;
+import org.apache.phoenix.util.QueryUtil;
import org.apache.phoenix.util.SchemaUtil;
import org.apache.phoenix.util.StringUtil;
+import org.apache.phoenix.util.TestUtil;
import org.junit.Test;
import org.junit.experimental.categories.Category;
@@ -616,6 +621,58 @@ public class TenantSpecificTablesDDLIT extends BaseTenantSpecificTablesIT {
conn.close();
}
}
+
+ @Test
+ public void testIndexHintWithTenantView() throws Exception {
+ String schemaName = generateUniqueName();
+ String dataTableName = generateUniqueName();
+ String fullDataTableName = SchemaUtil.getTableName(schemaName, dataTableName);
+ String viewName = generateUniqueName();
+ String fullViewName = SchemaUtil.getTableName(schemaName, viewName);
+ String viewIndexName = generateUniqueName();
+ try(Connection conn = DriverManager.getConnection(getUrl());
+ Statement stmt = conn.createStatement()) {
+ String createDataTable = "create table " + fullDataTableName + " (orgid varchar(10) not null, "
+ + "id1 varchar(10) not null, id2 varchar(10) not null, id3 integer not null, "
+ + "val1 varchar(10), val2 varchar(10) " +
+ "CONSTRAINT PK PRIMARY KEY (orgid, id1, id2, id3)) MULTI_TENANT=true";
+ stmt.execute(createDataTable);
+ stmt.execute("create view " + fullViewName + " as select * from " + fullDataTableName);
+ stmt.execute("create index " + viewIndexName + " on " + fullViewName + "(id3, id2, id1) include (val1, val2)");
+ }
+ try(Connection conn = DriverManager.getConnection(PHOENIX_JDBC_TENANT_SPECIFIC_URL);
+ Statement stmt = conn.createStatement()) {
+ String grandChildViewName = generateUniqueName();
+ String fullGrandChildViewName = SchemaUtil.getTableName(schemaName, grandChildViewName);
+ stmt.execute("create view " + fullGrandChildViewName + " as select * from " + fullViewName);
+ PhoenixConnection pconn = conn.unwrap(PhoenixConnection.class);
+ pconn.getTableNoCache(pconn.getTenantId(), fullGrandChildViewName);
+ stmt.execute("upsert into " + fullGrandChildViewName + " values ('a1', 'a2', 3, 'a4', 'a5')");
+ conn.commit();
+ stmt.execute("upsert into " + fullGrandChildViewName + " values ('b1', 'b2', 3, 'b4', 'b5')");
+ conn.commit();
+ String physicalViewIndexTableName = MetaDataUtil.getViewIndexPhysicalName(fullDataTableName);
+ TableName viewIndexHBaseTable = TableName.valueOf(physicalViewIndexTableName);
+ TestUtil.assertRawRowCount(conn, viewIndexHBaseTable, 2);
+ String sql = "SELECT /*+ INDEX(" + fullGrandChildViewName + " " + viewIndexName + ")*/ "
+ + "val2, id2, val1, id3, id1 FROM " + fullGrandChildViewName
+ + " WHERE id2 = 'a2' AND (id1 = 'a1' OR id1 = 'b1') AND id3 = 3";
+ ResultSet rs = stmt.executeQuery("EXPLAIN " + sql);
+ String actualQueryPlan = QueryUtil.getExplainPlan(rs);
+ assertTrue(actualQueryPlan.contains("1-WAY POINT LOOKUP ON 2 KEYS OVER " + physicalViewIndexTableName));
+ rs = stmt.executeQuery(sql);
+ assertTrue(rs.next());
+ assertFalse(rs.next());
+ sql = "SELECT val2, id2, val1, id3, id1 FROM " + fullGrandChildViewName
+ + " WHERE id2 = 'a2' AND (id1 = 'a1' OR id1 = 'b1') AND id3 = 3";
+ rs = stmt.executeQuery("EXPLAIN " + sql);
+ actualQueryPlan = QueryUtil.getExplainPlan(rs);
+ assertTrue(actualQueryPlan.contains("1-WAY POINT LOOKUP ON 2 KEYS OVER " + fullDataTableName));
+ rs = stmt.executeQuery(sql);
+ assertTrue(rs.next());
+ assertFalse(rs.next());
+ }
+ }
private void assertTableMetaData(ResultSet rs, String schema, String table, PTableType tableType) throws SQLException {
assertEquals(schema, rs.getString("TABLE_SCHEM"));
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/PartialIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/PartialIndexIT.java
index 08994c93fe..7eaa5efbd7 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/PartialIndexIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/PartialIndexIT.java
@@ -28,6 +28,7 @@ import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
+import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Arrays;
import java.util.Calendar;
@@ -821,4 +822,50 @@ public class PartialIndexIT extends BaseTest {
assertTrue(rs.getString(1).contains(fullIndexTableName));
}
}
+
+ @Test
+ public void testPartialIndexWithIndexHint() throws Exception {
+ try(Connection conn = DriverManager.getConnection(getUrl());
+ Statement stmt = conn.createStatement()) {
+ String dataTableName = generateUniqueName();
+ stmt.execute("create table " + dataTableName + " (id1 varchar not null, id2 integer not null, "
+ + "A integer constraint pk primary key (id1, id2))" + (salted ? " SALT_BUCKETS=4" : ""));
+ stmt.execute("upsert into " + dataTableName + " values ('id11', 10, 1)");
+ conn.commit();
+ stmt.execute("upsert into " + dataTableName + " values ('id12', 100, 2)");
+ conn.commit();
+ String indexTableName = generateUniqueName();
+ stmt.execute("CREATE " + (uncovered ? "UNCOVERED " : " ") +
+ (local ? "LOCAL " : " ") +"INDEX "
+ + indexTableName + " on " + dataTableName + " (id2, id1) " +
+ (uncovered ? "" : "INCLUDE (A)") + " WHERE id2 > 50");
+ // Index hint provided and query plan using partial index is usable
+ String selectSql = "SELECT /*+ INDEX(" + dataTableName + " " + indexTableName + ")*/ "
+ + "A from " + dataTableName + " WHERE id2 = 100 AND id1 = 'id12'";
+ ResultSet rs = stmt.executeQuery("EXPLAIN " + selectSql);
+ String actualQueryPlan = QueryUtil.getExplainPlan(rs);
+ assertTrue(actualQueryPlan.contains("POINT LOOKUP ON 1 KEY OVER " + indexTableName));
+ rs = stmt.executeQuery(selectSql);
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertFalse(rs.next());
+ // Index hint provided but query plan using partial index is not usable so, no data
+ selectSql = "SELECT /*+ INDEX(" + dataTableName + " " + indexTableName + ")*/ "
+ + "A from " + dataTableName + " WHERE id2 = 10 AND id1 = 'id11'";
+ rs = stmt.executeQuery("EXPLAIN " + selectSql);
+ actualQueryPlan = QueryUtil.getExplainPlan(rs);
+ assertTrue(actualQueryPlan.contains("POINT LOOKUP ON 1 KEY OVER " + indexTableName));
+ rs = stmt.executeQuery(selectSql);
+ assertFalse(rs.next());
+ // No index hint so, use data table only as its point lookup
+ selectSql = "SELECT A from " + dataTableName + " WHERE id2 = 10 AND id1 = 'id11'";
+ rs = stmt.executeQuery("EXPLAIN " + selectSql);
+ actualQueryPlan = QueryUtil.getExplainPlan(rs);
+ assertTrue(actualQueryPlan.contains("POINT LOOKUP ON 1 KEY OVER " + dataTableName));
+ rs = stmt.executeQuery(selectSql);
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertFalse(rs.next());
+ }
+ }
}
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/UncoveredGlobalIndexRegionScannerIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/UncoveredGlobalIndexRegionScannerIT.java
index e29284a951..b85ee06b37 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/UncoveredGlobalIndexRegionScannerIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/UncoveredGlobalIndexRegionScannerIT.java
@@ -27,6 +27,7 @@ import static org.junit.Assert.fail;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
+import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Arrays;
import java.util.Calendar;
@@ -51,6 +52,7 @@ import org.apache.phoenix.query.QueryServices;
import org.apache.phoenix.util.EnvironmentEdgeManager;
import org.apache.phoenix.util.QueryUtil;
import org.apache.phoenix.util.ReadOnlyProps;
+import org.apache.phoenix.util.SchemaUtil;
import org.apache.phoenix.util.TestUtil;
import org.junit.After;
import org.junit.Assert;
@@ -824,6 +826,47 @@ public class UncoveredGlobalIndexRegionScannerIT extends BaseTest {
IndexRegionObserver.setFailPostIndexUpdatesForTesting(false);
}
}
+
+ @Test
+ public void testPointLookup() throws Exception {
+ if (uncovered || salted) {
+ return;
+ }
+ String schemaName = generateUniqueName();
+ String dataTableName = generateUniqueName();
+ String fullDataTableName = SchemaUtil.getTableName(schemaName, dataTableName);
+ populateTable(fullDataTableName);
+ String indexName = generateUniqueName();
+ String fullIndexName = SchemaUtil.getTableName(schemaName, indexName);
+ try(Connection conn = DriverManager.getConnection(getUrl());
+ Statement stmt = conn.createStatement()) {
+ stmt.execute("create index " + indexName + " on " + fullDataTableName + " (val2) include (val1)");
+ // Index hint is incorrect as full index name with schema is used
+ String sql = "SELECT /*+ INDEX(" + fullDataTableName + " " + fullIndexName + ")*/ val2, val3 from "
+ + fullDataTableName + " WHERE id = 'a'";
+ ResultSet rs = stmt.executeQuery("EXPLAIN " + sql);
+ String actualQueryPlan = QueryUtil.getExplainPlan(rs);
+ assertTrue(actualQueryPlan.contains("POINT LOOKUP ON 1 KEY OVER " + fullDataTableName));
+ rs = stmt.executeQuery(sql);
+ assertTrue(rs.next());
+ // No explicit index hint and being point lookup no index will be used
+ sql = "SELECT val2, val3 from " + fullDataTableName + " WHERE id = 'a'";
+ rs = stmt.executeQuery("EXPLAIN " + sql);
+ actualQueryPlan = QueryUtil.getExplainPlan(rs);
+ assertTrue(actualQueryPlan.contains("POINT LOOKUP ON 1 KEY OVER " + fullDataTableName));
+ rs = stmt.executeQuery(sql);
+ assertTrue(rs.next());
+ // Index hint with point lookup over data table, still index should be used
+ sql = "SELECT /*+ INDEX(" + fullDataTableName + " " + indexName + ")*/ val2, val3 from "
+ + fullDataTableName + " WHERE id = 'a'";
+ rs = stmt.executeQuery("EXPLAIN " + sql);
+ actualQueryPlan = QueryUtil.getExplainPlan(rs);
+ assertTrue(actualQueryPlan.contains("FULL SCAN OVER " + fullIndexName));
+ rs = stmt.executeQuery(sql);
+ assertTrue(rs.next());
+ }
+ }
+
public static class ScanFilterRegionObserver extends SimpleRegionObserver {
public static final AtomicInteger count = new AtomicInteger(0);
public static void resetCount() {