[
https://issues.apache.org/jira/browse/PHOENIX-6959?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Istvan Toth updated PHOENIX-6959:
---------------------------------
Description:
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}
was:
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}
> Server merges are not used for hinted uncovered indexes for wildcard selects
> ----------------------------------------------------------------------------
>
> 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)
