तेज़ POSTGRESQL COUNT (*)

गिनती (*) इतनी धीमी क्यों है?

SELECT count(*)
FROM /*   */;

SELECT count(*) FROM large_table;

क्या "*" गिनती में है (*) एक समस्या?

सूचकांक का उपयोग केवल स्कैन

धुरी तालिका का उपयोग करना

START TRANSACTION;
 
CREATE TABLE mytable_count(c bigint);
 
CREATE FUNCTION mytable_count() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF TG_OP = 'INSERT' THEN
      UPDATE mytable_count SET c = c + 1;
 
      RETURN NEW;
   ELSIF TG_OP = 'DELETE' THEN
      UPDATE mytable_count SET c = c - 1;
 
      RETURN OLD;
   ELSE
      UPDATE mytable_count SET c = 0;
 
      RETURN NULL;
   END IF;
END;$$;
 
CREATE CONSTRAINT TRIGGER mytable_count_mod
   AFTER INSERT OR DELETE ON mytable
   DEFERRABLE INITIALLY DEFERRED
   FOR EACH ROW EXECUTE PROCEDURE mytable_count();
 
-- TRUNCATE triggers must be FOR EACH STATEMENT
CREATE TRIGGER mytable_count_trunc AFTER TRUNCATE ON mytable
   FOR EACH STATEMENT EXECUTE PROCEDURE mytable_count();
 
-- initialize the counter table
INSERT INTO mytable_count
   SELECT count(*) FROM mytable;
 
COMMIT;

आपको वास्तव में गिनती (*) की आवश्यकता है

SELECT reltuples::bigint
FROM pg_catalog.pg_class
WHERE relname = 'mytable';

क्वेरी परिणामों की संख्या का अनुमान लगाना

CREATE FUNCTION row_estimator(query text) RETURNS bigint
   LANGUAGE plpgsql AS
$$DECLARE
   plan jsonb;
BEGIN
   EXECUTE 'EXPLAIN (FORMAT JSON) ' || query INTO plan;
 
   RETURN (plan->0->'Plan'->>'Plan Rows')::bigint;
END;$$;