RE: phoenix explain plan not showing any difference after adding a local index on the table column that is used in query filter

[Talanki, Vikash]

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 <ankitsingha...@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.attl...@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