Postgres 12

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 playground (id serial PRIMARY KEY, val int, payload text);
insert into playground (payload, val) values 
  ('foo1', 1)
, ('foo2', 4)
, ('foo3', 10)
, ('foo4', 20)
, ('foo5', 21)
, ('foo6', 23)
, ('foo7', 111)
, ('foo8', 300);

create index on playground (val);

-- function
CREATE OR REPLACE FUNCTION foo(_ranges int[])
  RETURNS SETOF playground LANGUAGE plpgsql PARALLEL SAFE STABLE AS
$func$
DECLARE
   _range   int[];
BEGIN
   FOREACH _range SLICE 1 IN ARRAY _ranges
   LOOP
      RETURN QUERY
      SELECT * FROM playground WHERE val BETWEEN _range[1] AND _range[2];
   END LOOP;
END
$func$;

CREATE TABLE



INSERT 0 8



CREATE INDEX



CREATE FUNCTION


SET enable_seqscan = false;

SELECT * FROM playground WHERE val BETWEEN   1 AND   5
UNION ALL
SELECT * FROM playground WHERE val BETWEEN  20 AND  25
UNION ALL
SELECT * FROM playground WHERE val BETWEEN 200 AND 400;

SET




id
val
payload


1
1
foo1

2
4
foo2

4
20
foo4

5
21
foo5

6
23
foo6

8
300
foo8



SELECT 6


SELECT * FROM foo('{{1,5},{20,25},{200,400}}');


id
val
payload


1
1
foo1

2
4
foo2

4
20
foo4

5
21
foo5

6
23
foo6

8
300
foo8



SELECT 6