-- A table with several sift_col indexes is scanned through the first of them that gives a plan: each index holds its own columns and paths, and the one
-- PostgreSQL lists first is no better a choice than the others for a query it cannot answer. The results, with the pushdown off and on, are the same.
CREATE EXTENSION pg_sift;
SET max_parallel_workers_per_gather = 0;
SET seq_page_cost = 1000;
SET random_page_cost = 1000;
\ir test/agg_helpers.sql
\set ECHO none
\ir test/agg_same_helpers.sql
\set ECHO none
-- the index the pushdown scans, or native when the plan has no SiftAgg
CREATE FUNCTION used_index(q text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE l text;
BEGIN
	PERFORM set_config('sift.enable_agg', 'on', true);
	FOR l IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP
		IF l ~ '^\s*Index: ' THEN RETURN btrim(l); END IF;
	END LOOP;
	RETURN 'native';
END $$;
CREATE TABLE mi (id int PRIMARY KEY, grp int, amount float8, doc jsonb);
INSERT INTO mi SELECT i, i % 7, i % 100, jsonb_build_object('n', i % 13, 'kind', 'k' || i % 3) FROM generate_series(1, 20000) i;
CREATE INDEX mi_amount ON mi USING sift_col (id, grp, amount);
NOTICE:  sift_col index "mi_amount": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('mi_amount');
CREATE INDEX mi_doc ON mi USING sift_col (id, grp, doc) WITH (paths = 'doc.n float8, doc.kind text');
NOTICE:  sift_col index "mi_doc": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('mi_doc');
VACUUM ANALYZE mi;
-- each query only one of the two indexes can answer, whichever the planner lists first
SELECT used_index('SELECT grp, sum(amount) FROM mi GROUP BY grp') AS amount_only;
   amount_only    
------------------
 Index: mi_amount
(1 row)

SELECT same('SELECT grp, sum(amount), count(*) FROM mi GROUP BY grp') AS amount_only_same;
 amount_only_same 
------------------
 same
(1 row)

SELECT used_index('SELECT grp, sum((doc ->> $$n$$)::float8) FROM mi GROUP BY grp') AS doc_only;
   doc_only    
---------------
 Index: mi_doc
(1 row)

SELECT same('SELECT grp, sum((doc ->> $$n$$)::float8), count(*) FROM mi GROUP BY grp') AS doc_only_same;
 doc_only_same 
---------------
 same
(1 row)

SELECT used_index('SELECT doc ->> $$kind$$, count(*) FROM mi WHERE grp < 4 GROUP BY 1') AS doc_key;
    doc_key    
---------------
 Index: mi_doc
(1 row)

SELECT same('SELECT doc ->> $$kind$$, count(*) FROM mi WHERE grp < 4 GROUP BY 1') AS doc_key_same;
 doc_key_same 
--------------
 same
(1 row)

-- both indexes hold the columns: the first is scanned, the answer is the same
SELECT used_index('SELECT grp, count(*) FROM mi GROUP BY grp') IN ('Index: mi_amount', 'Index: mi_doc') AS both_hold;
 both_hold 
-----------
 t
(1 row)

SELECT same('SELECT grp, count(*) FROM mi GROUP BY grp') AS both_hold_same;
 both_hold_same 
----------------
 same
(1 row)

-- no index holds both columns: the normal plan
SELECT used_index('SELECT grp, sum(amount), sum((doc ->> $$n$$)::float8) FROM mi GROUP BY grp') AS neither;
 neither 
---------
 native
(1 row)

SELECT same('SELECT grp, sum(amount), sum((doc ->> $$n$$)::float8) FROM mi GROUP BY grp') AS neither_same;
 neither_same 
--------------
 same
(1 row)

-- a join to a dimension table keeps its lookup on either index
CREATE TABLE mi_dim (id int PRIMARY KEY, label text);
INSERT INTO mi_dim SELECT g, 'g' || g FROM generate_series(0, 6) g;
ANALYZE mi_dim;
SELECT used_index('SELECT d.label, sum(m.amount) FROM mi m JOIN mi_dim d ON d.id = m.grp GROUP BY 1') AS joined_amount;
  joined_amount   
------------------
 Index: mi_amount
(1 row)

SELECT same('SELECT d.label, sum(m.amount) FROM mi m JOIN mi_dim d ON d.id = m.grp GROUP BY 1') AS joined_amount_same;
 joined_amount_same 
--------------------
 same
(1 row)

SELECT used_index('SELECT d.label, sum((m.doc ->> $$n$$)::float8) FROM mi m JOIN mi_dim d ON d.id = m.grp GROUP BY 1') AS joined_doc;
  joined_doc   
---------------
 Index: mi_doc
(1 row)

SELECT same('SELECT d.label, sum((m.doc ->> $$n$$)::float8) FROM mi m JOIN mi_dim d ON d.id = m.grp GROUP BY 1') AS joined_doc_same;
 joined_doc_same 
-----------------
 same
(1 row)

-- rows written after the build, and the index that went away
INSERT INTO mi SELECT i, i % 7, i % 100, jsonb_build_object('n', i % 13, 'kind', 'k' || i % 3) FROM generate_series(20001, 20500) i;
SELECT same('SELECT grp, sum(amount), count(*) FROM mi GROUP BY grp') AS amount_buffer_same;
 amount_buffer_same 
--------------------
 same
(1 row)

SELECT same('SELECT grp, sum((doc ->> $$n$$)::float8), count(*) FROM mi GROUP BY grp') AS doc_buffer_same;
 doc_buffer_same 
-----------------
 same
(1 row)

DROP INDEX mi_doc;
SELECT used_index('SELECT grp, sum(amount) FROM mi GROUP BY grp') AS after_drop;
    after_drop    
------------------
 Index: mi_amount
(1 row)

SELECT used_index('SELECT grp, sum((doc ->> $$n$$)::float8) FROM mi GROUP BY grp') AS after_drop_doc;
 after_drop_doc 
----------------
 native
(1 row)

DROP FUNCTION same(text);
DROP FUNCTION runq(text, bool);
DROP FUNCTION used_index(text), outcome, pushed, runs_in_scan;
DROP TABLE mi, mi_dim;
DROP EXTENSION pg_sift;