Why does Redshift always do a seq scan, even with a sort key?

user_7416647 0
Viewed 4582

I have what seems to be a pretty simple scenario but for some reason my query always does a seq scan and doesn't use the sortkey defined for the table.

SELECT *
FROM table1
WHERE md_event_time between '2017-07-23 00:00:00' AND '2017-07-27 23:59:00'

The field in the where clause is showing up as the sortkey in the table info. Explain:

XN Seq Scan on table1 (cost=0.00..0.00 rows=1 width=20) 
Filter: ((md_event_time <= '2017-07-27 23:59:00'::timestamp without time zone) AND 
(md_event_time >= '2017-07-23 00:00:00'::timestamp without time zone))

Any help is appreciated.

amazon-redshift performance
1 Answers

You can't see the effect of the sort key in the EXPLAIN plan. Run the query and then look for it in the SVL_QUERY_SUMMARY view, specifically look for the column is_rrscan equal to t on the row that shows a scan of the table. This indicates that Redshift was able to reduce the scan by using the sort key range.

If there is a large difference between the rows_pre_filter and rows counts then make sure the table is VACUUMed (to removed deleted rows) and then consider removing compression from the first sort key only by setting it to ENCODE raw (too much compression on the sort key forces the DB to materialize too many rows).

http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_QUERY_SUMMARY.html

user_1394900
Related