-- x = ANY (ARRAY(SELECT ..)): the keys of an uncorrelated ARRAY sub-select are known when the scan starts, and then they are the constants of an IN list: the point directory of the column
-- hands the scan the rows of the keys, whatever the heap, where a test of every row of every unit answered the same. The answers are the table's (a copy without an index is the oracle).
CREATE EXTENSION pg_sift;
SET client_min_messages = warning;
SET max_parallel_workers_per_gather = 0;
SET sift.async_maintenance = off;
-- the heap in the order of a hash of id: no range of ids is a range of pages
CREATE TABLE ia AS SELECT g::int AS id, (g % 1000)::int AS v, (g % 7)::int AS w FROM generate_series(1, 100000) g ORDER BY md5((g * 31 + 7)::text);
CREATE INDEX ia_i ON ia USING sift_col (id, v, w) WITH (point_tid_columns = 'id');
VACUUM (FREEZE, ANALYZE) ia;
CREATE TABLE ia_plain AS TABLE ia;
CREATE TABLE ik AS SELECT (g * 1999 % 100000 + 1)::int AS k FROM generate_series(1, 20) g;
INSERT INTO ik VALUES (NULL), (0), (100001);
ANALYZE ik;
-- the rows the point directory handed the scan (0 when the units were read)
CREATE FUNCTION ia_points(q text) RETURNS int LANGUAGE plpgsql SET sift.enable_agg = on SET max_parallel_workers_per_gather = 0 AS $$
DECLARE l text;
BEGIN
	FOR l IN EXECUTE 'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) ' || q LOOP
		IF l ~ 'from point directory \((\d+) rows\)' THEN RETURN substring(l from 'from point directory \((\d+) rows\)')::int; END IF;
	END LOOP;
	RETURN 0;
END $$;
SELECT count(*), sum(v) FROM ia WHERE id = ANY (ARRAY(SELECT k FROM ik));
 count |  sum  
-------+-------
    20 | 18810
(1 row)

SELECT count(*), sum(v) FROM ia_plain WHERE id = ANY (ARRAY(SELECT k FROM ik));
 count |  sum  
-------+-------
    20 | 18810
(1 row)

SELECT count(*), sum(v), sum(w) FROM ia WHERE id = ANY (ARRAY(SELECT k FROM ik)) AND w <> 3;
 count |  sum  | sum 
-------+-------+-----
    17 | 15840 |  53
(1 row)

SELECT count(*), sum(v), sum(w) FROM ia_plain WHERE id = ANY (ARRAY(SELECT k FROM ik)) AND w <> 3;
 count |  sum  | sum 
-------+-------+-----
    17 | 15840 |  53
(1 row)

SELECT count(*) FROM ia WHERE id <> ALL (ARRAY(SELECT k FROM ik));
 count 
-------
     0
(1 row)

SELECT count(*) FROM ia_plain WHERE id <> ALL (ARRAY(SELECT k FROM ik));
 count 
-------
     0
(1 row)

SELECT count(*), sum(v) FROM ia WHERE id = ANY (ARRAY(SELECT k FROM ik WHERE false));
 count | sum 
-------+-----
     0 |    
(1 row)

SELECT count(*), sum(v) FROM ia WHERE id = ANY (ARRAY(SELECT k FROM ik WHERE k IS NULL));
 count | sum 
-------+-----
     0 |    
(1 row)

SELECT ia_points('SELECT count(*), sum(v) FROM ia WHERE id = ANY (ARRAY(SELECT k FROM ik))') AS rows_of_the_keys,
       ia_points('SELECT count(*), sum(v) FROM ia WHERE id = ANY (ARRAY(SELECT k FROM ik)) AND w <> 3') > 0 AS with_a_filter,
       ia_points('SELECT count(*), sum(v) FROM ia WHERE id <> ALL (ARRAY(SELECT k FROM ik))') AS not_in_reads_the_units;
 rows_of_the_keys | with_a_filter | not_in_reads_the_units 
------------------+---------------+------------------------
               20 | t             |                      0
(1 row)

DROP FUNCTION ia_points(text);
DROP TABLE ia, ia_plain, ik;
DROP EXTENSION pg_sift;