You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Vamsi Krishna <va...@gmail.com> on 2016/06/28 11:25:20 UTC
phoenix explain plan not showing any difference after adding a local
index on the table column that is used in query filter
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
Re: phoenix explain plan not showing any difference after adding a
local index on the table column that is used in query filter
Posted by vikashtalanki <vt...@visa.com>.
Please replace 'Select * from...' with 'Select COL1,COL2,COL3 from...'.
I did the same and it worked. Not sure about the exact reason.
--
View this message in context: http://apache-phoenix-user-list.1124778.n5.nabble.com/phoenix-explain-plan-not-showing-any-difference-after-adding-a-local-index-on-the-table-column-that-r-tp1996p2000.html
Sent from the Apache Phoenix User List mailing list archive at Nabble.com.
Re: phoenix explain plan not showing any difference after adding a
local index on the table column that is used in query filter
Posted by Vamsi Krishna <va...@gmail.com>.
Thanks Vikash.
On Wed, Jun 29, 2016 at 12:26 PM Talanki, Vikash <vt...@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 <an...@gmail.com>
> *Date: *Wednesday, Jun 29, 2016, 12:15
> *To: *user@phoenix.apache.org <us...@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 <va...@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
Re: phoenix explain plan not showing any difference after adding a
local index on the table column that is used in query filter
Posted by Ankit Singhal <an...@gmail.com>.
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 <va...@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
>