CODEWITHSUNDEEP
amazon-redshift
Harsha Sridhara
Harsha Sridhara

Reputation: 3

How to fetch tables that are operated by a given query in redshift

Is there a way to fetch the tables that are operated by a given query? For example, the below query operates on table 'abc':

select * from abc

After a query is executed successfully, can we fetch the tables that the query actually operated on in redshift?

Upvotes: 0

Views: 274

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11032

Harsha - yes and in a number of ways. The most straight forward is to query the stl_scan system table which lists all table scans and the query number that generated the scan. The question for you is how do you want to identify the query you just ran? By text? By current session id? Stl_scan will have lots of data in it for a busy cluster so you want to find only those rows you care about. If current session you can use "where pid = (SELECT pg_backend_pid())" to get the query run by the current session but pid isn't in stl_scan so you will need to join with stl_query which has both pid and query number. You will also want to have a "where starttime > getdate() - interval '1 hour'" in your query so you aren't looking through all of history for information about a query you just ran.

Upvotes: 1

Related Questions