[PHOENIX-4508] Order-by not optimized in sort-merge-join on salted tables

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.13.2
• Fix Version/s: 4.13.0, 5.0.0-alpha, 4.14.0
• Labels: planner query
• Assignee: Wei Xue
• Reporter: Flavio Pompermaier
• Created: 02/Jan/18 13:37
• Updated: 26/Jul/18 01:13
• Resolved: 08/Jan/18 19:35

Description

In my Phoenix tables I found that one query ens successfully while another one, logically equal, does not (unless that I don't apply some tuning to timeouts).

The 2 queries extract the same data but, while the first query terminates the second does not.
PS: without the USE_SORT_MERGE_JOIN both queries weren't working

---

First query

SELECT /*+ USE_SORT_MERGE_JOIN */ COUNT(*) 
FROM PEOPLE ds JOIN MYTABLE l ON ds.PERSON_ID = l.LOCALID
WHERE l.EID IS NULL AND l.DSID = 'PEOPLE' AND l.HAS_CANDIDATES = FALSE;

----------------------------------------------------------------------------------------------------------------------------------------------------------+

PLAN

EST_BYTES_READ

EST_ROWS_READ

EST_INFO_TS

----------------------------------------------------------------------------------------------------------------------------------------------------------+

SORT-MERGE-JOIN (INNER) TABLES	14155777900	12077867	1513754378759
CLIENT 42-CHUNK 6168903 ROWS 11324622221 BYTES PARALLEL 3-WAY FULL SCAN OVER PEOPLE	14155777900	12077867	1513754378759
SERVER FILTER BY FIRST KEY ONLY	14155777900	12077867	1513754378759
CLIENT MERGE SORT	14155777900	12077867	1513754378759
AND (SKIP MERGE)	14155777900	12077867	1513754378759
CLIENT 15-CHUNK 5908964 ROWS 2831155679 BYTES PARALLEL 15-WAY RANGE SCAN OVER MYTABLE [0] - [2]	14155777900	12077867	1513754378759
SERVER FILTER BY (EID IS NULL AND DSID = 'PEOPLE' AND HAS_CANDIDATES = false)	14155777900	12077867	1513754378759
SERVER SORTED BY [L.LOCALID]	14155777900	12077867	1513754378759
CLIENT MERGE SORT	14155777900	12077867	1513754378759
CLIENT AGGREGATE INTO SINGLE ROW	14155777900	12077867	1513754378759

----------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows selected (0.041 seconds)

---

Second query

SELECT /*+ USE_SORT_MERGE_JOIN */ COUNT(*) 
FROM (SELECT LOCALID FROM MYTABLE
WHERE EID IS NULL AND DSID = 'PEOPLE' AND HAS_CANDIDATES = FALSE) l JOIN PEOPLE  ds ON ds.PERSON_ID = l.LOCALID;

---------------------------------------------------------------------------------------------------------------------------------------------------------+

PLAN

EST_BYTES_READ

EST_ROWS_READ

EST_INFO_TS

---------------------------------------------------------------------------------------------------------------------------------------------------------+

SORT-MERGE-JOIN (INNER) TABLES	14155777900	12077867	1513754378759
CLIENT 15-CHUNK 5908964 ROWS 2831155679 BYTES PARALLEL 3-WAY RANGE SCAN OVER MYTABLE [0] - [2]	14155777900	12077867	1513754378759
SERVER FILTER BY (EID IS NULL AND DSID = 'PEOPLE' AND HAS_CANDIDATES = false)	14155777900	12077867	1513754378759
CLIENT MERGE SORT	14155777900	12077867	1513754378759
AND (SKIP MERGE)	14155777900	12077867	1513754378759
CLIENT 42-CHUNK 6168903 ROWS 11324622221 BYTES PARALLEL 42-WAY FULL SCAN OVER PEOPLE	14155777900	12077867	1513754378759
SERVER FILTER BY FIRST KEY ONLY	14155777900	12077867	1513754378759
SERVER SORTED BY [DS.PERSON_ID]	14155777900	12077867	1513754378759
CLIENT MERGE SORT	14155777900	12077867	1513754378759
CLIENT AGGREGATE INTO SINGLE ROW	14155777900	12077867	1513754378759

---------------------------------------------------------------------------------------------------------------------------------------------------------+