[jira] [Created] (PHOENIX-889) Query performance with Join feature is poor

["yang ming (JIRA)" <ji...@apache.org>]

yang ming created PHOENIX-889:
---------------------------------

             Summary: Query performance with Join feature is poor
                 Key: PHOENIX-889
                 URL: https://issues.apache.org/jira/browse/PHOENIX-889
             Project: Phoenix
          Issue Type: Improvement
    Affects Versions: 3.0.0
            Reporter: yang ming


table1:
create table if not exists yk.video_summary
(
videoid integer not null,
date date not null,
platform varchar not null,
device varchar not null,
systemgroup varchar not null,
system varchar not null,
vv bigint
constraint pk primary key (videoid, date,platform, device, systemgroup,system)
)salt_buckets = 30,versions=1,compression='snappy';

table2:
create table if not exists yk.video_meta(
     videoid integer  not null,
     showid integer  not null,
     title varchar  not null,
     showvideotype varchar  not null,
     publishtime date not null
     constraint pk primary key (videoid,showid,showvideotype,publishtime)
) salt_buckets = 10,versions=1,compression='snappy';

Query1:
select videoid from YK.VIDEO_META where showid=99299;
Result:
+------------+
|  VIDEOID   |
+------------+
| 137102991  |
| 151113895  |
| 171559204  |
| 171559439  |
| 171573932  |
| 171574082  |
| 171574164  |
| 171643206  |
| 171677219  |
| 171764188  |
| 171794335  |
| 171874661  |
+------------+

Query2:
select date,sum(vv) as sv from YK.VIDEO_SUMMARY where videoid in (137102991,151113895,171559204,171559439,171573932,171574082,171574164,171643206,171677219,171764188,171794335,171874661) and date>=to_date('2014-03-17','yyyy-MM-dd') and date<=to_date('2014-03-23','yyyy-MM-dd') group by date order by date desc;
Result(cost 3s):
+---------------------+------------+
|        DATE         |     SV     |
+---------------------+------------+
| 2014-03-23          | 2795341    |
| 2014-03-22          | 3111076    |
| 2014-03-21          | 3588108    |
| 2014-03-20          | 5972243    |
| 2014-03-19          | 5192865    |
| 2014-03-18          | 2848761    |
| 2014-03-17          | 8922       |
+---------------------+------------+

Query3:
select a.date,sum(a.vv) as sv from YK.VIDEO_SUMMARY as a inner join YK.VIDEO_META as b on (b.showid=99299 and a.videoid=b.videoid) where a.date>=to_date('2014-03-17','yyyy-MM-dd') and a.date<=to_date('2014-03-23','yyyy-MM-dd') group by a.date order by a.date desc;
Result:
{color:red}Not return results,the server load is high.I kill this query.{color}

The execution plan:
Query2:
+------------+
|    PLAN    |
+------------+
| CLIENT PARALLEL 90-WAY SKIP SCAN ON 360 RANGES OVER YK.VIDEO_SUMMARY [0,137102991,'2014-03-17 00:00:00.000'] - [29,171874661,'2014-03-23 00:00:00.000'] |
|     SERVER AGGREGATE INTO DISTINCT ROWS BY [DATE] |
| CLIENT MERGE SORT |
| CLIENT SORTED BY [DATE DESC] |
+------------+

Query3:
+------------+
|    PLAN    |
+------------+
| CLIENT PARALLEL 240-WAY FULL SCAN OVER YK.VIDEO_SUMMARY |
|     SERVER FILTER BY (DATE >= '2014-03-17 00:00:00.000' AND DATE <= '2014-03-23 00:00:00.000') |
|     SERVER AGGREGATE INTO DISTINCT ROWS BY [DATE] |
| CLIENT MERGE SORT |
| CLIENT SORTED BY [DATE DESC] |
|     PARALLEL EQUI-JOIN 1 HASH TABLES: |
|     BUILD HASH TABLE 0 (SKIP MERGE) |
|         CLIENT PARALLEL 60-WAY FULL SCAN OVER YK.VIDEO_META |
|             SERVER FILTER BY FIRST KEY ONLY AND SHOWID = 99299 |
|         CLIENT MERGE SORT |
+------------+

Table YK.VIDEO_META is small. Query 3 with join is a full scan,it's performance with is very poor!
Is there any other suggestion?



--
This message was sent by Atlassian JIRA
(v6.2#6252)