Return-Path: X-Original-To: apmail-phoenix-commits-archive@minotaur.apache.org Delivered-To: apmail-phoenix-commits-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 05796C248 for ; Sat, 22 Nov 2014 05:10:45 +0000 (UTC) Received: (qmail 45183 invoked by uid 500); 22 Nov 2014 05:10:44 -0000 Delivered-To: apmail-phoenix-commits-archive@phoenix.apache.org Received: (qmail 45095 invoked by uid 500); 22 Nov 2014 05:10:44 -0000 Mailing-List: contact commits-help@phoenix.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@phoenix.apache.org Delivered-To: mailing list commits@phoenix.apache.org Received: (qmail 45081 invoked by uid 99); 22 Nov 2014 05:10:44 -0000 Received: from tyr.zones.apache.org (HELO tyr.zones.apache.org) (140.211.11.114) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 22 Nov 2014 05:10:44 +0000 Received: by tyr.zones.apache.org (Postfix, from userid 65534) id 34AE5A0B769; Sat, 22 Nov 2014 05:10:44 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: jamestaylor@apache.org To: commits@phoenix.apache.org Date: Sat, 22 Nov 2014 05:10:44 -0000 Message-Id: <4b77e0ba34434169a6751383432f3c18@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: [1/2] phoenix git commit: PHOENIX-1436 the choice of index by phoneix is not correct where do querying Repository: phoenix Updated Branches: refs/heads/4.0 3c1eb3a9f -> b8e7da27f PHOENIX-1436 the choice of index by phoneix is not correct where do querying Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/907e656e Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/907e656e Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/907e656e Branch: refs/heads/4.0 Commit: 907e656efaa9745daf7b65b89bc30cbf804ca2f5 Parents: 3c1eb3a Author: James Taylor Authored: Fri Nov 21 21:08:37 2014 -0800 Committer: James Taylor Committed: Fri Nov 21 21:08:37 2014 -0800 ---------------------------------------------------------------------- .../org/apache/phoenix/optimize/QueryOptimizer.java | 3 --- .../apache/phoenix/compile/QueryOptimizerTest.java | 15 +++++++++++++++ 2 files changed, 15 insertions(+), 3 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/907e656e/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java ---------------------------------------------------------------------- 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 bf6de8a..333b102 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 @@ -331,9 +331,6 @@ public class QueryOptimizer { // indexed columns then give first priority to the local index. if (!bothLocalIndexes && table1.getIndexType() == IndexType.LOCAL && plan1.getContext().getScanRanges().getRanges().isEmpty()) c--; - // if both tables are index tables then select plan below based on number of - // columns and type of index. - if(table1.getType()==PTableType.INDEX && table2.getType()==PTableType.INDEX && !bothLocalIndexes) c=0; } if (c != 0) return c; if (plan1.getGroupBy()!=null && plan2.getGroupBy()!=null) { http://git-wip-us.apache.org/repos/asf/phoenix/blob/907e656e/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryOptimizerTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryOptimizerTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryOptimizerTest.java index c046bc6..c45d75c 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryOptimizerTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryOptimizerTest.java @@ -25,6 +25,7 @@ import java.sql.Connection; import java.sql.Date; import java.sql.DriverManager; import java.sql.PreparedStatement; +import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; @@ -38,6 +39,7 @@ import org.apache.phoenix.query.BaseConnectionlessQueryTest; import org.apache.phoenix.query.QueryConstants; import org.apache.phoenix.schema.PTableType; import org.apache.phoenix.util.PhoenixRuntime; +import org.apache.phoenix.util.QueryUtil; import org.apache.phoenix.util.SchemaUtil; import org.junit.Test; @@ -370,6 +372,19 @@ public class QueryOptimizerTest extends BaseConnectionlessQueryTest { testAssertQueryPlanDetails(false, true, false); } + @Test + public void testQueryOptimizerShouldSelectThePlanWithMoreNumberOfPKColumns() throws Exception { + Connection conn1 = DriverManager.getConnection(getUrl()); + Connection conn2 = DriverManager.getConnection(getUrl()); + conn1.createStatement().execute("create table index_test_table (a varchar not null,b varchar not null,c varchar not null,d varchar,e varchar, f varchar constraint pk primary key(a,b,c))"); + conn1.createStatement().execute( + "create index INDEX_TEST_TABLE_INDEX_D on INDEX_TEST_TABLE(A,D) include(B,C,E,F)"); + conn1.createStatement().execute( + "create index INDEX_TEST_TABLE_INDEX_F on INDEX_TEST_TABLE(A,F) include(B,C,D,E)"); + ResultSet rs = conn2.createStatement().executeQuery("explain select * from INDEX_TEST_TABLE where A in ('1','2','3','4','5') and F in ('1111','2222','3333')"); + assertEquals("CLIENT PARALLEL 1-WAY SKIP SCAN ON 15 KEYS OVER INDEX_TEST_TABLE_INDEX_F ['1','1111'] - ['5','3333']", QueryUtil.getExplainPlan(rs)); + } + private void testAssertQueryPlanDetails(boolean multitenant, boolean useIndex, boolean salted) throws Exception { String sql; PreparedStatement stmt;