The query for products is doing a point lookup based on the combinations of product_id, sha_key, zip_code and then (I think) a skip scan is being done for clicks based on the sha_key (based on the DYNAMIC SERVER FILTER). You should be able to confirm this in a debugger through the scans that take place and the invocation of the SkipScanFilter. There's a similar pending question on PHOENIX-3999. Please let us know what you find out.

Also, if you could file a JIRA for the explain plan being confusing, that'd be much appreciated. At a minimum, we should update our documentation in our Tuning Guide to make this more clear. Patches are welcome.

Thanks,
James

On Thu, Jul 27, 2017 at 5:35 PM, Lew Jackman <lew9090@netzero.net> wrote:
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