You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@phoenix.apache.org by "Viraj Jasani (Jira)" <ji...@apache.org> on 2023/05/19 03:05:00 UTC
[jira] [Commented] (PHOENIX-6959) Server merges are not used for hinted uncovered indexes for wildcard selects on 5.1
[ https://issues.apache.org/jira/browse/PHOENIX-6959?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17724085#comment-17724085 ]
Viraj Jasani commented on PHOENIX-6959:
---------------------------------------
I was also doing similar testing yesterday and I also did not see full scan as per the explain plan. This remained true even when uncovered was not hinted.
For instance,
{code:java}
CREATE TABLE T1 (ID VARCHAR(15) NOT NULL PRIMARY KEY, COL1 VARCHAR, COL2 VARCHAR, COL3 VARCHAR);
CREATE UNCOVERED INDEX IDX_T1 ON T1 (PHOENIX_ROW_TIMESTAMP());
UPSERT INTO T1 VALUES ('a', 'a1', 'a2', 'a3');
UPSERT INTO T1 VALUES ('b', 'b1', 'b2', 'b3');
UPSERT INTO T1 VALUES ('c', 'c1', 'c2', 'c3');
EXPLAIN SELECT * FROM T1 WHERE PHOENIX_ROW_TIMESTAMP() > TO_DATE('2023-05-16 00:00:00', 'yyyy-MM-dd HH:mm:ss') AND PHOENIX_ROW_TIMESTAMP() < TO_DATE('2023-05-19', 'yyyy-MM-dd'); {code}
Explain plan output:
{code:java}
CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER IDX_T1 ['2023-05-16 00:00:00.001'] - ['2023-05-19 00:00:00.000']
SERVER MERGE [0.COL1, 0.COL2, 0.COL3]
SERVER FILTER BY FIRST KEY ONLY{code}
> Server merges are not used for hinted uncovered indexes for wildcard selects on 5.1
> -----------------------------------------------------------------------------------
>
> Key: PHOENIX-6959
> URL: https://issues.apache.org/jira/browse/PHOENIX-6959
> Project: Phoenix
> Issue Type: Bug
> Components: phoenix
> Affects Versions: 5.1.3
> Reporter: Istvan Toth
> Assignee: Istvan Toth
> Priority: Major
>
> When selecting all columns by specifying '*' phoenix uses a skip-scan-join for the query.
> When specifying each column individually, Phoenix uses the more efficient server merge plan.
> This is already fixed in 5.2
> Check if it is feasible to backport this optimization to 5.1
> -When selecting all columns by specifying '*', Phoenix performs a full table scan.-
> -Selecting all columns by specifying each one individually, Phoenix uses the uncovered index as intended.-
> {noformat}
> create table data_table (k integer primary key, v1 integer, v2 integer, v3 integer, v4 integer);
> create index uncovered on data_table (v1);
> explain select /*+ INDEX(data_table uncovered) */ k,v1,v2,v3,v4 from data_table where v1=1;
> +-------------------------------------------------------------------------+----------------+---------------+-------------+
> | PLAN | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |
> +-------------------------------------------------------------------------+----------------+---------------+-------------+
> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER UNCOVERED [1] | null | null | null |
> | SERVER MERGE [0.V2, 0.V3, 0.V4] | null | null | null |
> | SERVER FILTER BY FIRST KEY ONLY | null | null | null |
> +-------------------------------------------------------------------------+----------------+---------------+-------------+
> 3 rows selected (0.011 seconds)
> 0: jdbc:phoenix:localhost:49653> explain select /*+ INDEX(data_table uncovered) */ * from data_table where v1=1;
> +---------------------------------------------------------------------------------+----------------+---------------+-------------+
> | PLAN | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |
> +---------------------------------------------------------------------------------+----------------+---------------+-------------+
> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER DATA_TABLE | null | null | null |
> | SKIP-SCAN-JOIN TABLE 0 | null | null | null |
> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER UNCOVERED [1] | null | null | null |
> | SERVER FILTER BY FIRST KEY ONLY | null | null | null |
> | DYNAMIC SERVER FILTER BY "DATA_TABLE.K" IN ($183.$185) | null | null | null |
> +---------------------------------------------------------------------------------+----------------+---------------+-------------+
> 5 rows selected (0.012 seconds)
> {noformat}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)