phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Flavio Pompermaier <pomperma...@okkam.it>
Subject Query optimization
Date Wed, 20 Dec 2017 16:58:36 GMT
Hi to all,
I'm trying to find the best query for my use case but I found that one
version work and the other one does not (unless that I don't apply some
tuning to timeouts etc like explained in [1]).

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  |
+---------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+


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  |
+--------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
10 rows selected (0.041 seconds)

What do you think? Whould I try to give more resources to HBase/Phoenix or
is the first query the best (and reliable) one?
Any insight about this is highly appreciated..

Best,
Flavio

Mime
View raw message