phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Maryann Xue (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-4508) Wrong query plan generation
Date Sat, 06 Jan 2018 03:11:00 GMT

    [ https://issues.apache.org/jira/browse/PHOENIX-4508?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16314332#comment-16314332
] 

Maryann Xue commented on PHOENIX-4508:
--------------------------------------

Thank you, [~f.pompermaier]! Just to confirm, are you using the latest version of Phoenix?
I tried to replicate this issue based on my guess of the DDLs but could not. The query plans
turned out to be different from what you got. I'll just wait for your input then.

> 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
>            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
> ----
> h2. First query
> {code:sql}
> 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;
> {code}
> +---------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
> |                                                     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)
> ----
> h2. Second query
> {code:sql}
> 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;
> {code}
> +--------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
> |                                                     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