phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From NaHeon Kim <honey.and...@gmail.com>
Subject Ask performance advice
Date Wed, 05 Apr 2017 03:01:46 GMT
Hi all,

My team has a Phoenix table containing over 30,000,000 rows and try to
speed up its query performance.
It'll be perfect if all queries could be done within 1~2 seconds.

----- table schema -----

CREATE TABLE MY_TABLE (
   REGYMDT timestamp not null,
   OBJ_ID varchar(50) not null,
   KEYWORD varchar not null,
   OBJ_GRP varchar(50),
   CNT integer,
   POST_TP varchar(20),
   CONSTRAINT PK PRIMARY KEY (REGYMDT, OBJ_ID, KEYWORD)
) IMMUTABLE_ROWS = true;

create index objgrp_reg_kwd on my_table(obj_grp, regymdt, keyword) include
(cnt);

----- tuning-needed query -----

select keyword, sum(cnt) as count
from my_table
where regymdt >= to_timestamp('2017-04-03 00:00') and regymdt <=
to_timestamp('2017-04-03 23:00')
and obj_grp = '100'
group by keyword
order by count desc;


The reason why obj_grp isn't PK is that it's optional value.
I could use obj_grp's default value and propagate it to PK, but still not
think it's desired pattern.

The query above takes more than 20 seconds!
It does *not use* objgrp_reg_kwd index, doing just regymdt range scan.

Thanks in advance!
NaHeon

Mime
View raw message