CODEWITHSUNDEEP
amazon-redshift
Faheem Sohail
Faheem Sohail

Reputation: 846

Is order of redshift interleaved sort keys important?

Is it important for the where clause to specify interleaved sort keys in the same order as they are defined? I would think it is. If the order is important, why cant the query planner figure this out itself?

Upvotes: 1

Views: 430

Answers (1)

sheh
sheh

Reputation: 1023

I think the order doesn't matter.

In chapter "Comparing Sort Styles", I have found some interesting benchmark.

Table's scheme:

create table cust_sales_date_interleaved as
(select c_custkey, c_nation, c_region, c_mktsegment, d_date::date, lo_revenue
from customer, lineorder, dwdate
where lo_custkey = c_custkey 
and lo_orderdate = dwdate.d_datekey
and lo_revenue > 0)
interleaved sortkey (c_custkey, c_region, c_mktsegment, d_date);

query #1

select max(lo_revenue), min(lo_revenue)
from cust_sales_date_interleaved
where c_region = 'ASIA'
and c_mktsegment = 'FURNITURE';

query #2

select max(lo_revenue), min(lo_revenue)
from cust_sales_date_interleaved
where d_date between '01/01/1996' and '01/14/1996'
and c_mktsegment = 'FURNITURE'
and c_region = 'ASIA';

And we have results

query #1 - 1.46 s

query #2 - 0.80 s

Query #2 is faster because it has extra filter by d_date.

Upvotes: 1

Related Questions