Using Apache Phoenix Secondary Indexes to Speed Up Selective Queries

Why use a secondary index in Phoenix

When a query filters on a non‑key column, Phoenix scans the entire base table unless a secondary index exists. Adding a global secondary index creates a separate HBase table that stores the indexed column plus the primary key, allowing the optimizer to perform an INDEX SCAN and return only matching rows.

Worked example: creating and using a global secondary index

1. Start the Phoenix query console (sqlline.py) as a user with CREATE, ALTER and SELECT privileges on the target namespace.
2. Create a sample table.

CREATE TABLE IF NOT EXISTS sales (
    sale_id VARCHAR PRIMARY KEY,
    sale_date DATE,
    region VARCHAR,
    amount DECIMAL
);

Insert a few rows for demonstration (in a real workload use bulk load).

UPSERT INTO sales VALUES ('S001', '2026-09-01', 'West', 150.00);
UPSERT INTO sales VALUES ('S002', '2026-09-02', 'East', 200.00);
UPSERT INTO sales VALUES ('S003', '2026-09-03', 'West', 75.00);

Add a global secondary index on the region column.

CREATE INDEX idx_sales_region ON sales (region);

Phoenix builds the index as an HBase table named IDX_SALES_REGION and keeps it in sync with DML operations.

Run a query that filters on region and ask Phoenix to explain the plan.

EXPLAIN SELECT * FROM sales WHERE region = 'West';

The output will contain an INDEX SCAN step similar to:

INDEX SCAN over IDX_SALES_REGION (region = 'West')
    -> FETCH sales

Without the index the plan would show a full TABLE SCAN on SALES.

Verify the index exists and is enabled.

SELECT * FROM SYSTEM.CATALOG
WHERE TABLE_NAME = 'IDX_SALES_REGION' AND INDEX_NAME IS NOT NULL;

The STATUS column should read ENABLED.

Limits and common mistakes

• High‑cardinality columns. Indexing a column with many distinct values (e.g., a timestamp with millisecond precision) can make the index larger than the base table, hurting both read and write performance. Only index such columns when the query predicate is extremely selective.
• Schema changes. Adding or dropping columns in the base table does not automatically propagate to existing indexes. After an ALTER TABLE you must drop and recreate the index, or rebuild it manually.
• Mixed predicates. If a WHERE clause mixes an indexed predicate with non‑indexed ones without proper parentheses, the optimizer may fail to push the indexed filter down, resulting in a suboptimal plan. Write the clause as (region = 'West') AND amount > 100 to keep the indexed part together.
• Write overhead. Every UPSERT, DELETE or UPDATE also updates the index. For bulk loads, drop the index, load the data, then recreate it to avoid the per‑row cost.
• Local vs global indexes. On heavily salted tables a local index (scoped to a salt bucket) is smaller and cheaper to maintain, but it can only be used when the query includes the salt key. Use CREATE LOCAL INDEX only when your queries always specify the salt column.

Practical verification steps

1. Run the EXPLAIN statement before and after creating the index; compare latency using the Phoenix query console or JDBC driver with warmed‑up caches.
2. Check SYSTEM.CATALOG for the index entry and confirm STATUS = ENABLED.
3. Monitor write throughput (e.g., via HBase metrics) to ensure the added index does not exceed your workload’s write budget.