useful postgresql queries
list all tables, order by record count
-- This query uses the PostgreSQL statistics to count.-- To more precise numbers, run-- VACUUM ANALYZE-- before the query.SELECT relname, n_live_tupFROM pg_stat_user_tablesORDER BY n_live_tup DESC;
show all database indexes
this query generates create index
commands for all database indexes.
select 'CREATE INDEX '|| index_name || ' ON ' || table_name || ' ( ' || colunas || ' );' from (select table_name, index_name, array_to_string ( ARRAY ( select a.attname as column_name from pg_class t, pg_class i, pg_index ix, pg_attribute a where t.oid = ix.indrelid and i.oid = ix.indexrelid and a.attrelid = t.oid and a.attnum = ANY(ix.indkey) and t.relkind = 'r' and i.relname not like 'fk_%' and i.relname not like 'pk_%' and i.relname not like '%_pkey' and t.relname = base.table_name and i.relname = base.index_name order by t.relname, i.relname, a.attname ), ', ') AS colunas from (select distinct t.relname as table_name, i.relname as index_name from pg_class t, pg_class i, pg_index ix, pg_attribute a where t.oid = ix.indrelid and i.oid = ix.indexrelid and a.attrelid = t.oid and a.attnum = ANY( ix.indkey ) and t.relkind = 'r' and i.relname not like 'fk_%' and i.relname not like 'pk_%' and i.relname not like '%_pkey' order by t.relname, i.relname ) as base) as base2;
add line break
SELECT 'First Line' || Chr(13) ||'Second Line';
CHAR(13)
is the same as CR
. To follow the CRLF
line break on Windows,
you need CHAR(13)+CHAR(10)
.
what if i'm not seeing the line break?
when viewing using psql
, it shows the \r
, which is the representation of the
line break character:
when we visualize it through pgadmin, it makes the break, as it should be:
but what about my app?
if you use this tip in an application developed by you, you will need to handle
the query result, and display the line break characters as as you wish. for
example, if you are developing a web app, you can, in visualization layer,
replace line breaks with <br/>
.
disk space
relation size, only data
SELECT pg_size_pretty(pg_relation_size('actor'));
relation size, with indexes
SELECT pg_size_pretty(pg_total_relation_size('actor'));
20 biggest tables
SELECT nspname || '.' || 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 LIMIT 20;
use any(array)
instead of IN
using regex: 8 seconds on ~3.5MM records
mydatabase=> SELECT my_column FROM my_table WHERE my_column ~* '^12025550188|^12025550103|^12025550193|^12025550143|^12025550141';-[ RECORD 1 ]--------------my_column | 12025550188-[ RECORD 2 ]--------------my_column | 12025550103-[ RECORD 3 ]--------------my_column | 12025550193-[ RECORD 4 ]--------------my_column | 12025550143-[ RECORD 5 ]--------------my_column | 12025550141Time: 8060,375 ms (00:08,060)
using any: 3 seconds on ~3.5MM records
mydatabase=> SELECT my_column FROM my_table WHERE my_column LIKE ANY(array['12025550188%','12025550103%','12025550193%','12025550143%','12025550141%']);-[ RECORD 1 ]--------------my_column | 12025550188-[ RECORD 2 ]--------------my_column | 12025550103-[ RECORD 3 ]--------------my_column | 12025550193-[ RECORD 4 ]--------------my_column | 12025550143-[ RECORD 5 ]--------------my_column | 12025550141Time: 3712,223 ms (00:03,712)
reference: Row and Array Comparisons