Read data with group by 100000 distinct values on 36 million rows I am looking for sub second solution for reading data from a billion row table with 8 different columns. 

I tried using mysql but it gives sub-par performance. My machine config is :12 core cpu 1.4 Ghz xeon 64GB RAM.

I tried using vertica, citus with postgres, scyllaDB and spark but nothing worked.

Query:

select 
    bd, 
    p_s, 
    sum(v2) v2, 
    sum(v1) v1, 
    sum(case WHEN tf='N' THEN v2 ELSE 0 end) ty_nv,
    sum(case WHEN tf='E' THEN v1+v2 ELSE 0 end) ly_ev, 
    sum(case WHEN tf='S' AND v2&gt;0 AND v1&lt;=0 THEN v2 ELSE 0 end) ty_NBLY_Bv2, 
    sum(case WHEN tf='S' AND v2&lt;0 THEN v2 ELSE 0 end) ty_same_ntv, 
    sum(case WHEN tf='S' AND v2&lt;=0 AND v1&gt;0 THEN v1 ELSE 0 end) ly_BLY_NBv2, 
    sum(case WHEN tf='S' AND v1&lt;0 THEN v1 ELSE 0 end) ly_same_ntv, 
    sum(case WHEN tf='S' THEN v2 ELSE 0 end) ty_sv, 
    sum(case WHEN tf='S' THEN v1 ELSE 0 end) ly_sv,
    sum(case WHEN tf='S' AND v2&gt;0 AND v1&gt;0 THEN v2 ELSE 0 end) AS ty_BLY_Bv2, 
    sum(case WHEN tf='S' AND v2&gt;0 AND v1&gt;0 THEN v1 ELSE 0 end) AS ly_BLY_Bv2, 
    sum(case WHEN tf='S' AND v2&gt;0 AND v1&gt;0 THEN (v2-v1) ELSE 0 end) ty_lysiv 
FROM  (
    select 
        month,
        bd, 
        p_s, 
        si,
        sum(v2) v2,
        sum(v1) v1,
        (case when sum(sf) &gt; 0 then 'S' else (case when sum(nf) &gt; 0 then 'N' else  (case when sum(ef) &gt; 0 then 'E' else null end)end)end) as tf,
        (case when sum(ly_sf) &gt; 0 then 'S' else (case when sum(ly_nf) &gt; 0 then 'N' else  (case when sum(ly_ef) &gt; 0 then 'E' else null end)end)end) as ly_f 
    from  
        temp    
    group by month, si,p_s,bd
    ) as a 
group by p_s,bd


Table Structure is:

CREATE TABLE `temp` (
    `year` int(11) DEFAULT NULL,   
    `month` varchar(3) DEFAULT NULL,
    `si` bigint(20) DEFAULT NULL,   
    `category` varchar(100) DEFAULT NULL,
    `bd` varchar(100) DEFAULT NULL,   
    `pack_type` varchar(100) DEFAULT NULL,   
    `pack_segment` varchar(100) DEFAULT NULL,
    `p_s` int(11) DEFAULT NULL,   
    `v2` double DEFAULT NULL,  
    `v1` double DEFAULT NULL,   
    `sf` int(11) DEFAULT NULL,  
    `nf` int(11) DEFAULT NULL,   
    `ef` int(11) DEFAULT NULL,  
    `ly_sf` int(11) DEFAULT NULL,   
    `ly_nf` int(11) DEFAULT NULL,   
    `ly_ef` int(11) DEFAULT NULL )


Explain Plan of the Query:

"GroupAggregate  (cost=14717923.03..15097409.03 rows=40000 width=60)"
"  -&gt;  Sort  (cost=14717923.03..14727169.03 rows=3698400 width=60)"
"        Sort Key: a.p_s, a.bd”
"        -&gt;  Subquery Scan on a  (cost=12565877.39..13897301.39 rows=3698400 width=60)"
"              -&gt;  GroupAggregate  (cost=12565877.39..13860317.39 rows=3698400 width=64)"
"                    -&gt;  Sort  (cost=12565877.39..12658337.39 rows=36984000 width=64)"
"                          Sort Key: temp.month, temp.si, temp.p_s, temp.bd”
"                          -&gt;  Seq Scan on temp  (cost=0.00..964340.00 rows=36984000 width=64)"
Shrink datatypes --


INT takes 4 bytes; probably overkill for your data.
It is usually ill-advised to split a DATE into multiple columns.
Normalize any repeated VARCHARs.


You have nothing limiting the number of rows you need to look at, so the query must read the entire table.  Unless you have magical disks, you must have enough RAM to hold the entire table, do you?

Since you have nested GROUP BYs, there will need to be one or two temp tables, each with lots of rows, plus one or two sorts.

Your only hope to avoid "sub-par" performance by any database engine is to reformulate your query.

Since you are grouping by month, are the previous month's data static?  That is, could you summarize each month at the beginning of the next month, then use the Summary table?

It might be better to summarize daily, thereby avoiding the moderately large monthly query.

Such a summary table would probably look very much like the output of the derived table (in the from cause).  Probably this would be best:  PRIMARY KEY(p_s, bd, month, si).

Bug?  You are grouping by month.  Does that mean you are combining last July with this July?  Or did you mean to include year in that?  At which point, it is even more compelling to use DATE instead of two columns.