Question about the performance of the local index in Apache Phoenix

bgkim — Fri, 17 Feb 2023 04:03:37 GMT

Hello,

I have a question about the performance of the local index in Apache Phoenix

For example, I have a table TBL_X having 6 columns (A , B ,C ,D E, F).

1. Primary Key is (A, B)

2. One of the query patterns is "Select * from TBL_X where A = 'val1' and C = 'val3' "

For the better performance of the above query, I try to build the local index.

Does [ Local index on (C, A) ] perform better than [ Local index on (C) ] ??

Will there be no performance difference since A is already in the first part of the PK ??

Re: Question about the performance of the local index in Apache Phoenix

smdas — Tue, 28 Feb 2023 06:54:12 GMT

Hello @bgkim

Thanks for using Cloudera Community. To your Q, the Composite Primary Key would require using both A & B in WHERE Clause as the Indexing is done collectively. As such, Your SELECT Query would ideally benefit upon creating a Local Index on A & C. You may review [1] as Read-Heavy Use-Case benefit via Global Index with Penalty incurred during Writes. Additionally, Phoenix offers Covered Index & Explain Plan helps confirming the Index Usage. Link [2] offers few examples as well.

With all recommendations, Best Advise is always to review the Performance internally prior to implementing them in Production.

Regards, Smarak

[1] https://phoenix.apache.org/secondary_indexing.html

[2] https://learn.microsoft.com/en-us/azure/hdinsight/hbase/apache-hbase-phoenix-performance

question Question about the performance of the local index in Apache Phoenix in Support Questions

Question about the performance of the local index in Apache Phoenix

Re: Question about the performance of the local index in Apache Phoenix