PG Commands

Braindump.sql

---To find database size, works with PG 12---------------------
SELECT pg_size_pretty( pg_database_size('QA') );
--------------------------------------------------------------------

-------------  To find out top X size of tables, works with PG12 ----------------------

SELECT
   nspname, relname AS "relation",
    pg_size_pretty (
        pg_total_relation_size (C .oid)
    ) AS "total_size",
	reltuples AS approximate_row_count
FROM
    pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)
WHERE
    nspname NOT IN (
        'pg_catalog',
        'information_schema'
    )
AND C .relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY
    pg_total_relation_size (C .oid) DESC
LIMIT 5;
-------------------------------------------------------------------------

------------------Killing held queries, works with PG v12 --------------------

SELECT pg_cancel_backend(PID); --Gracefully
SELECT pg_terminate_backend(PID); --Terminate
------------------------------------------------------------

----Find out all locks acquired by the query with time, works with PG V12 ------

SELECT a.datname,
         l.relation::regclass,
         l.transactionid,
         l.mode,
         l.GRANTED,
         a.usename,
         a.query,
         a.query_start,
         age(now(), a.query_start) AS "age",
         a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
ORDER BY a.query_start;

-------------------------------------------------------------------------------

-----------------------SHOW Config file locations----------------------
show config_file


-----------------------Generate Random String---------------------------
Create or replace function random_string(length integer) returns text as
$$
declare
  chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
  result text := '';
  i integer := 0;
begin
  if length < 0 then
    raise exception 'Given length cannot be less than 0';
  end if;
  for i in 1..length loop
    result := result || chars[1+random()*(array_length(chars, 1)-1)];
  end loop;
  return result;
end;
$$ language plpgsql;
					  
select random_string(15) from generate_series(1,15);  
					  
					  
------------------------------- TO FIND TAble space---
select t.schemaname, t.tablename, 
       coalesce(t.tablespace, ts.default_tablespace) as tablespace
from pg_tables t
  cross join (
     select ts.spcname as default_tablespace
     from pg_database d
       join pg_tablespace ts on ts.oid = d.dattablespace
      where d.datname = current_database()
   ) ts
where tablename = 'a'
  and schemaname = 'public';
					  
					  
-------- IO Times------------
SET max_parallel_workers_per_gather = 0;
SET track_io_timing = on
					  
-------Timestamp UTC---
(NOW() AT TIME ZONE 'UTC');
					  
-----Get the current trnsaction level----
SELECT current_setting('transaction_isolation');
RAISE NOTICE '% %',current_setting('transaction_isolation'),txid_current();
					  
					  
---Serialize any table to JSONB[]
SELECT jsonb_agg(jsonpacket)
FROM(
SELECT *
FROM "MessagesToAck"
)as jsonpacket INTO "Result";
					  
--Deserialize from json to record set
SELECT "C" AS "CursorId","T" AS "Token"
	FROM jsonb_to_recordset("MessagesToAckSerialized")
	AS ("C" Bigint, "T" integer)
					  
					  
----Capture returning elements from update nd delete nd insert
with inserted as (
  INSERT INTO table1 (value1,value2) 
  SELECT value3,value4 
  FROM table2 
  RETURNING id
) 
insert into temp
select id 
from inserted;
					  
---Size of columns
Select
    pg_size_pretty(sum(pg_column_size(column_name))) as total_size,
    pg_size_pretty(avg(pg_column_size(column_name))) as average_size,
    sum(pg_column_size(column_name)) * 100.0 / pg_total_relation_size('table_name') as percentage
from table_name;
											 
--Schema path
SET search_path TO "Q";		
											 
--Dynamic Table 
SELECT *
FROM (
Values('A',1),('r',3)
) as t ("C1","C2")
		    
---Trend Analysis
SELECT *,
"Slope"*"XStartPoint"+"Intercept" AS "StartPoint", --y = mx + b
"Slope"*("XStartPoint"*3)+"Intercept"	AS "Endpoint" --y = m(x*3) + b
FROM
(
	SELECT "Message", 
	REGR_SLOPE(TRUNC(EXTRACT(EPOCH FROM "Elapsed")*1000),TRUNC(EXTRACT(EPOCH FROM "Timestamp")*1000)) AS "Slope",
	REGR_INTERCEPT(TRUNC(EXTRACT(EPOCH FROM "Elapsed")*1000),TRUNC(EXTRACT(EPOCH FROM "Timestamp")*1000)) AS "Intercept",
	MIN(TRUNC(EXTRACT(EPOCH FROM "Timestamp")*1000)) AS "XStartPoint",
	AVG(TRUNC(EXTRACT(EPOCH FROM "Elapsed")*1000)) AS "Average",
	MAX(TRUNC(EXTRACT(EPOCH FROM "Elapsed")*1000)) AS "MAX",
	Count("Elapsed") AS "Number OF Batches"
	FROM "Anukram"."Logs"
	GROUP BY "Message"
) "TrendAnalysis"
--WHERE "Slope">0
ORDER BY "Average" DESC
--ORDER BY "Slope" DESC
	    
-- To find live and dead tuples 
SELECT 
	relname AS ObjectName
	,pg_stat_get_live_tuples(c.oid) AS LiveTuples
	,pg_stat_get_dead_tuples(c.oid) AS DeadTuples
FROM pg_class c
 WHERE relnamespace IN (
SELECT oid FROM pg_namespace
WHERE nspname IN ('Anukram','Data','Warehouse')
 )
ORDER BY LiveTuples DESC
	    
--TO find summary of block reads vs in memory hits
SELECT * 
FROM pg_statio_user_tables
WHERE schemaname IN ('Anukram','Data','Warehouse')
ORDER BY heap_blks_read DESC
	    
	    
-- To find how many seq scan vs index scans 
SELECT * 
FROM pg_stat_user_tables
WHERE schemaname IN ('Anukram','Data','Warehouse')
ORDER BY seq_scan DESC
	    
-- To set seq scan off
SET enable_seqscan = OFF;
	    
-- Find all tables and when they were last vacuumed/analyzed, either manually or automatically
SELECT relname, 
       last_vacuum, 
       last_autovacuum, 
       last_analyze, 
       last_autoanalyze 
FROM   pg_stat_all_tables 
WHERE  schemaname = 'Data' 
ORDER  BY last_vacuum DESC;