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,
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
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!