phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Vincent Poon <vincentp...@apache.org>
Subject Re: Phoenix perform full scan and ignore covered global index
Date Wed, 26 Dec 2018 19:19:40 GMT
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 <cloud.poster@gmail.com> wrote:

> Could you please show your SQL of the CREATE TABLE/INDEX
>
> ----------------------------------------
>    Jaanai Zhang
>    Best regards!
>
>
>
>
> Batyrshin Alexander <0x62ash@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)
>>
>>
>>

Mime
View raw message