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 doc (
  id int
, title text
, content text
);

insert into doc values
(1, 'test title', '9527'),
(2, 'test2', '9528');

create table tag (
  id int
, tagname text
);

insert into tag values
(1, '周星馳'),
(2, '小島南');


create table mapping (
  docid int
, tagid int
);

insert into mapping values
(1,1);
2 rows affected

2 rows affected

1 rows affected

create or replace function f_chk_available(text, text, text)
returns boolean
language sql
as
$code$
with t1 as (
select id
  from doc
 where title = $1
   and content = $2
),t2 as (
select id
  from tag
 where tagname = $3
)
select count(t1.id)::int::boolean
  from t1
  join mapping m
    on t1.id = m.docid
  join t2
    on m.tagid = t2.id;
$code$;
select a.*
     , f_chk_available(title, content, tag)::text as available
  from (values ('test title', '9527', '周星馳')
             , ('test title', '9527', '小島南')
             , ('test2', '9528', '周星馳')
             , ('test2', '9528', '小島南')
       ) as a(title, content, tag);


title
content
tag
available


test title
9527
周星馳
true

test title
9527
小島南
false

test2
9528
周星馳
false

test2
9528
小島南
false

title	content	tag	available
test title	9527	周星馳	true
test title	9527	小島南	false
test2	9528	周星馳	false
test2	9528	小島南	false