CODEWITHSUNDEEP
amazon-web-servicesamazon-redshift
saurabh bansal
saurabh bansal

Reputation: 23

How to tell what zone map (blocks) read by amazon redshift query

Is there any way to tell if the zone map is used by a specific query. Is there a way to list block query read

My query is taking more time than expected, I just want to make the sure the query is using zone map to filter out blocks.

Upvotes: 1

Views: 593

Answers (1)

Joe Harris
Joe Harris

Reputation: 14035

The table stl_scan contains this information.

  • is_rrscan indicates if the scan used range restriction (zone maps).
  • rows_pre_user_filter is the row count before zone map restrictions
  • rows_pre_filter is the row count after zone map restrictions
  • rows is the row count after all predicates were evaluated
SELECT query, segment 
     , tbl, perm_table_name
     , is_rrscan
     , SUM( rows_pre_user_filter ) rows_on_table
     , SUM( rows_pre_filter )      rows_scanned
     , SUM( rows )                 rows_returned
FROM stl_scan
WHERE query = 999999
GROUP BY 1,2,3,4,5
ORDER BY 1,2,3,4,5

Upvotes: 2

Related Questions