You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Batyrshin Alexander <0x...@gmail.com> on 2018/12/23 13:38:37 UTC
Phoenix perform full scan and ignore covered global index
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)
Re: Phoenix perform full scan and ignore covered global index
Posted by Vincent Poon <vi...@apache.org>.
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 <cl...@gmail.com> wrote:
> Could you please show your SQL of the CREATE TABLE/INDEX
>
> ----------------------------------------
> Jaanai Zhang
> Best regards!
>
>
>
>
> Batyrshin Alexander <0x...@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)
>>
>>
>>
Re: Phoenix perform full scan and ignore covered global index
Posted by Jaanai Zhang <cl...@gmail.com>.
Could you please show your SQL of the CREATE TABLE/INDEX
----------------------------------------
Jaanai Zhang
Best regards!
Batyrshin Alexander <0x...@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)
>
>
>