提升 PostgreSQL 中 Count 的速度

-- create a million random numbers and strings
CREATE TABLE items AS
  SELECT
    (random()*1000000)::integer AS n,
    md5(random()::text) AS s
  FROM
    generate_series(1,1000000);
-- inform planner of big table size change
VACUUM ANALYZE;

# Tests in this article were run against PostgreSQL 9.5.4
echo "SELECT count(*) FROM items;" | pgbench -d count -t 50 -P 1 -f -
# average  84.915 ms
# stddev    5.251 ms

# average  98.896 ms
# stddev    7.280 ms

EXPLAIN SELECT count(*) FROM items;
Aggregate  (cost=20834.00..20834.01 rows=1 width=0)
  ->  Seq Scan on items  (cost=0.00..18334.00 rows=1000000 width=0)

BEGIN;
CREATE TABLE row_counts (
  relname   text PRIMARY KEY,
  reltuples bigint
);
-- establish initial count
INSERT INTO row_counts (relname, reltuples)
  VALUES ('items', (SELECT count(*) from items));
CREATE OR REPLACE FUNCTION adjust_count()
RETURNS TRIGGER AS
$
   DECLARE
   BEGIN
   IF TG_OP = 'INSERT' THEN
      EXECUTE 'UPDATE row_counts set reltuples=reltuples +1 where relname = ''' || TG_RELNAME || '''';
      RETURN NEW;
   ELSIF TG_OP = 'DELETE' THEN
      EXECUTE 'UPDATE row_counts set reltuples=reltuples -1 where relname = ''' || TG_RELNAME || '''';
      RETURN OLD;
   END IF;
   END;
$
LANGUAGE 'plpgsql';
CREATE TRIGGER items_count BEFORE INSERT OR DELETE ON items
  FOR EACH ROW EXECUTE PROCEDURE adjust_count();
COMMIT;

INSERT INTO items (n, s)
  SELECT
    (random()*1000000)::integer AS n,
    md5(random()::text) AS s
  FROM generate_series(1,1000000);

-- Asking the stats collector
SELECT n_live_tup
  FROM pg_stat_all_tables
 WHERE relname = 'items';
-- Updated by VACUUM and ANALYZE
SELECT reltuples
  FROM pg_class
 WHERE relname = 'items';

-- pg_relation_size and block size have fresh info so combine them with
-- the estimation of tuples per page
SELECT
  (reltuples/relpages) * (
    pg_relation_size('items') /
    (current_setting('block_size')::integer)
  )
  FROM pg_class where relname = 'items';

CREATE FUNCTION count_estimate(query text) RETURNS integer AS $
DECLARE
  rec   record;
  rows  integer;
BEGIN
  FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP
    rows := substring(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');
    EXIT WHEN rows IS NOT NULL;
  END LOOP;
  RETURN rows;
END;
$ LANGUAGE plpgsql VOLATILE STRICT;

SELECT count_estimate('SELECT 1 FROM items WHERE n < 1000');

echo "SELECT count(DISTINCT n) FROM items;" | pgbench -d count -t 50 -P 1 -f -
# average  742.855 ms
# stddev    21.907 ms
echo "SELECT count(DISTINCT s) FROM items;" | pgbench -d count -t 5 -P 1 -f -
# average  31747.337 ms
# stddev     267.183 ms

-- plan for the integer column, n
Aggregate  (cost=20834.00..20834.01 rows=1 width=4) (actual time=860.620..860.620 rows=1 loops=1)
  Output: count(DISTINCT n)
  Buffers: shared hit=3904 read=4430, temp read=1467 written=1467
  ->  Seq Scan on public.items  (cost=0.00..18334.00 rows=1000000 width=4) (actual time=0.005..107.702 rows=1000000 loops=1)
        Output: n, s
        Buffers: shared hit=3904 read=4430
-- plan for the text column, s
Aggregate  (cost=20834.00..20834.01 rows=1 width=33) (actual time=31172.340..31172.340 rows=1 loops=1)
  Output: count(DISTINCT s)
  Buffers: shared hit=3936 read=4398, temp read=5111 written=5111
  ->  Seq Scan on public.items  (cost=0.00..18334.00 rows=1000000 width=33) (actual time=0.005..142.276 rows=1000000 loops=1)
        Output: n, s
        Buffers: shared hit=3936 read=4398

EXPLAIN (ANALYZE, VERBOSE) SELECT DISTINCT n FROM items;
Unique  (cost=131666.34..136666.34 rows=498824 width=4) (actual time=766.775..1229.040 rows=631846 loops=1)
  Output: n
  ->  Sort  (cost=131666.34..134166.34 rows=1000000 width=4) (actual time=766.774..1075.712 rows=1000000 loops=1)
        Output: n
        Sort Key: items.n
        Sort Method: external merge  Disk: 13632kB
        ->  Seq Scan on public.items  (cost=0.00..18334.00 rows=1000000 width=4) (actual time=0.006..178.153 rows=1000000 loops=1)
              Output: n

echo "SELECT COUNT_DISTINCT(n) FROM items;" | pgbench -d count -t 50 -P 1 -f -
# average  434.726 ms
# stddev    19.955 ms

SET work_mem='1GB';
EXPLAIN SELECT DISTINCT n FROM items;
HashAggregate  (cost=20834.00..25822.24 rows=498824 width=4)
  Group Key: n
  ->  Seq Scan on items  (cost=0.00..18334.00 rows=1000000 width=4)