phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jaanai Zhang <cloud.pos...@gmail.com>
Subject Re: Phoenix perform full scan and ignore covered global index
Date Mon, 24 Dec 2018 03:55:25 GMT
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