Three faster way to speed up Postgre count all query

on

  • Query with unique (ex: id) index field 
    • SELECT COUNT(id) FROM my_table
  • Query pre process table using trigger AFTER INSERT OR DELETE
    • CREATE TABLE tcounter(id serial primary key,table_schema text, table_name text, count serial);
    • INSERT INTO tcounter(table_schema, table_name,count) SELECT 'my_schema', 'my_table', count(*) FROM my_schema.my_table;
    • CREATE OR REPLACE FUNCTION ex_count()
      
RETURNS       trigger AS
$BODY$
BEGIN
    IF (TG_OP='INSERT') THEN
      UPDATE tcounter set count = count + 1 where table_schema = TG_TABLE_SCHEMA::TEXT and table_name = TG_TABLE_NAME::TEXT;
    ELSIF  (TG_OP='DELETE') THEN
      UPDATE tcounter set count = count - 1 where table_schema = TG_TABLE_SCHEMA::TEXT and table_name = TG_TABLE_NAME::TEXT;
    END IF;
RETURN NEW;
END$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE TRIGGER tg_counter  AFTER INSERT OR DELETE
  ON my_schema.my_table  FOR EACH ROW  EXECUTE PROCEDURE ex_count();
  • Query estimated 
    • select reltuples from pg_class where relname='tablename';