You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@phoenix.apache.org by "Istvan Toth (Jira)" <ji...@apache.org> on 2023/11/20 06:50:00 UTC

[jira] [Commented] (PHOENIX-6998) Perf regression with server merge query plan for hinted uncovered indexes

    [ https://issues.apache.org/jira/browse/PHOENIX-6998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17787832#comment-17787832 ] 

Istvan Toth commented on PHOENIX-6998:
--------------------------------------

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)