phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Batyrshin Alexander <0x62...@gmail.com>
Subject Phoenix perform full scan and ignore covered global index
Date Sun, 23 Dec 2018 13:38:37 GMT
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