[jira] [Updated] (PHOENIX-6604) Index not used for wildcard topN query on salted table
[
https://issues.apache.org/jira/browse/PHOENIX-6604?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Lars Hofhansl updated PHOENIX-6604:
---
Description:
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.
was:
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;{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
[jira] [Updated] (PHOENIX-6604) Index not used for wildcard topN query on salted table
[
https://issues.apache.org/jira/browse/PHOENIX-6604?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Lars Hofhansl updated PHOENIX-6604:
---
Attachment: 6604-1.5.1.3
> Index not used for wildcard topN query on salted table
> --
>
> 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;{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}
>
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Updated] (PHOENIX-6604) Index not used for wildcard topN query on salted table
[
https://issues.apache.org/jira/browse/PHOENIX-6604?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Lars Hofhansl updated PHOENIX-6604:
---
Attachment: 6604.5.1.3
> Index not used for wildcard topN query on salted table
> --
>
> 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.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;{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}
>
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Updated] (PHOENIX-6604) Index not used for wildcard topN query on salted table
[
https://issues.apache.org/jira/browse/PHOENIX-6604?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Lars Hofhansl updated PHOENIX-6604:
---
Summary: Index not used for wildcard topN query on salted table (was:
Local index not used for wildcard topN query on salted table)
> Index not used for wildcard topN query on salted table
> --
>
> 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
>
>
> 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;{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}
>
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
