Redshift Queries

RedshiftQueries.sql

-- find # rows in each table
select "table", tbl_rows
from svv_table_info
where SCHEMA = 'sh'
order by 1;


-- view history of queries
select query, datediff(seconds, starttime, endtime), trim(querytxt) as sqlquery
from stl_query
order by query desc limit 20;


--view definition of a table
select trim(tablename) as table, "column", trim(type) as type, encoding, distkey, sortkey, "notnull" 
from pg_table_def 
where tablename like 'sales%';

-- get analyze output
select distinct a.xid, trim(t.name) as name, a.status, a.rows, a.modified_rows, a.starttime, a.endtime
from stl_analyze a 
join stv_tbl_perm t  on t.id=a.table_id
where name = 'users'
order by starttime;

-- find tables that might need reindexing
select tbl as tbl_id, stv_tbl_perm.name as table_name, 
col, interleaved_skew, last_reindex
from svv_interleaved_columns, stv_tbl_perm
where svv_interleaved_columns.tbl = stv_tbl_perm.id
and interleaved_skew is not null;



-- find skew in a table
SELECT diststyle, skew_rows
FROM svv_table_info
where "table" = 'table_name'

-- view query performance
Select * from SVL_S3QUERY_SUMMARY 
where query=<Query-ID>;

-- view errors from COPY
select * 
from stl_load_errors le
where le.query = pg_last_copy_id();

-- view external schemas
select * from svv_external_schemas;

-- view WLM queues
create view WLM_QUEUE_STATE_VW as
select (config.service_class-5) as queue
, trim (class.condition) as description
, config.num_query_tasks as slots
, config.query_working_mem as mem
, config.max_execution_time as max_time
, config.user_group_wild_card as "user_*"
, config.query_group_wild_card as "query_*"
, state.num_queued_queries queued
, state.num_executing_queries executing
, state.num_executed_queries executed
from
STV_WLM_CLASSIFICATION_CONFIG class,
STV_WLM_SERVICE_CLASS_CONFIG config,
STV_WLM_SERVICE_CLASS_STATE state
where
class.action_service_class = config.service_class 
and class.action_service_class = state.service_class 
and config.service_class > 4
order by config.service_class;

-- WLM query state
create view WLM_QUERY_STATE_VW as
select query, (service_class-5) as queue, slot_count, trim(wlm_start_time) as start_time, trim(state) as state, trim(queue_time) as queue_time, trim(exec_time) as exec_time
from stv_wlm_query_state;