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:
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 | 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)