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_tup
FROM
pg_stat_user_tables
ORDER 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:

result using psql

when we visualize it through pgadmin, it makes the break, as it should be:

result using pgadmin

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 | 12025550141
Time: 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 | 12025550141
Time: 3712,223 ms (00:03,712)

reference: Row and Array Comparisons