phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ankit Singhal <ankitsingha...@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 Wed, 29 Jun 2016 19:15:38 GMT
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
>

Mime
View raw message