SQL
🕦

SQL

 
SQL
WITH abc AS( select
             FROM ...)
, XYZ AS(select
         From abc ....) /*This one uses "abc" multiple times*/
  Select 
  From XYZ....   /*using abc, XYZ multiple times*/
 
Timing
You can have psql run a timer for your queries by setting
SQL
\timing
 
Index Cache Hit Rate
SQL
SELECT  sum(idx_blks_read) as idx_read,   sum(idx_blks_hit)  as idx_hit,   
(sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio 
FROM pg_statio_user_indexes;
 

LOGGING

Log slow queries by setting log_min_duration_statement
SQL
ALTER database postgres SET log_min_duration_statement='250ms';
 
Control which statement types get logged
Control the types of statements that are logged for your database.
SQL
ALTER DATABASE postgres SET log_statement = 'all'
Valid values include all, ddl, none, mod
 
Log when waiting on a lock
Log when database is waiting on a lock.
SQL
ALTER DATABASE postgres SET log_lock_waits = 'on'

PERFORMANCE

Use statement timeouts to control runaway queries
Setting a statement timeout prevents queries from running longer than the specified time. You can set a statement timeout on the database, user, or session level. We recommend you set a global timeout on the database and then override that one specific users or sessions that need a longer allowed time to run.
SQL
ALTER DATABASE mydatabase SET statement_timeout = '60s';
 
Use pg_stat_statements to find the queries and processes that use the most resources
SQL
SELECT
	total_exec_time,
	mean_exec_time as avg_ms,
	calls,
	query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
Monitor connections in Postgres
This query will provide the number of connection based on type.
SQL
SELECT count(*),
	   state
FROM pg_stat_activity
GROUP BY state;
If you see idle is above 20 it's recommended to explore using PgBouncer.
 
Query size of specific table
Will give you the size of the specific relation you pass in.
SQL
SELECT pg_relation_size('table_name');

# For prettier formatting you can wrap with:
SELECT pg_size_pretty(pg_relation_size('table_name'));
Query all relation sizes
Will report on all table sizes in descending order
SQL
SELECT relname AS relation,
       pg_size_pretty (
         pg_total_relation_size (C .oid)
       ) AS total_size
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
Check for unused indexes
Will return the unused indexes in descending order of size. Keep in mind you want to also check replicas before dropping indexes.
SQL
SELECT schemaname || '.' || relname AS table,
       indexrelname AS index,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS "index size",
       idx_scan as "index scans"
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique
  AND idx_scan < 50
  AND pg_relation_size(relid) > 5 * 8192
ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
pg_relation_size(i.indexrelid) DESC;
 
Get approximate counts for a table
Will return the approximate count for a table based on PostgreSQL internal statistics. Useful for large tables where performing a `SELECT count(*)` is costly on performance.
SQL
SELECT reltuples::numeric as count
FROM pg_class
WHERE relname='table_name';
Non-blocking index creation
Adding `CONCURRENTLY` during index creation, while not permitted in a transaction, will not hold a lock on the table while creating your index.
SQL
CREATE INDEX CONCURRENTLY foobar ON foo (bar)

PSQL

Automatically log query time in PSQL
Will automatically print the time it took to run a query from within psql.
*Of note this is the round trip time not simply query execution time.*
SQL
\timing
You can save this in your .psqlrc to be a default setting
 
Autoformat query results in psql
Will automatically reorganize the query output based on your terminal window for better readability.
SQL
\x auto
You can save this in your .psqlrc to be a default setting
 
Edit your psql queries in editor of your choice
Will automatically open your last run query in your default $EDITOR. When you save and close it will execute that query.
SQL
\e
Set a value for nulls
Will render the nulls as whatever character you specify. Handy for easier parsing of nulls vs. blank text.
SQL

\pset null 👻
You can save this in your .psqlrc to be a default setting
Save your query history per database locally
Will automatically save a history file for each **DBNAME**.
SQL
\set HISTFILE ~/.psql_history- :DBNAME
You can save this in your .psqlrc to be a default setting
Clear your psql screen
Will clear your screen in current psql session
SQL
\! clear
 
Continually run a query with watch
Will automatically run the last query ever 2 seconds and display the output. You can also specify the query that will run after watch as well.
SQL
\watch
 
Rollback to previous statement on error when in interactive mode
When you encounter an error when in interactive mode this will automatically rollback to just before the previous command, allowing you to continue working as you would expect.
SQL
\set ON_ERROR_ROLLBACK interactive
 
Export a CSV from directly in psql
Will run the specific query and return live a SQL for you
SQL
psql --csv -c 'select * from test;'
Run a query from a file in psql
Will execute the specified file when inside psql.
SQL
\i filename'
Provide clean border within Psql
Will give you a border around the output of your queries when in psql
SQL
\pset border 2
You can save this in your .psqlrc to be a default setting
Set linestyle to unicode
Changes the linestyle to unicode, which when combined with above tip leads to much cleaner formatting
SQL
\pset linestyle unicode
You can save this in your .psqlrc to be a default setting

SQL

Replace nulls with other value
Coalesce will use the value and if the value is null display your specified string.
JavaScript
SELECT id,
       coalesce(ip, 'no IP') // or use IfNull
FROM logs;
You can supply two columns as well prior to your replacement value and the function will use first not null value.
Generate data with generate_series
Generates values from the start to the end values supplied based on the interval. Values can be numbers or timestamps. Can be used in a FROM or JOIN clause or CTE. Commonly used when building charts and reports that require all dates to be filled.
SQL
SELECT * FROM generate_series(now() - '3 month'::interval, now(), '1 day');
Round dates with date_trunc
Will truncate the date to the specified level of precision. Some example precision levels include: month, week, day, hour, minute.
SQL
SELECT date_trunc('day', now());
Perform time math with intervals
You can add or subtract specific amounts of time of a timestamp by casting the value you want as an interval.
SQL
SELECT now() - '1 month'::interval;
 
Explain whether index has been used
JavaScript
explain SELECT * FROM student;
 

UTILITY

Create read only user
Once already creating a specific user role, you can user the pg_read_all_data to grant read only access to all tables.
SQL
GRANT pg_read_all_data TO username;
 
LIMIT
Return the results
SQL
LIMIT 1,1 // second and only one record
Function
SQL
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N = N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT Salary FROM Employee
      ORDER BY Salary DESC LIMIT N,1
  );
END
 

SQL efficiency

notion image
 

Ref