-1

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.

  1. Text fields aren't large
  2. Every column can be used in the WHERE clause
  3. No joins with other tables
  4. 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?

  • any progress with this? Have you looked into compression and columnar storage? Where is your database hosted? – Peter Aug 23 '16 at 16:00
1

As you're accessing all the columns, then consider row based rather than columnar.

Partitioning will help. Pick a column that is easy to divide into around 100 ranges, or that has 100 or so distinct values.

As you are not joining to any other tables, you might as well distribute randomly. This may be faster than hashing a text column.

Nothing beats trying it, especially as we have limited information about the content of your tables or the queries that you are running. I appreciate that repeated performance testing on billion row tables isn't easy though.

  • 1. Text fields aren't large 2. Every column can be used in the WHERE clause 3. No joins with other tables 4. 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 *) – Dor Cohen Apr 6 '16 at 11:37
  • @DorCohen Add this info in the question. – ypercubeᵀᴹ Apr 6 '16 at 12:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.