Postgres 9.6

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?.

DROP TABLE IF EXISTS t ;

CREATE TABLE t
(
    id integer /* PRIMARY KEY */, 
    stuff integer, 
    a_value float
) ;
    
INSERT INTO t
	(id, stuff, a_value)
SELECT
	id, random()*10000,  random()
FROM
       generate_series(1, 10000) AS i(id) ;

CREATE INDEX
        idx_value ON t(id, a_value);
10000 rows affected

VACUUM ANALYZE VERBOSE t ;
EXPLAIN ANALYZE
SELECT
	avg(a_value)
FROM
	t
WHERE
	id BETWEEN 5000 and 7500 
    AND (a_value > 0.1)


QUERY PLAN


Aggregate  (cost=103.67..103.69 rows=1 width=8) (actual time=1.139..1.140 rows=1 loops=1)

  ->  Index Only Scan using idx_value on t  (cost=0.29..98.05 rows=2251 width=8) (actual time=0.026..0.655 rows=2247 loops=1)

        Index Cond: ((id >= 5000) AND (id <= 7500) AND (a_value > '0.1'::double precision))

        Heap Fetches: 0

Planning time: 0.184 ms

Execution time: 1.179 ms