\pset pager off
select version();
create table t_channels (id int primary key, owner_id int not null, visibility text not null);
create table t_posts (id int primary key, channel_id int not null, status text not null);
create table t_follows (channel_id int not null, user_id int not null, primary key (channel_id, user_id));
create table t_sessions (id int primary key, post_id int not null, viewer_id int not null);
insert into t_channels values (1, 100, 'private'), (2, 200, 'private');
insert into t_posts values (10, 1, 'published'), (20, 2, 'published');
insert into t_follows values (1, 501), (2, 502);
insert into t_sessions values (1, 10, 501), (2, 20, 502);
analyze t_channels, t_posts, t_follows, t_sessions;

\echo '== Q1: each session viewer follows its own channel; expected (1,t), (2,t)'
select s.id,
  exists (select 1 from t_posts p join t_channels c on c.id = p.channel_id
           where p.id = s.post_id
             and (c.owner_id = s.viewer_id
                  or (p.status = 'published'
                      and (c.visibility = 'public'
                           or exists (select 1 from t_follows f where f.channel_id = c.id and f.user_id = s.viewer_id))))) as allowed
  from t_sessions s order by s.id;

\echo '== Q2: identical except OFFSET 0 on the inner EXISTS'
select s.id,
  exists (select 1 from t_posts p join t_channels c on c.id = p.channel_id
           where p.id = s.post_id
             and (c.owner_id = s.viewer_id
                  or (p.status = 'published'
                      and (c.visibility = 'public'
                           or exists (select 1 from t_follows f where f.channel_id = c.id and f.user_id = s.viewer_id offset 0))))) as allowed
  from t_sessions s order by s.id;

\echo '== Q1 plan'
explain (analyze, verbose, costs off, timing off, summary off)
select s.id,
  exists (select 1 from t_posts p join t_channels c on c.id = p.channel_id
           where p.id = s.post_id
             and (c.owner_id = s.viewer_id
                  or (p.status = 'published'
                      and (c.visibility = 'public'
                           or exists (select 1 from t_follows f where f.channel_id = c.id and f.user_id = s.viewer_id))))) as allowed
  from t_sessions s order by s.id;