Marcin Januszkiewicz created PHOENIX-4336:
---------------------------------------------
Summary: SELECT + ORDER BY performance much worse than self-join
Key: PHOENIX-4336
URL: https://issues.apache.org/jira/browse/PHOENIX-4336
Project: Phoenix
Issue Type: Bug
Reporter: Marcin Januszkiewicz
We have a wide table with 100M records created with the following DDL:
{code:sql}
CREATE TABLE traces (
rowkey VARCHAR PRIMARY KEY,
time VARCHAR,
number VARCHAR,
+40 more columns)
CREATE LOCAL INDEX ix_0 ON traces (UPPER(number)) INCLUDE (time, + some other
columns used for filtering)
{code}
We want to select into a large (~30M records) subset of this data with the
query:
{code:sql}
SELECT *all columns*
FROM traces
WHERE (UPPER(number) LIKE 'PO %')
ORDER BY time DESC, ROWKEY
LIMIT 101;
{code}
{noformat}
+-------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
| PLAN
| EST_BYTES_READ |
EST_ROWS_READ | EST_INFO_TS |
+-------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
| CLIENT 234-CHUNK 39850892 ROWS 73610048115 BYTES PARALLEL 234-WAY RANGE SCAN
OVER TRACES [1,'PO '] - [1,'PO!'] | 73610048115 |
39850892 | 1509102519122 |
| SERVER TOP 101 ROWS SORTED BY [cf."time" DESC, "ROWKEY"]
| 73610048115 |
39850892 | 1509102519122 |
| CLIENT MERGE SORT
| 73610048115 |
39850892 | 1509102519122 |
| CLIENT LIMIT 101
| 73610048115 |
39850892 | 1509102519122 |
+-------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
{noformat}
This times out after 15 minutes and puts a huge load on our cluster.
We have an alternate way of selecting this data:
{code:sql}
SELECT t.rowkey, *all columns*
FROM TRACES t
JOIN (
SELECT rowkey
FROM TRACES
WHERE (UPPER(number) LIKE 'PO %')
ORDER BY time DESC, ROWKEY
LIMIT 101
) ix
ON t.ROWKEY = ix.ROWKEY
order by t.ROWKEY;
{code}
{noformat}
+---------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------+-----------+
| PLAN
| EST_BYTES_READ
| EST_ROWS_READ | EST_INFO |
+---------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------+-----------+
| CLIENT 1500-CHUNK 97154640 ROWS 462422573830 BYTES PARALLEL 5-WAY FULL SCAN
OVER TRACES | 73610048115
| 39850892 | 150910251 |
| CLIENT MERGE SORT
| 73610048115
| 39850892 | 150910251 |
| PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)
| 73610048115
| 39850892 | 150910251 |
| CLIENT 234-CHUNK 39850892 ROWS 73610048115 BYTES PARALLEL 234-WAY
RANGE SCAN OVER TRACES [1,'PO '] - [1,'PO!'] | 73610048115
| 39850892 | 150910251 |
| SERVER TOP 101 ROWS SORTED BY [cf."time" DESC, "ROWKEY"]
| 73610048115
| 39850892 | 150910251 |
| CLIENT MERGE SORT
| 73610048115
| 39850892 | 150910251 |
| CLIENT LIMIT 101
| 73610048115
| 39850892 | 150910251 |
| DYNAMIC SERVER FILTER BY T.ROWKEY IN (IX.ROWKEY)
| 73610048115
| 39850892 | 150910251 |
+---------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------+-----------+
{noformat}
Which completes in just under a minute.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
