phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Xinyi Yan (Jira)" <j...@apache.org>
Subject [jira] [Created] (PHOENIX-5752) Phoenix RVC InListExpression generates wrong DESC order column scan
Date Fri, 28 Feb 2020 02:55:00 GMT
Xinyi Yan created PHOENIX-5752:
----------------------------------

             Summary: Phoenix RVC InListExpression generates wrong DESC order column scan
                 Key: PHOENIX-5752
                 URL: https://issues.apache.org/jira/browse/PHOENIX-5752
             Project: Phoenix
          Issue Type: Improvement
            Reporter: Xinyi Yan
            Assignee: Xinyi Yan


{code:java}
@Test
public void testInListExpressionWithDescOrderWithRightQueryPlan() throws Exception {
    String fullTableName = generateUniqueName();
    String fullViewName = generateUniqueName();
    String tenantView = generateUniqueName();
    String TENANT_SPECIFIC_URL1 = getUrl() + ';' + TENANT_ID_ATTRIB + "=tenant1";

    try (Connection conn = DriverManager.getConnection(getUrl())) {
        conn.setAutoCommit(true);
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("CREATE TABLE " + fullTableName + "(\n" + " TENANT_ID CHAR(15) NOT
NULL,\n" + " KEY_PREFIX CHAR(3) NOT NULL,\n" +
                    " CONSTRAINT PK PRIMARY KEY (\n" + " TENANT_ID," + " KEY_PREFIX" + "))
MULTI_TENANT=TRUE");
            stmt.execute("CREATE VIEW " + fullViewName + "(\n" + " ID1 VARCHAR NOT NULL,\n"
+ " ID2 VARCHAR NOT NULL,\n" + " ID3 BIGINT, ID4 BIGINT \n" +
                    " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (\n" + " ID1, ID2 DESC\n" + "))
" +
                    "AS SELECT * FROM " + fullTableName + " WHERE KEY_PREFIX = '0CY'");
        }
    }

    try (Connection viewConn = DriverManager.getConnection(TENANT_SPECIFIC_URL1)) {
        viewConn.setAutoCommit(true);
        try (Statement stmt = viewConn.createStatement()) {
            stmt.execute("CREATE VIEW IF NOT EXISTS " + tenantView + " AS SELECT * FROM "
+ fullViewName);

            try (PreparedStatement preparedStmt = viewConn.prepareStatement(
                    "SELECT * FROM " + tenantView + " WHERE (ID1, ID2) " +
                            "IN (('me', '000000000000500')," +
                            "('bar', '000000000000400')," +
                            "('foo', '000000000000300'))")) {
                QueryPlan queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
                assertTrue(queryPlan.getExplainPlan().toString().contains("POINT LOOKUP ON
"));
            }
        }
    }
}
{code}
InListExpression generates a range scan instead of a point lookup. This might result in an
extremely bad performance for the DELETE and SELECT query. >From my perspective, it might
be the point that we need to refactor and/or rewrite query optimization code so that we don't
need to have two code paths for InListExpresson. We can simply do the query rewrite to ((ID1=?
AND ID2=?) or (ID1=? AND  ID2=?)) as the right approach. 



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

Mime
View raw message