Redshift PSQL Commands


List users



List namespaces (or schema)



List tables



Describe table TABLENAME



Change your current schema to the SCHEMA

set search_path=’SCHEMA’;  


Show last 10 load errors in Redshift

select * from stl_load_errors order by starttime desc limit 10;


Show all queries from June 16th, 2009

select starttime, type, rtrim(text) from svl_statpreenttext 
where starttime like '2009-06-16%' and type='DDL' order by starttime asc;



Show all running queries

select pid, user_name, starttime, query 
from stv_recents
where status='Running';


View list of recent queries

select query, pid, elapsed, substring from svl_qlog 
where userid = 100
order by starttime desc
limit 5;


View list of all current sessions on Redshift

select * 
from stv_sessions;


Show extended column information for table ‘sometable’ (ie. displays distkey/sortkey)

select * from pg_table_def where tablename=’sometable’; 


Show free space of the Redshift cluster

select sum(capacity)/1024 as capacity_gbytes, 
sum(used)/1024 as used_gbytes,
(sum(capacity) - sum(used))/1024 as free_gbytes
from stv_partitions where part_begin=0;


Show # of rows and diskspace order by tablename

select trim(pgdb.datname) as Database, trim(pgn.nspname) as Schema, 
trim( as Table, b.mbytes, a.rows
from ( select db_id, id, name, sum(rows) as rows
from stv_tbl_perm a group by db_id, id, name ) as a
join pg_class as pgc on pgc.oid =
join pg_namespace as pgn on pgn.oid = pgc.relnamespace join pg_database as pgdb on pgdb.oid = a.db_id
join (select tbl, count(*) as mbytes
from stv_blocklist group by tbl) b on
order by mbytes desc, a.db_id,;


List 100 queries that have very slow query execution times in the past 24 hours

select row_number() over(order by total_exec_time desc) as rownum, ql.query, ql.substring as querytxt, 
  wq.total_exec_time / 1000000 as exec_time_sec, queue_start_time
from stl_wlm_query wq
left join svl_qlog ql on ql.query = wq.query
where queue_start_time >= sysdate - interval '24 hours'
order by total_exec_time desc limit 100;