You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "Lars Hofhansl (Jira)" <ji...@apache.org> on 2021/12/16 22:24:00 UTC
[jira] [Assigned] (PHOENIX-6604) Allow using indexes for wildcard topN queries on salted tables
[ https://issues.apache.org/jira/browse/PHOENIX-6604?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Lars Hofhansl reassigned PHOENIX-6604:
--------------------------------------
Assignee: Lars Hofhansl
> Allow using indexes for wildcard topN queries on salted tables
> --------------------------------------------------------------
>
> Key: PHOENIX-6604
> URL: https://issues.apache.org/jira/browse/PHOENIX-6604
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 5.1.2
> Reporter: Lars Hofhansl
> Assignee: Lars Hofhansl
> Priority: Major
> Fix For: 5.2.0, 5.1.3
>
> Attachments: PHOENIX-6604.5.1.3.v1.patch, 6604-1.5.1.3, 6604.5.1.3
>
>
> Just randomly came across this, playing with TPCH data.
> {code:java}
> CREATE TABLE lineitem (
> orderkey bigint not null,
> partkey bigint,
> suppkey bigint,
> linenumber integer not null,
> quantity double,
> extendedprice double,
> discount double,
> tax double,
> returnflag varchar(1),
> linestatus varchar(1),
> shipdate date,
> commitdate date,
> receiptdate date,
> shipinstruct varchar(25),
> shipmode varchar(10),
> comment varchar(44)
> constraint pk primary key(orderkey, linenumber)) IMMUTABLE_ROWS=true,SALT_BUCKETS=4;
> CREATE LOCAL INDEX l_shipdate ON lineitem(shipdate);{code}
> Now:
> {code:java}
> > explain select * from lineitem order by shipdate limit 1;
> +-----------------------------------------------------------------------------------+
> | PLAN |
> +-----------------------------------------------------------------------------------+
> | CLIENT 199-CHUNK 8859938 ROWS 2044738843 BYTES PARALLEL 199-WAY FULL SCAN OVER LI |
> | SERVER TOP 1 ROW SORTED BY [SHIPDATE] |
> | CLIENT MERGE SORT |
> | CLIENT LIMIT 1 |
> +-----------------------------------------------------------------------------------+
> 4 rows selected (6.525 seconds)
> -- SAME COLUMNS!
> > explain select ORDERKEY, PARTKEY, SUPPKEY, LINENUMBER, QUANTITY, EXTENDEDPRICE, DISCOUNT, TAX, RETURNFLAG, LINESTATUS, SHIPDATE, COMMITDATE, RECEIPTDATE, SHIPINSTRUCT, SHIPMODE, COMMENT from lineitem order by shipdate limit 1;
> +-----------------------------------------------------------------------------------+
> | |
> +-----------------------------------------------------------------------------------+
> | CLIENT 4-CHUNK 4 ROWS 204 BYTES PARALLEL 4-WAY RANGE SCAN OVER LINEITEM [1] |
> | SERVER MERGE [0.PARTKEY, 0.SUPPKEY, 0.QUANTITY, 0.EXTENDEDPRICE, 0.DISCOUNT, |
> | SERVER FILTER BY FIRST KEY ONLY |
> | SERVER 1 ROW LIMIT |
> | CLIENT MERGE SORT |
> | CLIENT 1 ROW LIMIT |
> +-----------------------------------------------------------------------------------+
> 6 rows selected (2.736 seconds){code}
>
> The same happens with a covered global index.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)