[jira] [Updated] (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 updated PHOENIX-6604:
---
Fix Version/s: 5.2.0
> 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
>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)
[jira] [Updated] (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
]
Daniel Wong updated PHOENIX-6604:
-
Attachment: PHOENIX-6604.5.1.3.v1.patch
> 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
>Priority: Major
> Fix For: 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)
[jira] [Updated] (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 updated PHOENIX-6604:
---
Summary: Allow using indexes for wildcard topN queries on salted tables
(was: Allow using indexes for wildcard topN query on salted tables)
> 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
>Priority: Major
> Fix For: 5.1.3
>
> Attachments: 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)
