-- A table that was loaded and never vacuumed or analyzed has reltuples = -1 and nothing visible in its visibility map. The probe rewrite of an EXISTS, NOT EXISTS, NOT IN or
-- join to a large btree table was refused for it (it asked pg_class for the rows), so the scan read the whole table or PostgreSQL kept its own plan: the planner's own estimate
-- of the rows (from the pages and the row width) decides, as it does for the plans around it. A correlated LIMIT 1 child that the scan probes in its btree (probe_direct) is priced
-- by what the probe takes, not by the normal plan's idea of the rows: it is the scan's plan in the three states.
CREATE EXTENSION pg_sift;
SET client_min_messages = warning;
SET max_parallel_workers_per_gather = 0;
SET jit = off;
CREATE TABLE ev (id int, fk int, v int) WITH (autovacuum_enabled = false);
INSERT INTO ev SELECT i, 1 + i % 3000, i % 7 FROM generate_series(1, 300000) i;
CREATE INDEX ev_col ON ev USING sift_col (id, fk, v);
CREATE TABLE lk (id int NOT NULL, parent int NOT NULL, status int, label text, weight int) WITH (autovacuum_enabled = false);
-- the index exists before the rows: the reltuples a btree build leaves in the table differs between PostgreSQL 17 (the rows) and 18 (-1), the state of the test is -1
CREATE INDEX lk_cover ON lk (parent, status) INCLUDE (label, weight);
INSERT INTO lk SELECT i, 1 + i % 3000, (i / 3000) % 5, 'l' || (i % 3000 % 3), i % 4 FROM generate_series(0, 149999) i;
SELECT c.relname, c.reltuples, c.relallvisible FROM pg_class c WHERE c.relname IN ('ev', 'lk') ORDER BY 1;
 relname | reltuples | relallvisible 
---------+-----------+---------------
 ev      |    300000 |             0
 lk      |        -1 |             0
(2 rows)

CREATE FUNCTION probes(q text) RETURNS bool LANGUAGE plpgsql AS $$
DECLARE line text;
BEGIN
	FOR line IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP
		IF line LIKE '%Lookup Joins:%' THEN RETURN true; END IF;
	END LOOP;
	RETURN false;
END $$;
CREATE TABLE shapes (name text, q text);
INSERT INTO shapes VALUES
 ('not_in', $q$SELECT ev.v, count(*) FROM ev WHERE ev.fk BETWEEN 100 AND 159 AND ev.fk NOT IN (SELECT lk.parent FROM lk WHERE lk.status = 4 AND lk.label = 'l1') GROUP BY 1 ORDER BY 1$q$),
 ('anti', $q$SELECT ev.v, count(*) FROM ev WHERE ev.fk BETWEEN 100 AND 159 AND NOT EXISTS (SELECT 1 FROM lk WHERE lk.parent = ev.fk AND lk.status = 4 AND lk.label = 'l1') GROUP BY 1 ORDER BY 1$q$),
 ('semi', $q$SELECT ev.v, count(*) FROM ev WHERE ev.fk BETWEEN 100 AND 159 AND EXISTS (SELECT 1 FROM lk WHERE lk.parent = ev.fk AND lk.status = 4 AND lk.label = 'l1') GROUP BY 1 ORDER BY 1$q$),
 ('lateral', $q$SELECT ev.v, count(*) FROM ev JOIN LATERAL (SELECT 1 FROM lk WHERE lk.parent = ev.fk AND lk.status = 0 LIMIT 1) x ON true WHERE ev.v < 5 GROUP BY 1 ORDER BY 1$q$),
 ('join', $q$SELECT lk.label, count(*), sum(ev.v) FROM ev JOIN lk ON lk.parent = ev.fk AND lk.status = 2 WHERE ev.fk BETWEEN 100 AND 159 GROUP BY 1 ORDER BY 1$q$);
-- never analyzed, never vacuumed
SELECT name, probes(q) AS probed FROM shapes ORDER BY name;
  name   | probed 
---------+--------
 anti    | t
 join    | t
 lateral | t
 not_in  | t
 semi    | t
(5 rows)

-- analyzed, not vacuumed (nothing all-visible)
ANALYZE ev, lk;
SELECT c.relname, c.relallvisible FROM pg_class c WHERE c.relname IN ('ev', 'lk') ORDER BY 1;
 relname | relallvisible 
---------+---------------
 ev      |             0
 lk      |             0
(2 rows)

SELECT name, probes(q) AS probed FROM shapes ORDER BY name;
  name   | probed 
---------+--------
 anti    | t
 join    | t
 lateral | t
 not_in  | t
 semi    | t
(5 rows)

-- vacuumed
VACUUM (FREEZE, ANALYZE) ev, lk;
SELECT name, probes(q) AS probed FROM shapes ORDER BY name;
  name   | probed 
---------+--------
 anti    | t
 join    | t
 lateral | t
 not_in  | t
 semi    | t
(5 rows)

DROP FUNCTION probes(text);
DROP TABLE shapes, ev, lk;
DROP EXTENSION pg_sift;