psql output for pg_hashed_subplan_repro.sql and pg_hashed_subplan_variations.sql Each run: a fresh official postgres Docker image, default configuration, run as: psql -X -v ON_ERROR_STOP=1 -f Expected in Q1: (1,t) and (2,t). Every version below returns (2,f) in Q1 and the correct (2,t) in Q2. Hosts: x86_64: Ubuntu 26.04.1 server, kernel 7.0.0-30, Docker 29.8.2 aarch64: Docker 29.7.2 on macOS 26.6 (Apple M3 Max) ================================================================== == pg_hashed_subplan_repro.sql on PostgreSQL 18.6 (x86_64) ================================================================== Pager usage is off. version -------------------------------------------------------------------------------------------------------------------- PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit (1 row) CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE INSERT 0 2 INSERT 0 2 INSERT 0 2 INSERT 0 2 ANALYZE == Q1: each session viewer follows its own channel; expected (1,t), (2,t) id | allowed ----+--------- 1 | t 2 | f (2 rows) == Q2: identical except OFFSET 0 on the inner EXISTS id | allowed ----+--------- 1 | t 2 | t (2 rows) == Q1 plan QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (actual rows=2.00 loops=1) Output: s.id, (EXISTS(SubPlan 3)) Sort Key: s.id Sort Method: quicksort Memory: 25kB Buffers: shared hit=8 -> Seq Scan on public.t_sessions s (actual rows=2.00 loops=1) Output: s.id, EXISTS(SubPlan 3) Buffers: shared hit=8 SubPlan 3 -> Hash Join (actual rows=0.50 loops=2) Inner Unique: true Hash Cond: (p.channel_id = c.id) Join Filter: ((c.owner_id = s.viewer_id) OR ((p.status = 'published'::text) AND ((c.visibility = 'public'::text) OR (ANY (c.id = (hashed SubPlan 2).col1))))) Rows Removed by Join Filter: 0 Buffers: shared hit=7 -> Seq Scan on public.t_posts p (actual rows=1.00 loops=2) Output: p.id, p.channel_id, p.status Filter: (p.id = s.post_id) Rows Removed by Filter: 0 Buffers: shared hit=2 -> Hash (actual rows=1.00 loops=2) Output: c.id, c.owner_id, c.visibility Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=4 -> Seq Scan on public.t_channels c (actual rows=1.00 loops=2) Output: c.id, c.owner_id, c.visibility Filter: ((c.owner_id = s.viewer_id) OR (c.visibility = 'public'::text) OR (ANY (c.id = (hashed SubPlan 2).col1))) Rows Removed by Filter: 1 Buffers: shared hit=4 SubPlan 2 -> Seq Scan on public.t_follows f (actual rows=1.00 loops=3) Output: f.channel_id Filter: (f.user_id = s.viewer_id) Rows Removed by Filter: 1 Buffers: shared hit=3 Planning: Buffers: shared hit=8 (37 rows) ================================================================== == pg_hashed_subplan_repro.sql on PostgreSQL 19beta4 (aarch64) ================================================================== Pager usage is off. version --------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 19beta4 (Debian 19~beta4-1.pgdg13+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit (1 row) CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE INSERT 0 2 INSERT 0 2 INSERT 0 2 INSERT 0 2 ANALYZE == Q1: each session viewer follows its own channel; expected (1,t), (2,t) id | allowed ----+--------- 1 | t 2 | f (2 rows) == Q2: identical except OFFSET 0 on the inner EXISTS id | allowed ----+--------- 1 | t 2 | t (2 rows) == Q1 plan QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (actual rows=2.00 loops=1) Output: s.id, (EXISTS(SubPlan exists_1)) Sort Key: s.id Sort Method: quicksort Memory: 25kB Buffers: shared hit=8 -> Seq Scan on public.t_sessions s (actual rows=2.00 loops=1) Output: s.id, EXISTS(SubPlan exists_1) Buffers: shared hit=8 SubPlan exists_1 -> Hash Join (actual rows=0.50 loops=2) Inner Unique: true Hash Cond: (p.channel_id = c.id) Join Filter: ((c.owner_id = s.viewer_id) OR ((p.status = 'published'::text) AND ((c.visibility = 'public'::text) OR (ANY (c.id = (hashed SubPlan exists_to_any_1).col1))))) Rows Removed by Join Filter: 0 Buffers: shared hit=7 -> Seq Scan on public.t_posts p (actual rows=1.00 loops=2) Output: p.id, p.channel_id, p.status Filter: (p.id = s.post_id) Rows Removed by Filter: 0 Buffers: shared hit=2 -> Hash (actual rows=1.00 loops=2) Output: c.id, c.owner_id, c.visibility Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=4 -> Seq Scan on public.t_channels c (actual rows=1.00 loops=2) Output: c.id, c.owner_id, c.visibility Filter: ((c.owner_id = s.viewer_id) OR (c.visibility = 'public'::text) OR (ANY (c.id = (hashed SubPlan exists_to_any_1).col1))) Rows Removed by Filter: 1 Buffers: shared hit=4 SubPlan exists_to_any_1 -> Seq Scan on public.t_follows f (actual rows=1.00 loops=3) Output: f.channel_id Filter: (f.user_id = s.viewer_id) Rows Removed by Filter: 1 Buffers: shared hit=3 Planning: Buffers: shared hit=8 (37 rows) ================================================================== == pg_hashed_subplan_repro.sql on PostgreSQL 18.6 (aarch64) ================================================================== Pager usage is off. version -------------------------------------------------------------------------------------------------------------------------- PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit (1 row) CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE INSERT 0 2 INSERT 0 2 INSERT 0 2 INSERT 0 2 ANALYZE == Q1: each session viewer follows its own channel; expected (1,t), (2,t) id | allowed ----+--------- 1 | t 2 | f (2 rows) == Q2: identical except OFFSET 0 on the inner EXISTS id | allowed ----+--------- 1 | t 2 | t (2 rows) == Q1 plan QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (actual rows=2.00 loops=1) Output: s.id, (EXISTS(SubPlan 3)) Sort Key: s.id Sort Method: quicksort Memory: 25kB Buffers: shared hit=8 -> Seq Scan on public.t_sessions s (actual rows=2.00 loops=1) Output: s.id, EXISTS(SubPlan 3) Buffers: shared hit=8 SubPlan 3 -> Hash Join (actual rows=0.50 loops=2) Inner Unique: true Hash Cond: (p.channel_id = c.id) Join Filter: ((c.owner_id = s.viewer_id) OR ((p.status = 'published'::text) AND ((c.visibility = 'public'::text) OR (ANY (c.id = (hashed SubPlan 2).col1))))) Rows Removed by Join Filter: 0 Buffers: shared hit=7 -> Seq Scan on public.t_posts p (actual rows=1.00 loops=2) Output: p.id, p.channel_id, p.status Filter: (p.id = s.post_id) Rows Removed by Filter: 0 Buffers: shared hit=2 -> Hash (actual rows=1.00 loops=2) Output: c.id, c.owner_id, c.visibility Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=4 -> Seq Scan on public.t_channels c (actual rows=1.00 loops=2) Output: c.id, c.owner_id, c.visibility Filter: ((c.owner_id = s.viewer_id) OR (c.visibility = 'public'::text) OR (ANY (c.id = (hashed SubPlan 2).col1))) Rows Removed by Filter: 1 Buffers: shared hit=4 SubPlan 2 -> Seq Scan on public.t_follows f (actual rows=1.00 loops=3) Output: f.channel_id Filter: (f.user_id = s.viewer_id) Rows Removed by Filter: 1 Buffers: shared hit=3 Planning: Buffers: shared hit=8 (37 rows) ================================================================== == pg_hashed_subplan_repro.sql on PostgreSQL 17.11 (aarch64) ================================================================== Pager usage is off. version ---------------------------------------------------------------------------------------------------------------------------- PostgreSQL 17.11 (Debian 17.11-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit (1 row) CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE INSERT 0 2 INSERT 0 2 INSERT 0 2 INSERT 0 2 ANALYZE == Q1: each session viewer follows its own channel; expected (1,t), (2,t) id | allowed ----+--------- 1 | t 2 | f (2 rows) == Q2: identical except OFFSET 0 on the inner EXISTS id | allowed ----+--------- 1 | t 2 | t (2 rows) == Q1 plan QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (actual rows=2 loops=1) Output: s.id, (EXISTS(SubPlan 3)) Sort Key: s.id Sort Method: quicksort Memory: 25kB -> Seq Scan on public.t_sessions s (actual rows=2 loops=1) Output: s.id, EXISTS(SubPlan 3) SubPlan 3 -> Hash Join (actual rows=0 loops=2) Inner Unique: true Hash Cond: (p.channel_id = c.id) Join Filter: ((c.owner_id = s.viewer_id) OR ((p.status = 'published'::text) AND ((c.visibility = 'public'::text) OR (ANY (c.id = (hashed SubPlan 2).col1))))) Rows Removed by Join Filter: 0 -> Seq Scan on public.t_posts p (actual rows=1 loops=2) Output: p.id, p.channel_id, p.status Filter: (p.id = s.post_id) Rows Removed by Filter: 0 -> Hash (actual rows=1 loops=2) Output: c.id, c.owner_id, c.visibility Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on public.t_channels c (actual rows=1 loops=2) Output: c.id, c.owner_id, c.visibility Filter: ((c.owner_id = s.viewer_id) OR (c.visibility = 'public'::text) OR (ANY (c.id = (hashed SubPlan 2).col1))) Rows Removed by Filter: 1 SubPlan 2 -> Seq Scan on public.t_follows f (actual rows=1 loops=3) Output: f.channel_id Filter: (f.user_id = s.viewer_id) Rows Removed by Filter: 1 (28 rows) ================================================================== == pg_hashed_subplan_repro.sql on PostgreSQL 16.15 (aarch64) ================================================================== Pager usage is off. version ---------------------------------------------------------------------------------------------------------------------------- PostgreSQL 16.15 (Debian 16.15-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit (1 row) CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE INSERT 0 2 INSERT 0 2 INSERT 0 2 INSERT 0 2 ANALYZE == Q1: each session viewer follows its own channel; expected (1,t), (2,t) id | allowed ----+--------- 1 | t 2 | f (2 rows) == Q2: identical except OFFSET 0 on the inner EXISTS id | allowed ----+--------- 1 | t 2 | t (2 rows) == Q1 plan QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (actual rows=2 loops=1) Output: s.id, ((SubPlan 3)) Sort Key: s.id Sort Method: quicksort Memory: 25kB -> Seq Scan on public.t_sessions s (actual rows=2 loops=1) Output: s.id, (SubPlan 3) SubPlan 3 -> Hash Join (actual rows=0 loops=2) Inner Unique: true Hash Cond: (p.channel_id = c.id) Join Filter: ((c.owner_id = s.viewer_id) OR ((p.status = 'published'::text) AND ((c.visibility = 'public'::text) OR (hashed SubPlan 2)))) Rows Removed by Join Filter: 0 -> Seq Scan on public.t_posts p (actual rows=1 loops=2) Output: p.id, p.channel_id, p.status Filter: (p.id = s.post_id) Rows Removed by Filter: 0 -> Hash (actual rows=1 loops=2) Output: c.id, c.owner_id, c.visibility Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on public.t_channels c (actual rows=1 loops=2) Output: c.id, c.owner_id, c.visibility Filter: ((c.owner_id = s.viewer_id) OR (c.visibility = 'public'::text) OR (hashed SubPlan 2)) Rows Removed by Filter: 1 SubPlan 2 -> Seq Scan on public.t_follows f (actual rows=1 loops=3) Output: f.channel_id Filter: (f.user_id = s.viewer_id) Rows Removed by Filter: 1 (28 rows) ================================================================== == pg_hashed_subplan_repro.sql on PostgreSQL 15.19 (aarch64) ================================================================== Pager usage is off. version ---------------------------------------------------------------------------------------------------------------------------- PostgreSQL 15.19 (Debian 15.19-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit (1 row) CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE INSERT 0 2 INSERT 0 2 INSERT 0 2 INSERT 0 2 ANALYZE == Q1: each session viewer follows its own channel; expected (1,t), (2,t) id | allowed ----+--------- 1 | t 2 | f (2 rows) == Q2: identical except OFFSET 0 on the inner EXISTS id | allowed ----+--------- 1 | t 2 | t (2 rows) == Q1 plan QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (actual rows=2 loops=1) Output: s.id, ((SubPlan 3)) Sort Key: s.id Sort Method: quicksort Memory: 25kB -> Seq Scan on public.t_sessions s (actual rows=2 loops=1) Output: s.id, (SubPlan 3) SubPlan 3 -> Hash Join (actual rows=0 loops=2) Inner Unique: true Hash Cond: (p.channel_id = c.id) Join Filter: ((c.owner_id = s.viewer_id) OR ((p.status = 'published'::text) AND ((c.visibility = 'public'::text) OR (hashed SubPlan 2)))) Rows Removed by Join Filter: 0 -> Seq Scan on public.t_posts p (actual rows=1 loops=2) Output: p.id, p.channel_id, p.status Filter: (p.id = s.post_id) Rows Removed by Filter: 0 -> Hash (actual rows=1 loops=2) Output: c.id, c.owner_id, c.visibility Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on public.t_channels c (actual rows=1 loops=2) Output: c.id, c.owner_id, c.visibility Filter: ((c.owner_id = s.viewer_id) OR (c.visibility = 'public'::text) OR (hashed SubPlan 2)) Rows Removed by Filter: 1 SubPlan 2 -> Seq Scan on public.t_follows f (actual rows=1 loops=3) Output: f.channel_id Filter: (f.user_id = s.viewer_id) Rows Removed by Filter: 1 (28 rows) ================================================================== == pg_hashed_subplan_repro.sql on PostgreSQL 14.24 (aarch64) ================================================================== Pager usage is off. version ---------------------------------------------------------------------------------------------------------------------------- PostgreSQL 14.24 (Debian 14.24-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit (1 row) CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE INSERT 0 2 INSERT 0 2 INSERT 0 2 INSERT 0 2 ANALYZE == Q1: each session viewer follows its own channel; expected (1,t), (2,t) id | allowed ----+--------- 1 | t 2 | f (2 rows) == Q2: identical except OFFSET 0 on the inner EXISTS id | allowed ----+--------- 1 | t 2 | t (2 rows) == Q1 plan QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (actual rows=2 loops=1) Output: s.id, ((SubPlan 3)) Sort Key: s.id Sort Method: quicksort Memory: 25kB -> Seq Scan on public.t_sessions s (actual rows=2 loops=1) Output: s.id, (SubPlan 3) SubPlan 3 -> Hash Join (actual rows=0 loops=2) Inner Unique: true Hash Cond: (p.channel_id = c.id) Join Filter: ((c.owner_id = s.viewer_id) OR ((p.status = 'published'::text) AND ((c.visibility = 'public'::text) OR (hashed SubPlan 2)))) Rows Removed by Join Filter: 0 -> Seq Scan on public.t_posts p (actual rows=1 loops=2) Output: p.id, p.channel_id, p.status Filter: (p.id = s.post_id) Rows Removed by Filter: 0 -> Hash (actual rows=1 loops=2) Output: c.id, c.owner_id, c.visibility Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on public.t_channels c (actual rows=1 loops=2) Output: c.id, c.owner_id, c.visibility Filter: ((c.owner_id = s.viewer_id) OR (c.visibility = 'public'::text) OR (hashed SubPlan 2)) Rows Removed by Filter: 1 SubPlan 2 -> Seq Scan on public.t_follows f (actual rows=1 loops=3) Output: f.channel_id Filter: (f.user_id = s.viewer_id) Rows Removed by Filter: 1 (28 rows) ================================================================== == pg_hashed_subplan_repro.sql on PostgreSQL 13.23 (aarch64) ================================================================== Pager usage is off. version ---------------------------------------------------------------------------------------------------------------------------- PostgreSQL 13.23 (Debian 13.23-1.pgdg13+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit (1 row) CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE INSERT 0 2 INSERT 0 2 INSERT 0 2 INSERT 0 2 ANALYZE == Q1: each session viewer follows its own channel; expected (1,t), (2,t) id | allowed ----+--------- 1 | t 2 | f (2 rows) == Q2: identical except OFFSET 0 on the inner EXISTS id | allowed ----+--------- 1 | t 2 | t (2 rows) == Q1 plan QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (actual rows=2 loops=1) Output: s.id, ((SubPlan 3)) Sort Key: s.id Sort Method: quicksort Memory: 25kB -> Seq Scan on public.t_sessions s (actual rows=2 loops=1) Output: s.id, (SubPlan 3) SubPlan 3 -> Hash Join (actual rows=0 loops=2) Inner Unique: true Hash Cond: (p.channel_id = c.id) Join Filter: ((c.owner_id = s.viewer_id) OR ((p.status = 'published'::text) AND ((c.visibility = 'public'::text) OR (alternatives: SubPlan 1 or hashed SubPlan 2)))) Rows Removed by Join Filter: 0 -> Seq Scan on public.t_posts p (actual rows=1 loops=2) Output: p.id, p.channel_id, p.status Filter: (p.id = s.post_id) Rows Removed by Filter: 0 -> Hash (actual rows=1 loops=2) Output: c.id, c.owner_id, c.visibility Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on public.t_channels c (actual rows=1 loops=2) Output: c.id, c.owner_id, c.visibility Filter: ((c.owner_id = s.viewer_id) OR (c.visibility = 'public'::text) OR (alternatives: SubPlan 1 or hashed SubPlan 2)) Rows Removed by Filter: 1 SubPlan 1 -> Seq Scan on public.t_follows f (never executed) Filter: ((f.channel_id = c.id) AND (f.user_id = s.viewer_id)) SubPlan 2 -> Seq Scan on public.t_follows f_1 (actual rows=1 loops=3) Output: f_1.channel_id Filter: (f_1.user_id = s.viewer_id) Rows Removed by Filter: 1 (31 rows) ================================================================== == pg_hashed_subplan_variations.sql on PostgreSQL 18.6 (aarch64) ================================================================== Pager usage is off. version -------------------------------------------------------------------------------------------------------------------------- PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit (1 row) CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE INSERT 0 2 INSERT 0 2 INSERT 0 2 INSERT 0 2 ANALYZE == V1: EXISTS referenced only once (no OR-qual extraction duplicate); expected (1,t), (2,t) id | allowed ----+--------- 1 | t 2 | t (2 rows) == V2: same rule as Q1, viewer passed as a bind parameter, one call per session; expected t then t PREPARE allowed --------- t (1 row) allowed --------- t (1 row) == V3: Q1 restricted to session 2 alone (single outer row); expected t id | allowed ----+--------- 2 | t (1 row) == V4: Q1 with the outer rows in reverse order; expected (2,t), (1,t) id | allowed ----+--------- 2 | t 1 | f (2 rows)