CODEWITHSUNDEEP
performanceamazon-redshift
Abrham Smith
Abrham Smith

Reputation: 163

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

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.

Upvotes: 6

Views: 7050

Answers (1)

Joe Harris
Joe Harris

Reputation: 14035

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

Upvotes: 6

Related Questions