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 <[email protected]> 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 >
