Question

Indexes for derived tables

  • 23 February 2015
  • 1 reply
  • 85 views

Hi,


I’m using a derived table for computing some details about a user. The Lookerml manual says that I can use indexes for derived tables, yet I get a warning about indexes being an unknown parameter for a derived table,

http://www.looker.com/docs/reference/derived-tables We use Redshift which I believe should be a Postgres dialect version.


Thank you.


- view: user_avg_value
derived_table:
sql: |
SELECT orders.user_id as user_id
, orders.email as email
, orders.website as website
, AVG(orders.order_value) AS avg_value
, SUM(orders.order_value) AS total_value
, SUM(orders.order_refund) AS total_refund
FROM orders
WHERE NOT cancelled GROUP BY orders.user_id, orders.website, orders.email

indexes: [email, website]
sortkeys: [email]
persist_for: 24 hours

1 reply

Userlevel 1

With Redshift, you’ll actually want to use distkey and sortkey on your derived tables, rather than indexes. You can read a bit about distkey and sortkeys, tuning Redshift in general, and their use in redshift.


In your particular case, you’ll want to remove the indexes parameter, and consider using a distkey along with the sortkey you already set.

Reply