Postgres 11

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 11.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit

CREATE TABLE IF NOT EXISTS valrng  (
  low int,
  high int
);
CREATE TABLE IF NOT EXISTS vals  (
  val int
);
INSERT INTO valrng VALUES (2,5),(7,8);
INSERT INTO vals VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
2 rows affected

10 rows affected

EXPLAIN ANALYZE SELECT val FROM valrng AS vr, vals AS v WHERE vr.low <= v.val AND v.val <= vr.high;

QUERY PLAN

Nested Loop  (cost=0.00..100926.25 rows=640333 width=4) (actual time=0.046..0.053 rows=6 loops=1)

  Join Filter: ((vr.low <= v.val) AND (v.val <= vr.high))

  Rows Removed by Join Filter: 14

  ->  Seq Scan on vals v  (cost=0.00..35.50 rows=2550 width=4) (actual time=0.012..0.013 rows=10 loops=1)

  ->  Materialize  (cost=0.00..43.90 rows=2260 width=8) (actual time=0.002..0.002 rows=2 loops=10)

        ->  Seq Scan on valrng vr  (cost=0.00..32.60 rows=2260 width=8) (actual time=0.006..0.007 rows=2 loops=1)

Planning Time: 0.228 ms

Execution Time: 0.406 ms

SELECT val FROM valrng AS vr, vals AS v WHERE vr.low <= v.val AND v.val <= vr.high;

val

2

3

4

5

7

8