[
https://issues.apache.org/jira/browse/PHOENIX-6998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17787832#comment-17787832
]
Istvan Toth edited comment on PHOENIX-6998 at 11/20/23 6:49 AM:
----------------------------------------------------------------
What I suspect happens:
Take a large query, which returns ~10% of the data rows, with relatively even
distribution:
Old code:
- Scans are run on index
- Results are returned to client
- Client segments index results by data table guideposts
- Client starts one scan by data table guidepost
New code:
- Scans are run on index
- RS takes index results ( and batches them)
- Batch is segmented by data table guideposts
- EACH RS starts one scan per guidepost.
Consider data table and index table both having N guideposts.
with the old code, up to N scans are run on the index table, and up to N scans
are run on the data tables.
With the new code, withe ideal batching, up to N scans are run on the index
table, and up to N^2 scans are run on the data table.
Even though the data table scans return the same amount of cells, the amount of
cells scanned can be multiplied by the number of guideposts.
This is not even a worst case scenario, as it assumes that the index results
fit into on batch.
In a really worst case scenario, the number of scans is N*N*((results per
guidepost)/(batch size))
was (Author: stoty):
What I suspect happens:
Take a large query, which returns ~10% of the data rows, with relatively even
distribution:
Old code:
- Scans are run on index
- Results are returned to client
- Client segments index results by data table guideposts
- Client starts one scan by data table guidepost
New code:
- Scans are run on index
- RS takes index results ( and batches them)
- Batch is segmented by data table guideposts
- EACH RS starts one scan guidepost.
Consider data table and index table both having N guideposts.
with the old code, up to N scans are run on the index table, and up to N scans
are run on the data tables.
With the new code, withe ideal batching, up to N scans are run on the index
table, and up to N^2 scans are run on the data table.
Even though the data table scans return the same amount of cells, the amount of
cells scanned can be multiplied by the number of guideposts.
This is not even a worst case scenario, as it assumes that the index results
fit into on batch.
In a really worst case scenario, the number of scans is N*N*((results per
guidepost)/(batch size))
> Perf regression with server merge query plan for hinted uncovered indexes
> -------------------------------------------------------------------------
>
> Key: PHOENIX-6998
> URL: https://issues.apache.org/jira/browse/PHOENIX-6998
> Project: Phoenix
> Issue Type: Bug
> Components: core
> Reporter: Istvan Toth
> Assignee: Jing Yu
> Priority: Minor
>
> Server merge indexes can be a huge performance win, especially for limit /
> TopN queries that can be resolved on the index table, and which the
> skip-scan-join plan does not push down.
> However, for a large resultset, and poor index selectivity the new server
> merge query plan is still significantly slower than the old skip-scan-join
> plan.
> In one case I had these results:
> unindexed query: 52s
> hinted skip-scan-join query: 90s
> hinted server merge query: 300s
> This was a manufactured worst case dataset, but we can still see that new
> plan can perform significantly worse than the old one.
> As we ultimately read the same cells from both tables, I can only conlcude
> that even the new batched server merge performs worse than getting the rows
> from the client for large resultsets.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
