Redshift XN Sort cost

Question

I have a query which is performing very poorly.

It's too big to post it here but basically it selects several columns and checks to which range they belong e.g.

CASE WHEN col < 3 THEN 'A' WHEN col BETWEEN 3 AND 5 THEN 'B' etc.

And then it counts 'A', 'B' etc

The execution time for 380M rows is 15 min.

It has no DISTINCT and no joins except of join to a small dimension table.

When I run explain it shows the following query plan

+-----------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                      |
|-----------------------------------------------------------------------------------------------------------------|
| XN Merge  (cost=1000011702317.03..1000011702317.16 rows=53 width=48)                                            |
|   Merge Key: programme_session.start_date                                                                       |
|   ->  XN Network  (cost=1000011702317.03..1000011702317.16 rows=53 width=48)                                    |
|         Send to leader                                                                                          |
|         ->  XN Sort  (cost=1000011702317.03..1000011702317.16 rows=53 width=48)                                 |
|               Sort Key: programme_session.start_date                                                            |
|               ->  XN HashAggregate  (cost=11702160.75..11702315.51 rows=53 width=48)                            |
|                     ->  XN Hash Join DS_DIST_ALL_NONE  (cost=105.08..11499929.95 rows=2186279 width=48)         |
|                           Hash Cond: ("outer".start_date = "inner".tk)                                          |
|                           ->  XN Seq Scan on programme_session  (cost=0.00..5101316.48 rows=510131648 width=48) |
|                           ->  XN Hash  (cost=105.00..105.00 rows=30 width=4)                                    |
|                                 ->  XN Seq Scan on dim_date dd  (cost=0.00..105.00 rows=30 width=4)             |
|                                       Filter: (("year" = 2019) AND ("month" = 6))                               |
+-----------------------------------------------------------------------------------------------------------------+

Here I see the explosion of cost from XN HashAggregate to XN Sort.

What could be the reason?

The table is sorted by start_date, I run VACUUM SORT ONLY and skew values are not very big:

+-------------------+-----------+----------------+------------+-----------------+-------------+
| table             | encoded   | diststyle      | sortkey1   | skew_sortkey1   | skew_rows   |
|-------------------+-----------+----------------+------------+-----------------+-------------|
| programme_session | Y         | KEY(device_id) | start_date | 2.26            | 1.00        |
+-------------------+-----------+----------------+------------+-----------------+-------------+

How can I improve the performance of my query?

The big cost seen to be XN Seq Scan on programme_session. Is the query using a WHERE clause on start_date for that table to take advantage of the SORTKEY? It's hard to suggest improvements without seeing the table DDL and the query. — John Rotenstein
@John Rotenstein Yes, the query contains WHERE start_date BETWEEN ..... To solve my problem I removed the join to a dimension table and now it's working fine. Which is strange because the dimension table is really small: 7000 records with 20 mostly numerical columns, DISTSTYLE ALL. They recommend using joins with dimension tables but in fact the performance can be very poor. — Oleg Pavliv
That is indeed strange. Using DISTSTYLE ALL should make that run quite efficiently. Perhaps the format of the join fields are not identical (eg timestamp vs text date), which caused extra work? Or, the query might be applying the WHERE on the dimension table, which prohibits it efficiently using the SORTKEY on the main table. If so, try moving the WHERE onto the date fields in the main table (main.start_date), even if using the dimension table for other fields. — John Rotenstein
@John Rotenstein Yes, exactly. I also came to the same conclusion. Thank you for confirming that. — Oleg Pavliv

Dan Anderson Dan Anderson · Accepted Answer · 2021-02-19T02:11:20

Check the data types for the columns in your join clause. I've seen redshift broadcast the transaction table when they are not identical.

Redshift XN Sort cost

1 Answers