slow read for cassandra time series

[michael <ro...@yahoo.com>]

I have a slow query that is making me think I don't understand the data model for
time series: 
select asset, returns from marketData where date >= 20130101 and date <= 20130110
allow filtering;

create table marketData {
asset varchar,
returns double,
date timestamp,
PRIMARY KEY(asset, date) }

cassandra blows up with out of memory exception when one's day of data
is only about 1 mb in size.

Re: slow read for cassandra time series

[Sylvain Lebresne <sy...@datastax.com>]

> I have a slow query that is making me think I don't understand the data
model for
> time series:
> select asset, returns from marketData where date >= 20130101 and date <=
20130110
> allow filtering;
>
> create table marketData {
> asset varchar,
> returns double,
> date timestamp,
> PRIMARY KEY(asset, date) }

You can only efficiently query a "time series" *within the same* partition
key in Cassandra.
In other words, the following works efficiently:
  SELECT returns FROM markerData WHERE asset='something' AND date >=
20130101 AND date <= 20130110

But it doesn't (work efficiently) if you don't specify a value of the
partition
key, i.e. asset in that case (Cassandra has to scan all your data to check
what
matches basically). Your cue should be the "ALLOW FILTERING" in your query.
The
fact that you *have to* add it for the query to work basically means "this
query will not be efficient".

--
Sylvain