This is an automated email from the ASF dual-hosted git repository. vjasani pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/phoenix.git
The following commit(s) were added to refs/heads/master by this push: new 3524dbd900 PHOENIX-7258: Query Optimizer should pick Index hint even for point lookup queries (#1851) 3524dbd900 is described below commit 3524dbd9007bb1534b40d378e18bb61b6f6902a7 Author: sanjeet006py <36011005+sanjeet00...@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() {