Useful PostgreSQL Queries

Finding the largest tables

SELECT nspname || '.' || relname AS "relation",
   pg_size_pretty(pg_relation_size(C.oid)) AS "size"
 FROM pg_class C
 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 WHERE nspname NOT IN ('pg_catalog', 'information_schema')
 ORDER BY pg_relation_size(C.oid) DESC
 LIMIT 20;

Empty tables

SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class  C 
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
and relname like 'netflow_____%' 
and pg_relation_size(C.oid)=0
ORDER BY pg_relation_size(C.oid) asc;

8.4 fixes

CREATE FUNCTION modulus(
   dividend numeric,
   divisor numeric)
  RETURNS numeric AS
' DECLARE result numeric; temp numeric; BEGIN   temp := ABS(divisor);result  := MOD(dividend, temp);IF result < 0 THEN    result := result + temp;  END IF;  RETURN result;END;'
 LANGUAGE plpgsql VOLATILE
 COST 100;
ALTER FUNCTION modulus(numeric, numeric)
 OWNER TO myipfix;
CREATE FUNCTION modulus(
   dividend numeric,
   divisor integer)
 RETURNS numeric AS
' DECLARE result numeric; temp numeric; BEGIN   temp := ABS(divisor);result  := MOD(dividend, temp);IF result < 0 THEN    result := result + temp;  END IF;  RETURN result;END;'
 LANGUAGE plpgsql VOLATILE
 COST 100;
ALTER FUNCTION modulus(numeric, numeric)
 OWNER TO myipfix;

Foogun to remove tables en-mas e.g. select foogun('public','flowsec_151'); will remove all tables starting with flowsec_151. Select netflow\_0\_5\_%\_150%\_ whill delete all netflow in the 150 time period

CREATE OR REPLACE FUNCTION foogun(IN _schema TEXT, IN _parttionbase TEXT) 
RETURNS void 
LANGUAGE plpgsql
AS
$$
DECLARE
    row     record;
BEGIN
    FOR row IN 
        SELECT
            table_schema,
            table_name
         FROM
            information_schema.tables
        WHERE
           table_type = 'BASE TABLE'
         AND
            table_schema = _schema
        AND
            table_name ILIKE (_parttionbase || '%')
    LOOP
        EXECUTE 'DROP TABLE ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
        RAISE INFO 'Dropped table: %', quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
    END LOOP;
END;
$$;

Delete old indexes

CREATE OR REPLACE FUNCTION foogunindex( IN _parttionbase TEXT) 
RETURNS void 
LANGUAGE plpgsql
AS
$$
DECLARE
    row     record;
BEGIN
    FOR row IN 
        SELECT indexname from pg_indexes WHERE indexname ILIKE ( _parttionbase||'%' ) and tablename like 'netflow_0_%'
    LOOP
        EXECUTE 'DROP INDEX ' || quote_ident(row.indexname );
        RAISE INFO 'Dropped INDEX %', quote_ident(row.indexname );
    END LOOP;
END;
$$;
select foogunindex('appid_0_%_%_86400000');
select foogunindex('srcport_0_%_%_86400000');
select foogunindex('dstport_0_%_%_86400000');
select foogunindex('srcport_0_%_%_86400000');

Find indexes

select ||a.aname|| from (SELECT n.nspname as "Schema",
 c.relname as "aname",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' 
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
  u.usename as "Owner",
 c2.relname as "Table"
FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
     JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('i',)
       AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
      AND pg_catalog.pg_table_is_visible(c.oid)
   and c.relname like 'appid_0_5_%'
 ORDER BY 1,2)as a;

Check table inheritance

SELECT pg_inherits.*, c.relname AS child, p.relname AS parent FROM pg_inherits JOIN pg_class AS c ON (inhrelid=c.oid) JOIN pg_class as p ON (inhparent=p.oid) where p.relname like 'fdw%';

Count number of user events in last 7 days

select userid,count(*) as eventCount, source as sourceips from userevents where firstseen > gettimeimmutable('7 days')  group by userid,sourceips

Count number of user logins in last 7 days

select userid,count(*) as eventCount, source as sourceips from userevents where firstseen > gettimeimmutable('7 days') and params like '%action\":[\"login\"]%' group by userid,sourceips

Find all uplinks

select ip,display,sysname,anifindex,name,alias,maccount,descr from (select *  from 
(select devices.ip,devices.display,devices.sysname,cams.device as deviceid,cams.ifindex as anifindex,count(distinct (mac)) as maccount from cams 
full outer join devices on devices.device=cams.device 
where firstseen>gettimeimmutable('10 day')  
group by cams.device,cams.ifindex,devices.ip,devices.display,devices.sysname order by maccount desc) as a
full outer join  deviceinterfaces on deviceinterfaces.device=a.deviceid and a.anifindex=deviceinterfaces.ifindex
)as fulllist where maccount >1 order by maccount desc