phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Taylor <jamestay...@apache.org>
Subject Re: Having Global covered Index
Date Mon, 18 Jan 2016 18:27:47 GMT
I'd recommend trying with and without the index with a representative data
set in Pherf, our performance testing tool:
https://phoenix.apache.org/pherf.html

Do a lot of your queries filter only on b (i.e. without any filter on a or
a1)? If so, that'll end up being a full table scan. One alternative is to
force a skip scan using the following hint:
SELECT /*+ SKIP_SCAN */ ...

You can read more about that here: https://phoenix.apache.org/skip_scan.html

However, since there are two columns in front of b in the primary key
constraint, I doubt that will help performance. It depends on the
cardinality of the three columns in the primary key constraint.

But the definitive answer is that it really does depend on your use case
and performance requirements.

On Mon, Jan 18, 2016 at 10:12 AM, Kumar Palaniappan <
kpalaniappan@marinsoftware.com> wrote:

> Thanks James.
>
> Do we need an index on a pk column, since it's a last element in the
> rowkey, to speed up the query?   ( because of this, write won't be impacted
> on that table) we do leverage the call queue.
>
> Kumar Palaniappan <http://about.me/kumar.palaniappan>
> <https://twitter.com/intent/follow?original_referer=https://twitter.com/about/resources/buttons&region=follow_link&screen_name=megamda&source=followbutton&variant=2.0>
>  [image: Description: Macintosh HD:Users:Kumarappan:Desktop:linkedin.gif]
> <http://www.linkedin.com/in/kumarpalaniappan>
>
> On Jan 18, 2016, at 10:07 AM, James Taylor <jamestaylor@apache.org> wrote:
>
> See https://phoenix.apache.org/secondary_indexing.html
>
> Hints are not required unless you want Phoenix to join between the index
> and data table because the index isn't fully covered and some of these non
> covered columns are referenced in the query.
>
> bq. Doesnt a single global covered index sufficient for both use cases?
>
> That depends entirely on the requirements for your use case. If query
> performance is "good enough" without the index(es), then you don't need
> them.
>
> On Mon, Jan 18, 2016 at 8:14 AM, Kumar Palaniappan <
> kpalaniappan@marinsoftware.com> wrote:
>
>>
>>
>> We have a table
>>
>> *create table t1 ( a bigint not null, a1 bigint not null, b bigint not
>> null, c varchar, d varchar pk_constraint "t1_pk" (a, a1,b))*
>>
>>
>> create a global indices as -
>>
>> *create index id_t1 on t1 (b) include (a,a1,c,d)* - this one is to speed
>> up filtering on b since it is the last element in the row key. (I highly
>> doubt we need this)
>>
>> *create index id_t1_c on t1 (c) include (a,d)*  - to increase the perf
>> on filtering the c
>>
>>
>> What are the impacts on these indices? does it really make any difference
>> in Phoenix? do we need to enforce the hints to pick up the index on run
>> time, if we have multiple indices on a single table?
>>
>> Doesnt a single global covered index sufficient for both use cases?
>>
>>
>>
>

Mime
View raw message