This is an automated email from the ASF dual-hosted git repository.
larsh 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 1fcc887 PHOENIX-5109 Allow local indexes to work for queries with
uncovered columns.
1fcc887 is described below
commit 1fcc887faac1aaa45c69b70b084a51ef7b8c5df6
Author: Lars Hofhansl <[email protected]>
AuthorDate: Thu Jan 24 21:58:49 2019 -0800
PHOENIX-5109 Allow local indexes to work for queries with uncovered columns.
---
.../apache/phoenix/end2end/index/LocalIndexIT.java | 122 +++++++++++++++++++++
.../org/apache/phoenix/compile/WhereCompiler.java | 11 --
.../apache/phoenix/optimize/QueryOptimizer.java | 11 +-
3 files changed, 129 insertions(+), 15 deletions(-)
diff --git
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
index f006097..e1d8eb2 100644
---
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
+++
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
@@ -103,6 +103,128 @@ public class LocalIndexIT extends BaseLocalIndexIT {
}
@Test
+ public void testUseUncoveredLocalIndex() throws Exception {
+ String tableName = schemaName + "." + generateUniqueName();
+ String indexName = "IDX_" + generateUniqueName();
+ TableName physicalTableName =
SchemaUtil.getPhysicalTableName(tableName.getBytes(), isNamespaceMapped);
+ String indexPhysicalTableName = physicalTableName.getNameAsString();
+
+ Connection conn = getConnection();
+ conn.setAutoCommit(true);
+ if (isNamespaceMapped) {
+ conn.createStatement().execute("CREATE SCHEMA IF NOT EXISTS " +
schemaName);
+ }
+
+ conn.createStatement().execute("CREATE TABLE " + tableName
+ + " (pk INTEGER PRIMARY KEY, v1 FLOAT, v2 FLOAT, V3 INTEGER,
V4 INTEGER)");
+ conn.createStatement().execute("CREATE LOCAL INDEX " + indexName + "
ON " + tableName + "(v2, v3, v4)");
+
+ // 1. COUNT(*) should still use the index - fewer bytes to scan
+ ResultSet rs = conn.createStatement().executeQuery("EXPLAIN SELECT
COUNT(*) FROM " + tableName);
+ assertEquals(
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER "
+ + indexPhysicalTableName + " [1]\n"
+ + " SERVER FILTER BY FIRST KEY ONLY\n"
+ + " SERVER AGGREGATE INTO SINGLE ROW",
+ QueryUtil.getExplainPlan(rs));
+ rs.close();
+
+ // 2. All column projected, no filtering by indexed column, not using
the index
+ rs = conn.createStatement().executeQuery("EXPLAIN SELECT * FROM " +
tableName);
+ assertEquals(
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + physicalTableName,
+ QueryUtil.getExplainPlan(rs));
+ rs.close();
+
+ // 3. if the index can avoid a sort operation, use it
+ rs = conn.createStatement().executeQuery("EXPLAIN SELECT * FROM " +
tableName + " ORDER BY v2");
+ assertEquals(
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER "
+ + indexPhysicalTableName + " [1]\n"
+ + " SERVER FILTER BY FIRST KEY ONLY\n"
+ + "CLIENT MERGE SORT",
+ QueryUtil.getExplainPlan(rs));
+ rs.close();
+
+ // 4. but can't use the index if not ORDERing by a prefix of the index
key.
+ rs = conn.createStatement().executeQuery("EXPLAIN SELECT * FROM " +
tableName + " ORDER BY v3");
+ assertEquals(
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER "
+ + physicalTableName + "\n"
+ + " SERVER SORTED BY [V3]\n"
+ + "CLIENT MERGE SORT",
+ QueryUtil.getExplainPlan(rs));
+ rs.close();
+
+ // 5. If we pin the prefix of the index key we use the index avoiding
sorting on the postfix
+ rs = conn.createStatement().executeQuery("EXPLAIN SELECT * FROM " +
tableName + " WHERE v2 = 2 ORDER BY v3");
+ assertEquals(
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER "
+ + indexPhysicalTableName + " [1,2]\n"
+ + " SERVER FILTER BY FIRST KEY ONLY\n"
+ + "CLIENT MERGE SORT",
+ QueryUtil.getExplainPlan(rs));
+ rs.close();
+
+ // 6. Filtering by a non-indexed column will not use the index
+ rs = conn.createStatement().executeQuery("EXPLAIN SELECT * FROM " +
tableName + " WHERE v1 = 3");
+ assertEquals(
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER "
+ + physicalTableName + "\n"
+ + " SERVER FILTER BY V1 = 3.0",
+ QueryUtil.getExplainPlan(rs));
+ rs.close();
+
+ // 7. Also don't use an index if not filtering on a prefix of the key
+ rs = conn.createStatement().executeQuery("EXPLAIN SELECT * FROM " +
tableName + " WHERE v3 = 1");
+ assertEquals(
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER "
+ + physicalTableName + "\n"
+ + " SERVER FILTER BY V3 = 1",
+ QueryUtil.getExplainPlan(rs));
+ rs.close();
+
+ // 8. Filtering along a prefix of the index key can use the index
+ rs = conn.createStatement().executeQuery("EXPLAIN SELECT * FROM " +
tableName + " WHERE v2 = 2");
+ assertEquals(
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER "
+ + indexPhysicalTableName + " [1,2]\n"
+ + " SERVER FILTER BY FIRST KEY ONLY\n"
+ + "CLIENT MERGE SORT",
+ QueryUtil.getExplainPlan(rs));
+ rs.close();
+
+ // 9. Make sure a gap in the index columns still uses the index as
long as a prefix is specified
+ rs = conn.createStatement().executeQuery("EXPLAIN SELECT * FROM " +
tableName + " WHERE v2 = 2 AND v4 = 4");
+ assertEquals(
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER "
+ + indexPhysicalTableName + " [1,2]\n"
+ + " SERVER FILTER BY FIRST KEY ONLY AND \"V4\"
= 4\n"
+ + "CLIENT MERGE SORT",
+ QueryUtil.getExplainPlan(rs));
+ rs.close();
+
+ // 10. Use index even when also filtering on non-indexed column
+ rs = conn.createStatement().executeQuery("EXPLAIN SELECT * FROM " +
tableName + " WHERE v2 = 2 AND v1 = 3");
+ assertEquals(
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER "
+ + indexPhysicalTableName + " [1,2]\n"
+ + " SERVER FILTER BY FIRST KEY ONLY AND \"V1\"
= 3.0\n"
+ + "CLIENT MERGE SORT",
+ QueryUtil.getExplainPlan(rs));
+ rs.close();
+
+ // 11. Another case of not using a prefix of the index key
+ rs = conn.createStatement().executeQuery("EXPLAIN SELECT * FROM " +
tableName + " WHERE v1 = 3 AND v3 = 1 AND v4 = 1");
+ assertEquals(
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER "
+ + physicalTableName + "\n"
+ + " SERVER FILTER BY (V1 = 3.0 AND V3 = 1 AND
V4 = 1)",
+ QueryUtil.getExplainPlan(rs));
+ rs.close();
+ }
+
+ @Test
public void testLocalIndexRoundTrip() throws Exception {
String tableName = schemaName + "." + generateUniqueName();
String indexName = "IDX_" + generateUniqueName();
diff --git
a/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereCompiler.java
b/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereCompiler.java
index 832b1f0..2c7926e 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereCompiler.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereCompiler.java
@@ -190,17 +190,6 @@ public class WhereCompiler {
protected ColumnRef resolveColumn(ColumnParseNode node) throws
SQLException {
ColumnRef ref = super.resolveColumn(node);
PTable table = ref.getTable();
- // if current table in the context is local index and table in
column reference is global means
- // the column is not present in the local index. If where
condition contains the column
- // not present in the index then we need to go through main table
for each row in index and get the
- // missing column which is like full scan of index table and data
table. Which is
- // inefficient. Then we can skip this plan.
- if (context.getCurrentTable().getTable().getIndexType() ==
IndexType.LOCAL
- && (table.getIndexType() == null || table.getIndexType()
== IndexType.GLOBAL)) {
- String schemaNameStr =
table.getSchemaName()==null?null:table.getSchemaName().getString();
- String tableNameStr =
table.getTableName()==null?null:table.getTableName().getString();
- throw new ColumnNotFoundException(schemaNameStr, tableNameStr,
null, ref.getColumn().getName().getString());
- }
// Track if we need to compare KeyValue during filter evaluation
// using column family. If the column qualifier is enough, we
// just use that.
diff --git
a/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
b/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
index 7d3e7d0..66d7bd7 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
@@ -59,6 +59,7 @@ import org.apache.phoenix.parse.SelectStatement;
import org.apache.phoenix.parse.TableName;
import org.apache.phoenix.parse.TableNode;
import org.apache.phoenix.parse.TableNodeVisitor;
+import org.apache.phoenix.query.KeyRange;
import org.apache.phoenix.query.QueryServices;
import org.apache.phoenix.query.QueryServicesOptions;
import org.apache.phoenix.schema.ColumnNotFoundException;
@@ -327,13 +328,15 @@ public class QueryOptimizer {
boolean optimizedOrderBy =
plan.getOrderBy().getOrderByExpressions().isEmpty() &&
!dataPlan.getOrderBy().getOrderByExpressions().isEmpty();
- // If query doesn't have where clause and some of columns to
project are missing
- // in the index then we need to get missing columns from main
table for each row in
- // local index. It's like full scan of both local index and
data table which is inefficient.
+ // If query doesn't have where clause, or the planner didn't
add any (bound) scan ranges, and some of
+ // columns to project/filter are missing in the index then we
need to get missing columns from main table
+ // for each row in local index. It's like full scan of both
local index and data table which is inefficient.
// Then we don't use the index. If all the columns to project
are present in the index
// then we can use the index even the query doesn't have where
clause.
// We'll use the index anyway if it allowed us to optimize an
ORDER BY clause away.
- if (index.getIndexType() == IndexType.LOCAL &&
indexSelect.getWhere() == null
+ if (index.getIndexType() == IndexType.LOCAL
+ && (indexSelect.getWhere() == null
+ ||
plan.getContext().getScanRanges().getBoundRanges().size() == 1)
&& !plan.getContext().getDataColumns().isEmpty() &&
!optimizedOrderBy) {
return null;
}
