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 Fri, 28 Jul 2017 00:35:12 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;
 
explain
select clicks.*
from
   clicks, products
where
   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)                      
                                 |
+--------------------------------------------------------------------------------------------------------------------------+
 
explain
select clicks.*
from
   clicks
where
   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)                                 
                     |
+--------------------------------------------------------------------------------------------------------------+



____________________________________________________________
Affordable Wireless Plans
Set up is easy. Get online in minutes.
Starting at only $14.95 per month! 
www.netzero.net?refcd=nzmem0216

Mime
View raw message