-- A condition over the columns of a joined sub-select that PostgreSQL pushed down into it (the run condition of a ranking window) is enforced by the plan
-- of the sub-select, which then does not output the column: the scan joins its rows without testing the condition again.
CREATE EXTENSION pg_sift;
SET default_statistics_target = 10000;
SET max_parallel_workers_per_gather = 0;
CREATE TABLE dim_a (id int PRIMARY KEY, kind text);
INSERT INTO dim_a SELECT i, (ARRAY['a', 'b', 'c'])[1 + i % 3] FROM generate_series(1, 90) i;
CREATE TABLE facts (id int, day date, dim_a_id int, deleted_at timestamp);
INSERT INTO facts SELECT i, '2026-01-01'::date + (i % 15), 1 + (i * i) % 97, CASE WHEN i % 17 = 0 THEN now() END FROM generate_series(1, 40000) i;
CREATE INDEX facts_col ON facts USING sift_col (id, day, dim_a_id, deleted_at);
VACUUM (FREEZE, ANALYZE) facts;
ANALYZE dim_a;
\ir test/agg_check_query.sql
-- The entities of the top three of their kind: the semi join to the ranked sub-select, whose `rn <= 3` the window evaluates itself.
SELECT * FROM check_query($q$
 SELECT e.kind, count(*) AS n
 FROM facts f JOIN dim_a e ON e.id = f.dim_a_id
 WHERE f.dim_a_id IN (SELECT dim_a_id FROM (
   SELECT dim_a_id, row_number() OVER (PARTITION BY kind ORDER BY n DESC, dim_a_id) AS rn
   FROM (SELECT f2.dim_a_id, e2.kind, count(*) AS n FROM facts f2 JOIN dim_a e2 ON e2.id = f2.dim_a_id GROUP BY 1, 2) c) ranked
   WHERE rn <= 3)
 GROUP BY 1
$q$);
-- The same as an inner join, and as a LEFT JOIN whose ON clause holds the condition.
SELECT * FROM check_query($q$
 SELECT e.kind, count(*) AS n
 FROM facts f JOIN dim_a e ON e.id = f.dim_a_id
 JOIN (SELECT dim_a_id, row_number() OVER (PARTITION BY kind ORDER BY n DESC, dim_a_id) AS rn
       FROM (SELECT f2.dim_a_id, e2.kind, count(*) AS n FROM facts f2 JOIN dim_a e2 ON e2.id = f2.dim_a_id GROUP BY 1, 2) c) ranked
   ON ranked.dim_a_id = f.dim_a_id AND ranked.rn <= 2
 GROUP BY 1
$q$);
SELECT * FROM check_query($q$
 SELECT e.kind, count(*) AS n, count(ranked.dim_a_id) AS ranked_rows
 FROM facts f JOIN dim_a e ON e.id = f.dim_a_id
 LEFT JOIN (SELECT dim_a_id, row_number() OVER (PARTITION BY kind ORDER BY n DESC, dim_a_id) AS rn
       FROM (SELECT f2.dim_a_id, e2.kind, count(*) AS n FROM facts f2 JOIN dim_a e2 ON e2.id = f2.dim_a_id GROUP BY 1, 2) c) ranked
   ON ranked.dim_a_id = f.dim_a_id AND ranked.rn <= 2
 GROUP BY 1
$q$);
-- The rank is read by the query too, so its column stays in the plan's output.
SELECT * FROM check_query($q$
 SELECT ranked.rn, count(*) AS n
 FROM facts f JOIN (SELECT dim_a_id, row_number() OVER (PARTITION BY kind ORDER BY n DESC, dim_a_id) AS rn
       FROM (SELECT f2.dim_a_id, e2.kind, count(*) AS n FROM facts f2 JOIN dim_a e2 ON e2.id = f2.dim_a_id GROUP BY 1, 2) c) ranked
   ON ranked.dim_a_id = f.dim_a_id
 WHERE ranked.rn <= 3
 GROUP BY 1
$q$);
DROP FUNCTION check_query(text);
DROP TABLE facts, dim_a;
DROP EXTENSION pg_sift;