[PHOENIX-6959] Server merges are not used for hinted uncovered indexes for wildcard selects on 5.1

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 5.1.3
• Fix Version/s: 5.1.4
• Component/s: phoenix
• Assignee: Istvan Toth
• Reporter: Istvan Toth
• Created: 18/May/23 06:36
• Updated: 21/Jun/23 14:48
• Resolved: 21/Jun/23 10:53

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.

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)

 

Issue Links

• depends upon: PHOENIX-6984 Fix fallback to skip-join-merge for hinted global indexes
• is related to: PHOENIX-6983 Add hint to disable server merges for uncovered index queries
• links to: Combined PR with dependencies
• links to: GitHub Pull Request #1623