Search code examples
kdb

kdb q - group table within partition

Starting from a fresh partition mydb I am saving the following three tables table1, table2, table3 in partitions 2018.01.01, 2018.01.02, 2018.01.03, respectively:

npertable:10000000;

table1:([]date:npertable?2018.01.01+til 25;acc:npertable?`C123`C132`C321`C121`C131;c:npertable?til 100);
table2:([]date:npertable?2018.02.01+til 25;acc:npertable?`C123`C132`C321`C121`C131;c:npertable?til 100);
table3:([]date:npertable?2018.03.01+til 25;acc:npertable?`C123`C132`C321`C121`C131;c:npertable?til 100);

table1:`date xasc table1;
table2:`date xasc table2;
table3:`date xasc table3;

`:mydb/2018.01.01/t/ set .Q.en[`:mydb;table1];
`:mydb/2018.01.02/t/ set .Q.en[`:mydb;table2];
`:mydb/2018.01.03/t/ set .Q.en[`:mydb;table3];

You can see that I have different acc groups that I will later select on. When I sort the tables before storing additionally by acc I get a slight speedup (253 vs 391 milliseconds). So if I later want to query

select from t where date=2018.01.01, acc=`C123

is sorting by acc before storing the best I can do? Or is there something in storing the partitions that will create an index for the different acc groups?

Thanks for the help

Solution

  • I think you should use the parted attribute for optimizing your queries. For example you can use this bit to sort by acc and apply the attribute.

    {@[`acc xasc .Q.par[`:mydb;x;`t];`acc;`p#]}'[2018.01.01 2018.01.02 2018.01.03]

    For more details about the parted attribute and its effects you can read this whitepaper from KX -> https://kx.com/media/2017/11/Columnar_database_and_query_optimization.pdf

    Also please be aware that you can use a month partition to suit your needs. If I properly understand your example you have year.day.month so you can reduce this to year.month if day will always be 01 i.e Instead of using 

    `:mydb/2018.01.01/t/ set .Q.en[`:mydb;table1];

    you can simply use 

    `:mydb/2018.01/t/ set .Q.en[`:mydb;table1];

    You can find more details about achieving this here -> https://code.kx.com/wiki/JB:KdbplusForMortals/partitioned_tables#1.3.7.2_Monthly