Faster PostgreSQL COUNT function for large result sets by using query planner estimates.

count_estimate.sql

CREATE FUNCTION count_estimate(query text) RETURNS integer AS $$
DECLARE
  rec   record;
  rows  integer;
  row  integer;
BEGIN
  rows := 1;
  FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP
    row := substring(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)')::integer;
    IF (row != 0) THEN rows := rows * row; END IF;
  END LOOP;
  RETURN rows;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT;