phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Flavio Pompermaier (JIRA)" <j...@apache.org>
Subject [jira] [Created] (PHOENIX-4508) Wrong query plan generation
Date Tue, 02 Jan 2018 13:38:00 GMT
Flavio Pompermaier created PHOENIX-4508:
-------------------------------------------

             Summary: 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


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)

Mime
View raw message