Postgres 13

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

select version();


version


PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


CREATE TABLE table1 (a int, b int);
INSERT INTO table1 VALUES 
  (   0 , 1 )
, (   5 , 2 )
, (  10 , 3 )
, (  20 , 4 )
;

CREATE TABLE table2 (c int, d int);

INSERT INTO table2 VALUES
  ( 2 , 100) 
, ( 3 , 101)
, ( 5 , 102)
, ( 7 , 103)
, (11 , 104)
, (13 , 105)
, (17 , 106)
, (19 , 107)
, (23 , 108)
;
4 rows affected

9 rows affected

SELECT t2.*
     , t1.b AS e
  FROM table2 AS t2
  JOIN table1 AS t1
    ON t1.a  <= t2.c
  LEFT JOIN table1 AS t1b
    ON t1b.a <= t2.c
   AND t1.a  < t1b.a
 WHERE t1b.a IS NULL
;


c
d
e


2
100
1

3
101
1

5
102
2

7
103
2

11
104
3

13
105
3

17
106
3

19
107
3

23
108
4