phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Vamsi Krishna <vamsi.attl...@gmail.com>
Subject Re: phoenix explain plan not showing any difference after adding a local index on the table column that is used in query filter
Date Thu, 30 Jun 2016 04:07:06 GMT
Thanks Vikash.

On Wed, Jun 29, 2016 at 12:26 PM Talanki, Vikash <vtalanki@visa.com> wrote:

> Hi Ankit,
>
> I think this is not completely true.
> Phoenix uses local index table when all columns in where clause and
> atleast one column in select clause are part of local index table.
>
> "Select col1,col2,col3 from table" uses local index but "select * from
> table" do not.
> I'm not certain of the reason
>
>
> Sent with Good Work (www.good.com)
>
> *From: *Ankit Singhal <ankitsinghal59@gmail.com>
> *Date: *Wednesday, Jun 29, 2016, 12:15
> *To: *user@phoenix.apache.org <user@phoenix.apache.org>
> *Subject: *Re: phoenix explain plan not showing any difference after
> adding a local index on the table column that is used in query filter
>
> Hi Vamsi,
>
> *Explain plan:*
> explain select * from vamsi.table_a where col2 = 'abc';
> +-------------------------------------------------------------+
> |                            PLAN                             |
> +-------------------------------------------------------------+
> | CLIENT 5-CHUNK PARALLEL 5-WAY FULL SCAN OVER VAMSI.TABLE_A  |
> |     SERVER FILTER BY COL2 = 'abc'                           |
> +-------------------------------------------------------------+
>
> You are selecting all the columns of the table, which are not part of
> local index (and are also not covered) so instead of scanning index and
> join back to data table to get all the columns is costly operation. so
> optimizer chooses data table to scan instead of using index to serve query.
>
> below query should use local indexes:-
> explain select col2, any_covered_colums from vamsi.table_a where col2 =
> 'abc';
>
> For covered indexes , you can read
> https://phoenix.apache.org/secondary_indexing.html
>
> Regards,
> Ankit Singhal
>
>
>
>
> On Tue, Jun 28, 2016 at 4:25 AM, Vamsi Krishna <vamsi.attluri@gmail.com>
> wrote:
>
>> Team,
>>
>> I'm using HDP 2.3.2 (HBase : 1.1.2, Phoenix : 4.4.0).
>> *Question: *phoenix explain plan not showing any difference after adding
>> a local index on the table column that is used in query filter. Can someone
>> please explain why?
>>
>> *Create table:*
>> CREATE TABLE IF NOT EXISTS VAMSI.TABLE_A (COL1 VARCHAR(36) , COL2
>> VARCHAR(36) , COL3 VARCHAR(36) CONSTRAINT TABLE_A_PK PRIMARY KEY (COL1))
>> COMPRESSION='SNAPPY', SALT_BUCKETS=5;
>> *Insert data:*
>> upsert into vamsi.table_a values ('abc123','abc','123');
>> upsert into vamsi.table_a values ('def456','def','456');
>>
>> *Explain plan:*
>> explain select * from vamsi.table_a where col2 = 'abc';
>> +-------------------------------------------------------------+
>> |                            PLAN                             |
>> +-------------------------------------------------------------+
>> | CLIENT 5-CHUNK PARALLEL 5-WAY FULL SCAN OVER VAMSI.TABLE_A  |
>> |     SERVER FILTER BY COL2 = 'abc'                           |
>> +-------------------------------------------------------------+
>>
>> *Create local index:*
>> CREATE LOCAL INDEX IF NOT EXISTS IDX_TABLE_A_COL2 ON VAMSI.TABLE_A (COL2);
>>
>> *Explain plan:*
>> explain select * from vamsi.table_a where col2 = 'abc';
>> +-------------------------------------------------------------+
>> |                            PLAN                             |
>> +-------------------------------------------------------------+
>> | CLIENT 5-CHUNK PARALLEL 5-WAY FULL SCAN OVER VAMSI.TABLE_A  |
>> |     SERVER FILTER BY COL2 = 'abc'                           |
>> +-------------------------------------------------------------+
>>
>> Thanks,
>> Vamsi Attluri
>>
>> --
>> Vamsi Attluri
>>
>
> --
Vamsi Attluri

Mime
View raw message