Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

ANALYZE Command History

It is useful to know when the last ANALYZE command was run on a table or database. When an ANALYZE command is run, Amazon Redshift executes multiple queries that look like this:

Copy
padb_fetch_sample: select * from table_name

To find out when ANALYZE commands were run, you can query system tables and views such as STL_QUERY and SVL_STATEMENTTEXT and include a restriction on padb_fetch_sample. For example, to find out when the SALES table was last analyzed, run this query:

Copy
select query, rtrim(querytxt), starttime from stl_query where querytxt like 'padb_fetch_sample%' and querytxt like '%sales%' order by query desc; query | rtrim | starttime ------+------------------------------------------------+---------------------- 81 | padb_fetch_sample: select * from sales | 2012-04-18 12:... 80 | padb_fetch_sample: select * from sales | 2012-04-18 12:... 79 | padb_fetch_sample: select count(*) from sales | 2012-04-18 12:... (3 rows)

Alternatively, you can run a more complex query that returns all the statements that ran in every completed transaction that included an ANALYZE command:

Copy
select xid, to_char(starttime, 'HH24:MM:SS.MS') as starttime, date_diff('sec',starttime,endtime ) as secs, substring(text, 1, 40) from svl_statementtext where sequence = 0 and xid in (select xid from svl_statementtext s where s.text like 'padb_fetch_sample%' ) order by xid desc, starttime; xid | starttime | secs | substring -----+--------------+------+------------------------------------------ 1338 | 12:04:28.511 | 4 | Analyze date 1338 | 12:04:28.511 | 1 | padb_fetch_sample: select count(*) from 1338 | 12:04:29.443 | 2 | padb_fetch_sample: select * from date 1338 | 12:04:31.456 | 1 | padb_fetch_sample: select * from date 1337 | 12:04:24.388 | 1 | padb_fetch_sample: select count(*) from 1337 | 12:04:24.388 | 4 | Analyze sales 1337 | 12:04:25.322 | 2 | padb_fetch_sample: select * from sales 1337 | 12:04:27.363 | 1 | padb_fetch_sample: select * from sales ...