Re: Phoenix perform full scan and ignore covered global index

2018-12-26 Thread Vincent Poon 
I'm not able to repro this on latest 4.15.0 :
0: jdbc:phoenix:> explain select * from US_POPULATION where city = 'test';
+-+-++--+
|  PLAN
   | EST_BYTES_READ  | EST_ROWS_READ  | EST_INFO_TS  |
+-+-++--+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER
US_POPULATION_COVERED_INDEX ['test']  | null| null   |
null |
+-+-++--+


On Sun, Dec 23, 2018 at 7:56 PM Jaanai Zhang  wrote:

> Could you please show your SQL of the CREATE TABLE/INDEX
>
> 
>Jaanai Zhang
>Best regards!
>
>
>
>
> Batyrshin Alexander <0x62...@gmail.com> 于2018年12月23日周日 下午9:38写道：
>
>> Examples:
>>
>> 1. Ignoring indexes if "*" used for select even index include all columns
>> from source table
>>
>> 0: jdbc:phoenix:127.0.0.1> explain select * from table where "p" =
>> '123123123';
>>
>> +---+-+++
>> | PLAN
>>   | EST_BYTES_READ  | EST_ROWS_READ  |
>> EST_INFO_TS   |
>>
>> +---+-+++
>> | CLIENT 1608-CHUNK 237983037 ROWS 160746749821 BYTES PARALLEL 30-WAY
>> FULL SCAN OVER table  | 160746749821| 237983037  | 1545484493647  |
>> | SERVER FILTER BY d."p" = '123123123'
>>   | 160746749821| 237983037  | 1545484493647  |
>> | CLIENT MERGE SORT
>>| 160746749821| 237983037  |
>> 1545484493647  |
>>
>> +---+-+++
>> 3 rows selected (0.05 seconds)
>>
>>
>> 2. Indexes used if only 1 column selected
>>
>> 0: jdbc:phoenix:127.0.0.1> explain select "c" from table where "p" =
>> '123123123';
>>
>> +-+-+++
>> |
>> PLAN
>>  | EST_BYTES_READ  | EST_ROWS_READ  |  EST_INFO_TS   |
>>
>> +-+-+++
>> | CLIENT 30-CHUNK 3569628 ROWS 3145729398 BYTES PARALLEL 30-WAY RANGE
>> SCAN OVER table_idx_p [0,'123123123'] - [29,'123123123']  | 3145729398
>> | 3569628| 1545484508039  |
>> | SERVER FILTER BY FIRST KEY ONLY
>>
>>  | 3145729398  | 3569628| 1545484508039  |
>> | CLIENT MERGE SORT
>>
>>  | 3145729398  | 3569628| 1545484508039  |
>>
>> +-+-+++
>> 3 rows selected (0.038 seconds)
>>
>>
>> 3.
>>
>> 0: jdbc:phoenix:127.0.0.1> explain select /*+ INDEX(table table_idx_p) */
>> * from table where "p" = '123123123';
>>
>> +-+-+++
>> |
>> PLAN
>>  | EST_BYTES_READ  | EST_ROWS_READ  |  EST_INFO_TS
>>  |
>>
>> +-+-+++
>> | CLIENT 1608-CHUNK 237983037 ROWS 160746749821 BYTES PARALLEL 30-WAY
>> FULL SCAN OVER table
>> | 3145729398  | 3569628| 1545484508039  |
>> | CLIENT MERGE SORT
>>
>>  | 3145729398  | 3569628|
>> 1545484508039  |
>> | SKIP-SCAN-JOIN TABLE 0
>>
>> | 3145729398  | 3569628| 1545484508039
>> |
>> | CLIENT 30-CHUNK 3569628 ROWS 3145729398 BYTES PARALLEL 30-WAY
>> RANGE SCAN OVER table_idx_p [0,'123123123'] - [29,'123123123']  |
>> 3145729398

Re: Phoenix perform full scan and ignore covered global index

2018-12-23 Thread Jaanai Zhang 
Could you please show your SQL of the CREATE TABLE/INDEX


   Jaanai Zhang
   Best regards!




Batyrshin Alexander <0x62...@gmail.com> 于2018年12月23日周日 下午9:38写道：

