Which pagination method in Apache Phoenix avoids OFFSET‑related scan overhead for large HBase tables?

Answer

Use key‑set (cursor) pagination: query with a WHERE clause that provides a strict lower bound on the primary key (or a column covered by a matching secondary index), ORDER BY that same key, and LIMIT the page size. This pattern lets Phoenix push the bound down as a HBase scan range start key, so the scanner does not need to skip rows and the cost does not grow with the offset.

Confirmed facts

• When the query includes WHERE rowkey > :last_key and ORDER BY rowkey, the optimizer can rewrite it to a PhoenixScan with a startKey predicate and no OFFSET operator.
• If table statistics are current, the cost‑based optimizer consistently chooses this range scan over a full table scan.
• A secondary index that matches the ORDER BY column and any filter columns can further reduce I/O by allowing the scan to start from the index entry.

Likely explanation / conditions

The optimizer’s decision depends on having accurate statistics and a monotonic, orderable key. With stale statistics it may still pick the range scan because the bounded scan is usually cheaper, but the choice is not guaranteed. If no suitable index exists and the WHERE clause references non‑key columns, the scanner may need to examine a large range and apply filters, diminishing the benefit.

Steps to verify and use this method

1. Ensure the table’s primary key (or the column you will paginate on) is monotonically increasing or at least orderable.
2. Collect fresh statistics: UPDATE STATISTICS TABLE_NAME;
3. Run the cursor‑based query, for example:

   SELECT * FROM MY_TABLE
   WHERE ROWKEY > :last_key
   ORDER BY ROWKEY
   LIMIT :page_size;

4. Check the plan with EXPLAIN; look for a PhoenixScan showing a startKey bound and no OFFSET.
5. Measure latency across successive pages; it should stay roughly constant rather than increase linearly.

Missing diagnostic detail

Are the table statistics up‑to‑date? If they are stale, the optimizer might fall back to a full scan, which would change the recommendation to either refresh statistics or consider adding a matching secondary index.