Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE test (
  id serial
, some_integer int
, payload text
);

INSERT INTO test (some_integer, payload)
SELECT trunc(random() * 10000)  -- fewer distinct values, still moderately few dupes
     , 'Lorem ipsum dolor sit amet, consectetur adipisicing elit'  -- bigger row !!
FROM   generate_series(1,100000) s;

CREATE INDEX some_integer_idx ON test (some_integer);

CREATE TABLE

INSERT 0 100000

CREATE INDEX

VACUUM ANALYZE test;

VACUUM

EXPLAIN ANALYZE SELECT count(DISTINCT some_integer) FROM test;

QUERY PLAN

Aggregate  (cost=2214.29..2214.30 rows=1 width=8) (actual time=19.541..19.542 rows=1 loops=1)

  ->  Index Only Scan using some_integer_idx on test  (cost=0.29..1964.29 rows=100000 width=4) (actual time=0.024..9.247 rows=100000 loops=1)

        Heap Fetches: 0

Planning Time: 0.341 ms

Execution Time: 19.679 ms

EXPLAIN