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 IF NOT EXISTS blacklist
(
    id                    NUMERIC(20, 0)                           NOT NULL PRIMARY KEY,
    device_id             VARCHAR(512)   DEFAULT NULL              NULL,
    phone_number          VARCHAR(512)   DEFAULT NULL              NULL
);

CREATE INDEX index_phone ON blacklist (phone_number);
CREATE INDEX index_device ON blacklist (device_id);

CREATE TABLE

CREATE INDEX

CREATE INDEX

insert into blacklist values (1,'ABC','ABC');

INSERT 0 1

explain analyze
select 1
where exists 
  (select null
  from blacklist where phone_number = 'ABC'
  union all
  select null
  from blacklist where device_id = 'ABC'
  )

QUERY PLAN

Result  (cost=8.25..8.26 rows=1 width=4) (actual time=0.051..0.052 rows=1 loops=1)

One-Time Filter: $0

InitPlan 1 (returns $0)

->  Append  (cost=0.14..16.35 rows=2 width=32) (actual time=0.049..0.050 rows=1 loops=1)

->  Index Only Scan using index_phone on blacklist  (cost=0.14..8.16 rows=1 width=32) (actual time=0.048..0.048 rows=1 loops=1)

Index Cond: (phone_number = 'ABC'::text)

Heap Fetches: 1

->  Index Only Scan using index_device on blacklist blacklist_1  (cost=0.14..8.16 rows=1 width=32) (never executed)

Index Cond: (device_id = 'ABC'::text)

Heap Fetches: 0

Planning Time: 0.410 ms

Execution Time: 0.112 ms

EXPLAIN