I've created the following table on GreenPlum:
CREATE TABLE data."CDR" ( mcc text, mnc text, lac text, cell text, from_number text, to_number text, cdr_time timestamp without time zone ) WITH ( OIDS = FALSE,appendonly=true, orientation=column,compresstype=quicklz, compresslevel=1 ) DISTRIBUTED BY (from_number);
I've loaded one billion rows to this table but every query works very slow.
- Text fields aren't large
- Every column can be used in the WHERE clause
- No joins with other tables
- I choose this key since it's the closest one to be a PK that isn't a date field 5. I'm fetching the full row from the DB (e.g. SELECT *)
What can I do to speed up my queries?
Using PARTITION? using indexes?
maybe using a different DB like Cassandra or Hadoop?