You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@phoenix.apache.org by "Lars Hofhansl (JIRA)" <ji...@apache.org> on 2018/10/17 16:43:00 UTC
[jira] [Comment Edited] (PHOENIX-4972) ORDER BY should use a GLOBAL
index even if the query is not fully covered
[ https://issues.apache.org/jira/browse/PHOENIX-4972?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16649731#comment-16649731 ]
Lars Hofhansl edited comment on PHOENIX-4972 at 10/17/18 4:42 PM:
------------------------------------------------------------------
It's quite a bit trickier for global indexes. Consider the following table and index:
{code:java}
create table test (pk integer primary key, v1 float, v2 float, v3 integer) IMMUTABLE_ROWS=true;
create index g1 on test(v2);
{code}
I now loaded a few million rows (on a single machine), and ran:
{code:java}
> explain select * 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 | null | null | null |
| SERVER TOP 10 ROWS SORTED BY [V2] | null | null | null |
| CLIENT MERGE SORT | null | null | null |
| CLIENT LIMIT 10 | null | null | null |
+----------------------------------------------------+-----------------+----------------+--------------+
{code}
{code}
> select * from test order by v2 limit 10;
+-----------+-------------+---------------+---------+
| PK | V1 | V2 | V3 |
+-----------+-------------+---------------+---------+
| 13919782 | 0.16441835 | 2.9902467E-6 | 992124 |
| 13228299 | 0.37690604 | 3.3516374E-6 | 21039 |
| 13633507 | 0.49935722 | 3.5236542E-6 | 150182 |
| 13088979 | 0.54811877 | 5.527496E-6 | 803913 |
| 13429848 | 0.10183071 | 7.2385224E-6 | 685023 |
| 13550341 | 0.6277581 | 7.675855E-6 | 833173 |
| 14053747 | 0.42457512 | 8.177846E-6 | 977065 |
| 13468963 | 0.21423191 | 8.252017E-6 | 16865 |
| 13495284 | 0.62101346 | 1.0189847E-5 | 302398 |
| 13134905 | 0.94166905 | 1.0432481E-5 | 157500 |
+-----------+-------------+---------------+---------+
10 rows selected (2.367 seconds)
{code}
But:
{code}
> select * from test where (pk,v2) in (select pk,v2 from test order by v2 limit 10) order by v2;
+-----------+-------------+---------------+---------+
| PK | V1 | V2 | V3 |
+-----------+-------------+---------------+---------+
| 13919782 | 0.16441835 | 2.9902467E-6 | 992124 |
| 13228299 | 0.37690604 | 3.3516374E-6 | 21039 |
| 13633507 | 0.49935722 | 3.5236542E-6 | 150182 |
| 13088979 | 0.54811877 | 5.527496E-6 | 803913 |
| 13429848 | 0.10183071 | 7.2385224E-6 | 685023 |
| 13550341 | 0.6277581 | 7.675855E-6 | 833173 |
| 14053747 | 0.42457512 | 8.177846E-6 | 977065 |
| 13468963 | 0.21423191 | 8.252017E-6 | 16865 |
| 13495284 | 0.62101346 | 1.0189847E-5 | 302398 |
| 13134905 | 0.94166905 | 1.0432481E-5 | 157500 |
+-----------+-------------+---------------+---------+
10 rows selected (0.065 seconds)
{code}
{code}
> explain select * from test where (pk,v2) in (select pk,v2 from test order by v2 limit 10) order by v2;
+------------------------------------------------------------------------------------------+-----------------+-----------------+
| PLAN | EST_BYTES_READ | EST_ROWS_READ |
+------------------------------------------------------------------------------------------+-----------------+-----------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER TEST | null | null |
| SERVER SORTED BY [TEST.V2] | null | null |
| CLIENT MERGE SORT | null | null |
| SKIP-SCAN-JOIN TABLE 0 | null | null |
| CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER G1 | null | null |
| SERVER FILTER BY FIRST KEY ONLY | null | null |
| SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY ["V2", "PK"] LIMIT 10 GROUPS | null | null |
| CLIENT 10 ROW LIMIT | null | null |
| DYNAMIC SERVER FILTER BY TEST.PK IN ($118.$120) | null | null |
+------------------------------------------------------------------------------------------+-----------------+-----------------+
{code}
was (Author: lhofhansl):
It's quite a bit trickier for global indexes. Consider the following table and index:
{code:java}
create table test (pk integer primary key, v1 float, v2 float, v3 integer) IMMUTABLE_ROWS=true;
create index g1 on test(v2);
{code}
I now loaded a few million rows (on a single machine), and ran:
{code:java}
> explain select * 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 | null | null | null |
| SERVER TOP 10 ROWS SORTED BY [V2] | null | null | null |
| CLIENT MERGE SORT | null | null | null |
| CLIENT LIMIT 10 | null | null | null |
+----------------------------------------------------+-----------------+----------------+--------------+
> select * from test order by v2 limit 10;
+-----------+-------------+---------------+---------+
| PK | V1 | V2 | V3 |
+-----------+-------------+---------------+---------+
| 13919782 | 0.16441835 | 2.9902467E-6 | 992124 |
| 13228299 | 0.37690604 | 3.3516374E-6 | 21039 |
| 13633507 | 0.49935722 | 3.5236542E-6 | 150182 |
| 13088979 | 0.54811877 | 5.527496E-6 | 803913 |
| 13429848 | 0.10183071 | 7.2385224E-6 | 685023 |
| 13550341 | 0.6277581 | 7.675855E-6 | 833173 |
| 14053747 | 0.42457512 | 8.177846E-6 | 977065 |
| 13468963 | 0.21423191 | 8.252017E-6 | 16865 |
| 13495284 | 0.62101346 | 1.0189847E-5 | 302398 |
| 13134905 | 0.94166905 | 1.0432481E-5 | 157500 |
+-----------+-------------+---------------+---------+
10 rows selected (2.367 seconds)
{code}
But:
{code}
> select * from test where (pk,v2) in (select pk,v2 from test order by v2 limit 10) order by v2;
+-----------+-------------+---------------+---------+
| PK | V1 | V2 | V3 |
+-----------+-------------+---------------+---------+
| 13919782 | 0.16441835 | 2.9902467E-6 | 992124 |
| 13228299 | 0.37690604 | 3.3516374E-6 | 21039 |
| 13633507 | 0.49935722 | 3.5236542E-6 | 150182 |
| 13088979 | 0.54811877 | 5.527496E-6 | 803913 |
| 13429848 | 0.10183071 | 7.2385224E-6 | 685023 |
| 13550341 | 0.6277581 | 7.675855E-6 | 833173 |
| 14053747 | 0.42457512 | 8.177846E-6 | 977065 |
| 13468963 | 0.21423191 | 8.252017E-6 | 16865 |
| 13495284 | 0.62101346 | 1.0189847E-5 | 302398 |
| 13134905 | 0.94166905 | 1.0432481E-5 | 157500 |
+-----------+-------------+---------------+---------+
10 rows selected (0.065 seconds)
> explain select * from test where (pk,v2) in (select pk,v2 from test order by v2 limit 10) order by v2;
+------------------------------------------------------------------------------------------+-----------------+-----------------+
| PLAN | EST_BYTES_READ | EST_ROWS_READ |
+------------------------------------------------------------------------------------------+-----------------+-----------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER TEST | null | null |
| SERVER SORTED BY [TEST.V2] | null | null |
| CLIENT MERGE SORT | null | null |
| SKIP-SCAN-JOIN TABLE 0 | null | null |
| CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER G1 | null | null |
| SERVER FILTER BY FIRST KEY ONLY | null | null |
| SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY ["V2", "PK"] LIMIT 10 GROUPS | null | null |
| CLIENT 10 ROW LIMIT | null | null |
| DYNAMIC SERVER FILTER BY TEST.PK IN ($118.$120) | null | null |
+------------------------------------------------------------------------------------------+-----------------+-----------------+
{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
>
> Like PHOENIX-4964 but for global indexes
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)