[
https://issues.apache.org/jira/browse/PHOENIX-6752?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17611831#comment-17611831
]
ASF GitHub Bot commented on PHOENIX-6752:
-----------------------------------------
comnetwork commented on code in PR #1508:
URL: https://github.com/apache/phoenix/pull/1508#discussion_r985057617
##########
phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryIT.java:
##########
@@ -169,4 +174,41 @@ public void testDistinctLimitScan() throws Exception {
}
}
+ @Test
+ public void testSelectWithLargeORs() throws Exception {
+ String viewName = generateUniqueName();
+ Properties tenantProps = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ tenantProps.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, tenantId);
+ Random rnd = new Random();
+ int numORs = 50000;
+ int numCols = 100;
+
+ try (Connection tenantConnection =
DriverManager.getConnection(getUrl(), tenantProps)) {
+ String createViewSQL = String.format("CREATE VIEW IF NOT EXISTS
%s(ID1 TIMESTAMP NOT NULL, ID2 VARCHAR(30) NOT NULL, ID3 INTEGER NOT NULL,
COL_V0 VARCHAR ", viewName);
+ for (int i = 1; i < numCols;i++) {
+ createViewSQL += String.format(", COL_V%06d VARCHAR ", i);
+ }
+ createViewSQL += " CONSTRAINT pk PRIMARY KEY (ID1 DESC, ID2 DESC,
ID3 DESC )) AS SELECT * FROM " + tableName + " WHERE entity_id = 'ECZ' ";
+ tenantConnection.createStatement().execute(createViewSQL);
+
+ StringBuilder whereClause = new StringBuilder("ID1 = ? AND ID2 > ?
AND (ID3 = ? ");
+ for (int i = 0; i < numORs;i++) {
+ whereClause.append(" OR ID3 = ?");
+ }
+ whereClause.append(") LIMIT 200");
+ String query = String.format("select * from %s where ", viewName)
+ whereClause;
+
+ PhoenixPreparedStatement stmtForTimingCheck =
tenantConnection.prepareStatement(query.toString()).unwrap(PhoenixPreparedStatement.class);
+ stmtForTimingCheck.setTimestamp(1, new
Timestamp(System.currentTimeMillis() + rnd.nextInt(50000)));
+ stmtForTimingCheck.setString(2,
RandomStringUtils.randomAlphanumeric(30));
+
+ for (int i = 0; i<numORs+1; i++) {
+ stmtForTimingCheck.setInt(i + 3, rnd.nextInt() );
+ }
+
+ long startResultSetTime = System.currentTimeMillis();
+ ResultSet rs = stmtForTimingCheck.executeQuery(query.toString());
+ assertTrue("Query execution time exceeded limit exceeded 10s",
(System.currentTimeMillis() - startResultSetTime) < 10000);
Review Comment:
I suggest we would better not assert the query time in test, which may be
cause the test flaky. In my opinion, for test, we could assert the internal
state which your PR modified to cause the query not timeout , and is same as
above, I think it is would be a UT, not a IT test.
> Duplicate expression nodes in extract nodes during WHERE compilation phase
> leads to poor performance.
> -----------------------------------------------------------------------------------------------------
>
> Key: PHOENIX-6752
> URL: https://issues.apache.org/jira/browse/PHOENIX-6752
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.15.0, 5.1.0, 4.16.1, 5.2.0
> Reporter: Jacob Isaac
> Assignee: Jacob Isaac
> Priority: Critical
> Fix For: 5.2.0
>
> Attachments: test-case.txt
>
>
> SQL queries using the OR operator were taking a long time during the WHERE
> clause compilation phase when a large number of OR clauses (~50k) are used.
> The key observation was that during the AND/OR processing, when there are a
> large number of OR expression nodes the same set of extracted nodes was
> getting added.
> Thus bloating the set size and slowing down the processing.
> [code|https://github.com/apache/phoenix/blob/0c2008ddf32566c525df26cb94d60be32acc10da/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java#L930]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
