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)

reference: Row and Array Comparisons