[
https://issues.apache.org/jira/browse/PHOENIX-4972?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16659811#comment-16659811
]
Lars Hofhansl commented on PHOENIX-4972:
----------------------------------------
This does is for me. Simply splices the ORDER BY as well as the LIMIT into the
subquery.
Unlike PHOENIX-4964, you still *need to hint the query*, though; won't happen
automatically (it's harder to figure out whether this is beneficial or not).
With this:
{code:java}
explain select /*+ INDEX(test g1) */ * from test order by v2 limit 10;
+--------------------------------------------------------------------------+-----------------+----------------+--------------+
| PLAN |
EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |
+--------------------------------------------------------------------------+-----------------+----------------+--------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER TEST |
390 | 10 | 0 |
| SERVER TOP 10 ROWS SORTED BY [TEST.V2] |
390 | 10 | 0 |
| CLIENT MERGE SORT |
390 | 10 | 0 |
| CLIENT LIMIT 10 |
390 | 10 | 0 |
| SKIP-SCAN-JOIN TABLE 0 |
390 | 10 | 0 |
| CLIENT 1-CHUNK 10 ROWS 390 BYTES SERIAL 1-WAY FULL SCAN OVER G1 |
390 | 10 | 0 |
| SERVER FILTER BY FIRST KEY ONLY |
390 | 10 | 0 |
| SERVER 10 ROW LIMIT |
390 | 10 | 0 |
| CLIENT 10 ROW LIMIT |
390 | 10 | 0 |
| DYNAMIC SERVER FILTER BY "TEST.PK" IN ($126.$128) |
390 | 10 | 0 |
+--------------------------------------------------------------------------+-----------------+----------------+--------------+
{code}
And
{code:java}
select /*+ INDEX(test g1) */ * from test order by v2 limit 10;
+-----------+-------------+---------------+---------+
| PK | V1 | V2 | V3 |
+-----------+-------------+---------------+---------+
| 37521881 | 0.06029208 | 1.5187367E-6 | 137988 |
| 37599850 | 0.07868222 | 1.808064E-6 | 484759 |
| 37543194 | 0.66105723 | 2.8379652E-6 | 81272 |
| 37263557 | 0.9790291 | 4.407983E-6 | 602335 |
| 37191472 | 0.48405886 | 4.6402142E-6 | 680119 |
| 37297270 | 0.3557069 | 1.115726E-5 | 593315 |
| 37272742 | 0.3983135 | 1.5404992E-5 | 805936 |
| 37634838 | 0.797158 | 1.6470609E-5 | 208149 |
| 37623234 | 0.60025907 | 1.7742768E-5 | 849905 |
| 37238655 | 0.064662 | 2.4182344E-5 | 274513 |
+-----------+-------------+---------------+---------+
10 rows selected (0.077 seconds)
{code}
> ORDER BY should use a GLOBAL index even if the query is not fully covered
> -------------------------------------------------------------------------
>
> Key: PHOENIX-4972
> URL: https://issues.apache.org/jira/browse/PHOENIX-4972
> Project: Phoenix
> Issue Type: Improvement
> Reporter: Lars Hofhansl
> Priority: Minor
> Attachments: PHOENIX-4972-4.x-HBase-1.4-v1.patch
>
>
> Like PHOENIX-4964 but for global indexes
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
