phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Taylor <jamestay...@apache.org>
Subject Re: Full Scan - is it really?
Date Fri, 28 Jul 2017 05:09:21 GMT
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
>

Mime
View raw message