You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "Mujtaba Chohan (JIRA)" <ji...@apache.org> on 2014/11/12 21:17:33 UTC

[jira] [Commented] (PHOENIX-1439) LIKE doesn't appear to use skip scanning

    [ https://issues.apache.org/jira/browse/PHOENIX-1439?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14208611#comment-14208611 ] 

Mujtaba Chohan commented on PHOENIX-1439:
-----------------------------------------

Tried this with Phoenix 4.2 branch. Generated data using bin/performance.py script and ran the following query:
{code}
select count(*) from performance_5000000 where host like 'E%' and domain like 'S%';
{code}

Explain plan shows skip scan is used:
{code}
CLIENT 2-CHUNK PARALLEL 1-WAY SKIP SCAN ON 1 RANGE OVER PERFORMANCE_5000000 ['E ','S'] - ['F ','T'] 
     SERVER FILTER BY FIRST KEY ONLY      
     SERVER AGGREGATE INTO SINGLE ROW  
{code}

Also another query that runs part of perf test

{code}
select count(*) from IN_LIKE_TABLE where (K1 in ('A','E','F','G','H','I','Z') or K1 like 'E%') and (K2 like '%xx1%' or K2 like '%xxxxxx2%')
{code}

Explain plan:
{code}
CLIENT 6-CHUNK PARALLEL 1-WAY SKIP SCAN ON 6 RANGES OVER IN_LIKE_TABLE [A] - [Z]
    SERVER FILTER BY FIRST KEY ONLY AND (K2 LIKE %xx1% OR K2 LIKE %xxxxxx2%)
    SERVER AGGREGATE INTO SINGLE ROW
{code}

[~jamestaylor]

> LIKE doesn't appear to use skip scanning
> ----------------------------------------
>
>                 Key: PHOENIX-1439
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-1439
>             Project: Phoenix
>          Issue Type: Improvement
>            Reporter: Brian Johnson
>
> We had a query using several like statements of the form LIKE 'prefix|%' and LIKE 'prefix2|%' ... and the performance was on par with a full table scan. When we replaced it with BETWEEN 'prefix|' and 'prefix|FFFFFFF...' the query time was substantially faster. It seems that the LIKE query should work with skip scan, but I don't think it's being used.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)