You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "Flavio Pompermaier (JIRA)" <ji...@apache.org> on 2018/01/02 13:40:00 UTC
[jira] [Updated] (PHOENIX-4508) Wrong query plan generation
[ https://issues.apache.org/jira/browse/PHOENIX-4508?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Flavio Pompermaier updated PHOENIX-4508:
----------------------------------------
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
```
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)
```
```
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 |
+--------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
```
was:
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
*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)
```
*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 |
+--------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
```
> Wrong query plan generation
> ---------------------------
>
> Key: PHOENIX-4508
> URL: https://issues.apache.org/jira/browse/PHOENIX-4508
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.13.2-cdh5.11.2
> Reporter: Flavio Pompermaier
> Labels: planner, query
>
> 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
> ```
> 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)
> ```
> ```
> 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 |
> +--------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
> ```
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)