-- CASE: Gather Merge

CREATE TABLE t1 (id int, s text);
CREATE TABLE t2 (id int primary key);
INSERT INTO t1 SELECT g, CASE WHEN g = 10 THEN 'x' ELSE g::text END
  FROM generate_series(1, 10) g;
INSERT INTO t2 SELECT generate_series(1, 9);
ANALYZE t1, t2;

SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET min_parallel_table_scan_size = 0;

EXPLAIN (COSTS OFF)
SELECT t1.s::int FROM t1 JOIN t2 USING (id)
ORDER BY t1.s::int
LIMIT 1;

SELECT t1.s::int FROM t1 JOIN t2 USING (id)
ORDER BY t1.s::int
LIMIT 1;


-- CASE: Merge Append

CREATE TABLE p (id int, s text) PARTITION BY RANGE (id);
CREATE TABLE p1 PARTITION of p FOR VALUES FROM (1) TO (6);
CREATE TABLE p2 PARTITION of p FOR VALUES FROM (6) TO (11);

INSERT INTO p (id,s)
  SELECT g, CASE WHEN g = 10 THEN 'x' ELSE g::text END
  FROM generate_series(1, 10) g;
CREATE INDEX ON p1 ((s::int));
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (id int PRIMARY KEY);
INSERT INTO t2 SELECT generate_series(1, 9);

ANALYZE p, t2;

EXPLAIN (COSTS OFF)
SELECT p.s::int FROM p JOIN t2 USING (id) ORDER BY p.s::int LIMIT 1;

SELECT p.s::int FROM p JOIN t2 USING (id) ORDER BY p.s::int LIMIT 1;


-- CASE: Eager Aggregation

CREATE TABLE a (id int PRIMARY KEY, g int);
CREATE TABLE b (id int, s text);
INSERT INTO a SELECT g, g % 2 FROM generate_series(1, 10) g;
INSERT INTO b SELECT g % 10 + 1, '1' FROM generate_series(1, 100) g;
INSERT INTO b VALUES (11, 'x');

ANALYZE a, b;

EXPLAIN (COSTS OFF)
SELECT a.g, sum(b.s::int) FROM a JOIN b USING (id) GROUP BY a.g;

SELECT a.g, sum(b.s::int) FROM a JOIN b USING (id) GROUP BY a.g;