phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "rajeshbabu@apache.org" <chrajeshbab...@gmail.com>
Subject Re: Join of multi index tables.
Date Wed, 20 Jul 2016 09:12:56 GMT
Hi William,

Currently we are not supporting index merge optimizations.

Here is the issue I have raised for the same thing. Would be better to
share details there to show the interest.
https://issues.apache.org/jira/browse/PHOENIX-1801

Can't you combine the columns in both indexes into a single index to avoid
index merge and make the query run faster?

Thanks,
Rajeshbabu.


On Wed, Jul 20, 2016 at 2:33 PM, William <yhxx511@163.com> wrote:

> Hi all,
>  I have a question about global  secondary index in Phoenix 4.6.  See the
> following statements:
>
> create table yy (pk integer primary key, cf.a integer, cf.b integer);
> create index yya on yy(cf.a);
> create index yyb on yy(cf.b);
>
> then upsert some data into table yy; do the following query:
>
> select pk from yy where cf.a = 1 or cf.b = 2;
> select /*+INDEX(yy yya yyb)*/ pk from yy where cf.a = 1 or cf.b = 2;
>
> I expect that both index tables will be used in this query, and join the
> results from both index tables and return.
> But unfortunately, no index tables has been used but a full table scan
> instead. The statement with a hint have the same behaviour. The explain
> plan is :
>
> FULL SCAN OVER YY
>     SERVER FILTER BY (CF.A = 1 OR CF.B = 2)
>
> Another example:
>
> create index yyi on yy (cf.a) include (cf.b);
> select pk from yy where cf.a = 1 or cf.b =2;
>
> then this query will hit the index table with a filter. The explain plan:
>
> FULL SCAN OVER YYI
>      SERVER FILTER BY (TO_INTEGER("A") = 1 OR CF."B" = 2)
>
> my question is : I only have index yya and yyb, and i want the above
> select statement to hit both index tables,  do we support this scenario?
> if so, how can i use both indexes?
> if not, why? It is too hard to implement ? Is there a plan to support it ?
>
> Thanks
> William
>
>
>
>

Mime
View raw message