> Examples:
>
> 1. Ignoring indexes if "*" used for select even index include all columns
> from source table
>
> 0: jdbc:phoenix:127.0.0.1> explain select * from table where "p" =
> '123123123';
>
> +---+-+++
> | PLAN
>   | EST_BYTES_READ  | EST_ROWS_READ  |
> EST_INFO_TS   |
>
> +---+-+++
> | CLIENT 1608-CHUNK 237983037 ROWS 160746749821 BYTES PARALLEL 30-WAY FULL
> SCAN OVER table  | 160746749821| 237983037  | 1545484493647  |
> | SERVER FILTER BY d."p" = '123123123'
>   | 160746749821| 237983037  | 1545484493647  |
> | CLIENT MERGE SORT
>  | 160746749821| 237983037  |
> 1545484493647  |
>
> +---+-+++
> 3 rows selected (0.05 seconds)
>
>
> 2. Indexes used if only 1 column selected
>
> 0: jdbc:phoenix:127.0.0.1> explain select "c" from table where "p" =
> '123123123';
>
> +-+-+++
> |
>   PLAN
>| EST_BYTES_READ  | EST_ROWS_READ  |  EST_INFO_TS   |
>
> +-+-+++
> | CLIENT 30-CHUNK 3569628 ROWS 3145729398 BYTES PARALLEL 30-WAY RANGE SCAN
> OVER table_idx_p [0,'123123123'] - [29,'123123123']  | 3145729398  |
> 3569628| 1545484508039  |
> | SERVER FILTER BY FIRST KEY ONLY
>
>| 3145729398  | 3569628| 1545484508039  |
> | CLIENT MERGE SORT
>
>| 3145729398  | 3569628| 1545484508039  |
>
> +-+-+++
> 3 rows selected (0.038 seconds)
>
>
> 3.
>
> 0: jdbc:phoenix:127.0.0.1> explain select /*+ INDEX(table table_idx_p) */
> * from table where "p" = '123123123';
>
> +-+-+++
> |
>   PLAN
>| EST_BYTES_READ  | EST_ROWS_READ  |  EST_INFO_TS   |
>
> +-+-+++
> | CLIENT 1608-CHUNK 237983037 ROWS 160746749821 BYTES PARALLEL 30-WAY FULL
> SCAN OVER table
> | 3145729398  | 3569628| 1545484508039  |
> | CLIENT MERGE SORT
>
>| 3145729398  | 3569628| 1545484508039  |
> | SKIP-SCAN-JOIN TABLE 0
>
> | 3145729398  | 3569628| 1545484508039
> |
> | CLIENT 30-CHUNK 3569628 ROWS 3145729398 BYTES PARALLEL 30-WAY
> RANGE SCAN OVER table_idx_p [0,'123123123'] - [29,'123123123']  |
> 3145729398  | 3569628| 1545484508039  |
> | SERVER FILTER BY FIRST KEY ONLY
>
>| 3145729398  | 3569628| 1545484508039  |
> | CLIENT MERGE SORT
>
>| 3145729398  | 3569628| 1545484508039  |
> | DYNAMIC SERVER FILTER BY "table.c" IN ($35.$37)
>
>| 3145729398  | 3569628| 1545484508039  |
>
> +-+-+++
> 7 rows selected (0.12 seconds)
>
>
>

Phoenix perform full scan and ignore covered global index

2018-12-23 Thread Batyrshin Alexander 
Examples:

1. Ignoring indexes if "*" used for select even index include all columns from 
source table

0: jdbc:phoenix:127.0.0.1> explain select * from table where "p" = '123123123';
+---+-+++
| PLAN  
| EST_BYTES_READ  | EST_ROWS_READ  |  EST_INFO_TS   |
+---+-+++
| CLIENT 1608-CHUNK 237983037 ROWS 160746749821 BYTES PARALLEL 30-WAY FULL SCAN 
OVER table  | 160746749821| 237983037  | 1545484493647  |
| SERVER FILTER BY d."p" = '123123123'  
| 160746749821| 237983037  | 1545484493647  |
| CLIENT MERGE SORT 
| 160746749821| 237983037  | 1545484493647  |
+---+-+++
3 rows selected (0.05 seconds)


2. Indexes used if only 1 column selected

0: jdbc:phoenix:127.0.0.1> explain select "c" from table where "p" = 
'123123123';
+-+-+++
|   
 PLAN   
  | EST_BYTES_READ  | EST_ROWS_READ  |  EST_INFO_TS   |
+-+-+++
| CLIENT 30-CHUNK 3569628 ROWS 3145729398 BYTES PARALLEL 30-WAY RANGE SCAN OVER 
table_idx_p [0,'123123123'] - [29,'123123123']  | 3145729398  | 3569628 
   | 1545484508039  |
| SERVER FILTER BY FIRST KEY ONLY   

  | 3145729398  | 3569628| 1545484508039  |
| CLIENT MERGE SORT 

  | 3145729398  | 3569628| 1545484508039  |
+-+-+++
3 rows selected (0.038 seconds)


3.

0: jdbc:phoenix:127.0.0.1> explain select /*+ INDEX(table table_idx_p) */ * 
from table where "p" = '123123123';
+-+-+++
|   
 PLAN   
  | EST_BYTES_READ  | EST_ROWS_READ  |  EST_INFO_TS   |
+-+-+++
| CLIENT 1608-CHUNK 237983037 ROWS 160746749821 BYTES PARALLEL 30-WAY FULL SCAN 
OVER table  
  | 3145729398  | 3569628| 1545484508039  |
| CLIENT MERGE SORT 

  | 3145729398  | 3569628| 1545484508039  |
| SKIP-SCAN-JOIN TABLE 0

  | 3145729398  | 3569628| 1545484508039  |
| CLIENT 30-CHUNK 3569628 ROWS 3145729398 BYTES PARALLEL 30-WAY RANGE 
SCAN OVER table_idx_p [0,'123123123'] - [29,'123123123']  | 3145729398  | 
3569628| 1545484508039  |
| SERVER FILTER BY FIRST KEY ONLY   

  | 3145729398  | 3569628| 1545484508039  |
| CLIENT MERGE SORT