phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Lew Jackman" <lew9...@netzero.net>
Subject Re: Full Scan - is it really?
Date Thu, 27 Jul 2017 23:55:23 GMT
Thanks all for the replies. I will illustrate using a simplified schema and query. Please note
I am not using any Phoenix indexes as there are further complexities that are not illustrated
by this simplified example that preclude using that Phoenix capability. I have created two
tables and two sample queries of the join I will paste below.  Perhaps I am missing something,
but I am unclear as to why a full table scan is needed on the clicks table given the key fields
in the query for both tables. Any further insight is very appreciated in advance.  CREATE
TABLE IF NOT EXISTS clicks (  sha_key     VARCHAR(64) NOT NULL PRIMARY KEY,  user_id     VARCHAR(40),
 product_id  VARCHAR(40),  zip_code    VARCHAR(40))COMPRESSION=SNAPPY, DISABLE_WAL=true, IMMUTABLE_ROWS=true;
 CREATE TABLE IF NOT EXISTS products (  product_id  VARCHAR(40) NOT NULL,  sha_key     VARCHAR(64)
NOT NULL,  zip_code    VARCHAR(64) NOT NULL  CONSTRAINT pk PRIMARY KEY (product_id, sha_key,
zip_code) )COMPRESSION=SNAPPY, DISABLE_WAL=true, IMMUTABLE_ROWS=true, SALT_BUCKETS=256; explainselect
clicks.*from   clicks, productswhere   clicks.sha_key = products.sha_key and   products.product_id
in ('w8kfc1','wxdfe8','fmlwl6') and   products.zip_code in ('90210','55511','81811') +--------------------------------------------------------------------------------------------------------------------------+|
                                                          PLAN                           
                               |+--------------------------------------------------------------------------------------------------------------------------+|
CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER CLICKS                          
                               ||     PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)           
                                                                 ||         CLIENT 256-CHUNK
PARALLEL 256-WAY ROUND ROBIN SKIP SCAN ON 768 KEYS OVER PRODUCTS [0,'fmlwl6'] - [255,'wxdfe8']
 ||             SERVER FILTER BY FIRST KEY ONLY AND ZIP_CODE IN ('55511','81811','90210')
                                   ||     DYNAMIC SERVER FILTER BY CLICKS.SHA_KEY IN (PRODUCTS.SHA_KEY)
                                                       |+--------------------------------------------------------------------------------------------------------------------------+
explainselect clicks.*from   clickswhere   clicks.sha_key in (       select products.sha_key
      from         products       where         products.product_id in ('w8kfc1','wxdfe8','fmlwl6')
and         products.zip_code in ('90210','55511','81811')) +--------------------------------------------------------------------------------------------------------------+|
                                                    PLAN                                 
                   |+--------------------------------------------------------------------------------------------------------------+|
CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER CLICKS                          
                   ||     SKIP-SCAN-JOIN TABLE 0                                         
                                         ||         CLIENT 256-CHUNK PARALLEL 256-WAY SKIP
SCAN ON 768 KEYS OVER PRODUCTS [0,'fmlwl6'] - [255,'wxdfe8']  ||             SERVER FILTER
BY FIRST KEY ONLY AND ZIP_CODE IN ('55511','81811','90210')                        ||    
        SERVER AGGREGATE INTO DISTINCT ROWS BY [SHA_KEY]                                 
               ||         CLIENT MERGE SORT                                              
                                     ||     DYNAMIC SERVER FILTER BY CLICKS.SHA_KEY IN ($5.$7)
                                                      |+--------------------------------------------------------------------------------------------------------------+
     
____________________________________________________________
Get Rid Of Unwanted Moles & Skin Tags [At Home]
Smart Life Now
http://thirdpartyoffers.netzero.net/TGL3231/597a7d934ae377d9379b6st02vuc
Mime
View raw message