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] [Comment Edited] (PHOENIX-4508) Wrong query plan generation
Date Fri, 05 Jan 2018 22:43:00 GMT

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

Flavio Pompermaier edited comment on PHOENIX-4508 at 1/5/18 10:43 PM:
----------------------------------------------------------------------

I can share them if it's important....in People table the PERSON_ID is the
only PK, while in MYTABLE the PK is composed by two string fields: LID
VARCHAR and EID CHAR(40)



was (Author: f.pompermaier):
I can share them if it's important....in People table the PERSON_ID is the
only PK, while in MYTABLE the PK is composed by two string fields: LID
VARCHAR and EID CHAR(40)

On 5 Jan 2018 21:49, "Maryann Xue (JIRA)" <jira@apache.org> wrote:


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

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

[~f.pompermaier], could you please share DDL of the two tables? Are
PERSON_ID and LOCALID both primary keys in their respective tables?

another one, logically equal, does not (unless that I don't apply some
tuning to timeouts).
the second does not.
----------------------------------------------------+-------
----------+----------------+----------------+
                                      | EST_BYTES_READ  | EST_ROWS_READ  |
EST_INFO_TS   |
----------------------------------------------------+-------
----------+----------------+----------------+
                                      | 14155777900     | 12077867       |
1513754378759  |
SCAN OVER PEOPLE                 | 14155777900     | 12077867       |
1513754378759  |
                                       | 14155777900     | 12077867       |
1513754378759  |
                                       | 14155777900     | 12077867       |
1513754378759  |
                                      | 14155777900     | 12077867       |
1513754378759  |
SCAN OVER MYTABLE [0] - [2]  | 14155777900     | 12077867       |
1513754378759  |
HAS_CANDIDATES = false)                   | 14155777900     | 12077867
 | 1513754378759  |
                                          | 14155777900     | 12077867
 | 1513754378759  |
                                       | 14155777900     | 12077867       |
1513754378759  |
                                      | 14155777900     | 12077867       |
1513754378759  |
----------------------------------------------------+-------
----------+----------------+----------------+
PEOPLE  ds ON ds.PERSON_ID = l.LOCALID;
---------------------------------------------------+--------
---------+----------------+----------------+
                                     | EST_BYTES_READ  | EST_ROWS_READ  |
EST_INFO_TS   |
---------------------------------------------------+--------
---------+----------------+----------------+
                                     | 14155777900     | 12077867       |
1513754378759  |
SCAN OVER MYTABLE [0] - [2]  | 14155777900     | 12077867       |
1513754378759  |
HAS_CANDIDATES = false)                  | 14155777900     | 12077867
 | 1513754378759  |
                                      | 14155777900     | 12077867       |
1513754378759  |
                                     | 14155777900     | 12077867       |
1513754378759  |
SCAN OVER PEOPLE               | 14155777900     | 12077867       |
1513754378759  |
                                      | 14155777900     | 12077867       |
1513754378759  |
                                    | 14155777900     | 12077867       |
1513754378759  |
                                      | 14155777900     | 12077867       |
1513754378759  |
                                     | 14155777900     | 12077867       |
1513754378759  |
---------------------------------------------------+--------
---------+----------------+----------------+



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)


> 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