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?.
select version();

version

PostgreSQL 12.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-15), 64-bit

SELECT 1

create table users (userid text primary key); 
create table posts (postid text primary key);
 
create table likedposts ( lp_id  text primary key
                        , userid text references users(userid) 
                        , postid text references posts(postid)
                        );

CREATE TABLE

CREATE TABLE

CREATE TABLE

-- #1 Relationships properly defines 
with uc(uc_schema, uc_name) as   
     (select rc.unique_constraint_schema,rc.unique_constraint_name
        from information_schema.table_constraints  tc 
        join information_schema.referential_constraints rc 
          on (tc.constraint_schema,tc.constraint_name) =
             (rc.constraint_schema,rc.constraint_name) 
       where (tc.table_schema,tc.table_name,tc.constraint_type) = 
             ('public','likedposts','FOREIGN KEY') 
     )  
select tcCnt = (select count(*) from uc)  
  from ( select distinct count(*)  over() tcCnt  
           from information_schema.table_constraints tc
           join uc 
             on (    tc.constraint_schema = uc_schema
                 and tc.constraint_name   = uc_name
                 and (   (tc.table_schema,tc.table_name) = ('public','posts' )
                      or (tc.table_schema,tc.table_name) = ('public','users' )
                     )
                ) 
        ) sc; 

?column?

t

SELECT 1

--#2 Some User likes some Post 
select exists ( select null from likedposts);

insert into users(userid)
       select 'user-' || to_char(n, 'fm09')
         from generate_series(1,5) gn(n);
         
insert into posts(postid)
       select 'post-' || to_char(n, 'fm09')
         from generate_series(1,5) gn(n); 
     
insert into likedposts(lp_id,userid, postid) 
       values ('lp13', 'user-01', 'post-03');

exists

f

SELECT 1

INSERT 0 5

INSERT 0 5

INSERT 0 1

select exists ( select null from likedposts);

exists

t

SELECT 1

-- #3A User x like some Post
select exists ( select null 
                  from likedposts lp
                  join users      u 
                    on u.userid = lp.userid
                 where u.userid = 'user-10' 
              ) ;
          
-- #3B Some user likes Post-02          
select exists ( select null 
                  from likedposts lp
                  join posts      p
                    on p.postid = lp.postid
                 where p.postid = 'post-03' 
              ) ;  
              
-- #3C Does user-01 like post-03
select exists ( select null 
                  from likedposts lp
                  join posts      p
                     on (p.postid = lp.postid)
                  join users      u
                    on (u.userid = lp.userid) 
                 where u.userid = 'user-01' 
                   and p.postid = 'post-03' 
              ) ; 

exists

f

SELECT 1

exists

t

SELECT 1

exists

t

SELECT 1