Change order of sortkey to descending

Question

We have a 2-node Redshift cluster with a table with around 100M records. We marked a timestamp column as the sortkey - because the queries are always time restricted. However, our use-case requires the results has to be sorted in the descending order (on the sortkey).

After some benchmarking, we noticed that the average time taken around 10s. However, when the reverse ordering was removed, the average time came down to under 1s.

Is it possible to reverse the order of the sortkey to be of descending order? The official documentation doesn't seem to indicate that is possible. However I tried putting this while creating a new table:

sortkey(start_time DESC)

There were no errors but it doesn't seem to have any effect.

EDIT: Added the result of EXPLAIN statement on the queries.

• The query with order_by ASC

    explain select * from kcdr_sr_desc where user_id=396747 and start_time > '2016-01-01' and start_time < '2016-07-01' order by start_time limit 20;
  

  Result:

      XN Limit  (cost=0.00..10.86 rows=20 width=300)
      ->  XN Merge  (cost=0.00..709235.56 rows=1306585 width=300)
         Merge Key: start_time
          ->  XN Network  (cost=0.00..709235.56 rows=1306585 width=300)
         Send to leader
         ->  XN Seq Scan on kcdr_sr_desc  (cost=0.00..709235.56 rows=1306585 width=300)
               Filter: ((user_id = 396747) AND (start_time > '2016-01-01 00:00:00'::timestamp without time zone) AND (start_time < '2016-07-01 00:00:00'::timestamp without time zone))
  

• The query with order_by DESC

   explain select * from kcdr_sr_desc where user_id=396747 and start_time > '2016-01-01' and start_time < '2016-07-01' order by start_time desc limit 20
  

  Result:

    XN Limit  (cost=1000000841967.42..1000000841967.47 rows=20 width=300)
    ->  XN Merge  (cost=1000000841967.42..1000000845233.88 rows=1306585 width=300)
    Merge Key: start_time
       ->  XN Network  (cost=1000000841967.42..1000000845233.88 rows=1306585 width=300)
         Send to leader
         ->  XN Sort  (cost=1000000841967.42..1000000845233.88 rows=1306585 width=300)
               Sort Key: start_time
               ->  XN Seq Scan on kcdr_sr_desc  (cost=0.00..709235.56 rows=1306585 width=300)
                     Filter: ((user_id = 396747) AND (start_time > '2016-01-01 00:00:00'::timestamp without time zone) AND (start_time < '2016-07-01 00:00:00'::timestamp without time zone))
  

Answer 1

The SORTKEY on an Amazon Redshift table is used to improve efficiency of queries via the use of Zone Maps. It is not intended to sort data to match queries.

Amazon Redshift stores data on disk in 1MB blocks. Each block contains data relating to one column of one table, and data from that column can occupy multiple blocks. Blocks can be compressed, so they will typically contain more than 1MB of data.

Each block on disk has an associated Zone Map that identifies the minimum and maximum value in that block for the column being stored. This enables Redshift to skip over blocks that do not contain relevant data. For example, if the SORTKEY is a timestamp and a query has a WHERE clause that limits data to a specific day, then Redshift can skip over any blocks where the desired date is not within that block.

Once Redshift locates the blocks with desired data, it will read those blocks to execute the query.

In looking at your EXPLAIN plans, the second example clearly has an extra SORT step. It would appear that the query optimizer knows that you are sorting on a column that matches the SORTKEY, so that the sort is skipped in the first example. This is common where data is appended to tables, resulting in more recent data appearing at the end of columns.

Some options for you:

• If you are always doing a fresh load of data, you could add a new column that is the reverse of the date (eg an interval from the year 3000 minus the stored date). Use this as the SORTKEY and the data will be stored in reverse. The Zone Maps for the real date column will be conveniently reverse-sorted too.

• If you are continually loading new data, the latest data will be appended to the end of the columns, so you can't easily keep data reverse sorted anyway.

• Use a smaller date range. The above query is restricting results to a range of six months, returning 1,306,585 rows. The query then sorts it an limits the result to the most recent 20. If you reduce the date range (eg to just one day), the less data will be retrieved, the sort will run faster and the query will be quicker. Given that many rows, there is an average of 7000+ records per day, so that should be more than sufficient for LIMIT 20.

• Do not use SELECT * -- it causes more blocks to be read from disk (since each column is stored in a separate block). By querying only the columns actually required, there will be less disk access and the query will run faster.