-- One column read twice under binary-compatible types (as itself and relabeled for a comparison with a char column) is one input of an expression key.
CREATE EXTENSION pg_sift;
SET max_parallel_workers_per_gather = 0;
CREATE TABLE countries (iso2 char(2) PRIMARY KEY, name text);
INSERT INTO countries VALUES ('US', 'United States'), ('BR', 'Brazil');
CREATE TABLE facts (id int, kind text, country_code varchar(2));
INSERT INTO facts SELECT i, CASE i % 3 WHEN 0 THEN 'a' WHEN 1 THEN 'b' END, CASE i % 4 WHEN 0 THEN 'US' WHEN 1 THEN 'BR' WHEN 2 THEN 'XX' END FROM generate_series(1, 20000) i;
CREATE INDEX facts_col ON facts USING sift_col (id, kind, country_code);
NOTICE:  sift_col index "facts_col": 1 automatic choice (rollup_columns), 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key; rollup_columns = kind, country_code
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('facts_col');
VACUUM (FREEZE, ANALYZE) facts;
ANALYZE countries;
-- The result, or the SQLSTATE and message, with the scan off and on. on: the table scans are off, so a Custom Scan (SiftAgg) runs when the query is one it can run.
\ir test/agg_helpers.sql
\set ECHO none
CREATE FUNCTION check_query(name text, q text) RETURNS TABLE (label text, scan bool, same bool, outcome text) LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY SELECT name, runs_in_scan(q), outcome(q, false) = outcome(q, true), left(outcome(q, true), 100);
END $$;
SELECT * FROM check_query('key', $q$
 SELECT jsonb_build_array(jsonb_build_object('kind', kind), CASE WHEN country_code IN (SELECT iso2 FROM countries) THEN jsonb_build_object('iso2', country_code, 'type', 'country') END) AS attribute,
        count(*) AS n
 FROM facts GROUP BY 1
$q$);
 label | scan | same |                                               outcome                                                
-------+------+------+------------------------------------------------------------------------------------------------------
 key   | t    | t    | [{"n": 1666, "attribute": [{"kind": "a"}, {"iso2": "BR", "type": "country"}]}, {"n": 1666, "attribut
(1 row)

SELECT * FROM check_query('key and filter', $q$
 SELECT CASE WHEN country_code IN (SELECT iso2 FROM countries) THEN country_code || kind END AS k, count(*) FILTER (WHERE country_code IN (SELECT iso2 FROM countries)) AS known
 FROM facts GROUP BY 1
$q$);
     label      | scan | same |                                               outcome                                                
----------------+------+------+------------------------------------------------------------------------------------------------------
 key and filter | t    | t    | [{"k": "BRa", "known": 1666}, {"k": "BRb", "known": 1667}, {"k": "USa", "known": 1666}, {"k": "USb",
(1 row)

DROP FUNCTION check_query(text, text), outcome(text, bool), runs_in_scan(text), pushed, runq;
DROP TABLE facts, countries;
DROP EXTENSION pg_sift;