-- pg_orca base regression test -- Tests: SELECT, WHERE, GROUP BY, JOIN, subqueries, CTE, UNION, EXPLAIN LOAD 'pg_orca'; set pg_orca.enable_orca to off; -- Create TPC-H schema tables (replaces pg_tpch dependency) CREATE TABLE nation ( n_nationkey INT NOT NULL, n_name TEXT NOT NULL, n_regionkey INT NOT NULL, n_comment TEXT ); CREATE TABLE customer ( c_custkey INT NOT NULL, c_name TEXT NOT NULL, c_address TEXT NOT NULL, c_nationkey INT NOT NULL, c_phone TEXT NOT NULL, c_acctbal FLOAT NOT NULL, c_mktsegment TEXT NOT NULL, c_comment TEXT NOT NULL ); CREATE TABLE orders ( o_orderkey INT NOT NULL, o_custkey INT NOT NULL, o_orderstatus TEXT NOT NULL, o_totalprice FLOAT NOT NULL, o_orderdate DATE NOT NULL, o_orderpriority TEXT NOT NULL, o_clerk TEXT NOT NULL, o_shippriority INT NOT NULL, o_comment TEXT NOT NULL ); -- Additional test tables CREATE TABLE product ( pn INT NOT NULL, pname TEXT NOT NULL, pcolor TEXT, PRIMARY KEY (pn) ); CREATE TABLE sale ( cn INT NOT NULL, vn INT NOT NULL, pn INT NOT NULL, dt DATE NOT NULL, qty INT NOT NULL, prc FLOAT NOT NULL, PRIMARY KEY (cn, vn, pn) ); CREATE TABLE test_table (a INT, b INT); ALTER TABLE test_table ADD COLUMN c INT; EXPLAIN (COSTS OFF) SELECT * FROM test_table; QUERY PLAN ------------------------ Seq Scan on test_table (1 row) ALTER TABLE test_table DROP COLUMN c; ALTER TABLE test_table ADD COLUMN c INT; -- Enable ORCA SET pg_orca.enable_orca TO on; -- RTE_RESULT VALUES(1,2); column1 | column2 ---------+--------- 1 | 2 (1 row) -- RTE_VALUES VALUES(1,2),(3,4); column1 | column2 ---------+--------- 1 | 2 3 | 4 (2 rows) -- Basic SELECT EXPLAIN (COSTS OFF, VERBOSE) SELECT FROM orders; QUERY PLAN --------------------------- Seq Scan on public.orders Optimizer: pg_orca (2 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM orders; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Seq Scan on public.orders Output: o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment Optimizer: pg_orca (3 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT o_custkey FROM orders; QUERY PLAN --------------------------- Seq Scan on public.orders Output: o_custkey Optimizer: pg_orca (3 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT o_custkey, o_custkey FROM orders; QUERY PLAN -------------------------------- Seq Scan on public.orders Output: o_custkey, o_custkey Optimizer: pg_orca (3 rows) SELECT n_regionkey AS x, n_regionkey AS y FROM nation; x | y ---+--- (0 rows) SELECT; -- (1 row) SELECT 1, '1', 2 AS x, 'xx' AS x; ?column? | ?column? | x | x ----------+----------+---+---- 1 | 1 | 2 | xx (1 row) EXPLAIN (COSTS OFF, VERBOSE) SELECT 1 + 1; QUERY PLAN ------------- Result Output: 2 (2 rows) SELECT 1, 1 + 1, true, null, array[1, 2, 3], array[[1], [2], [3]], '[1, 2, 3]'; ?column? | ?column? | ?column? | ?column? | array | array | ?column? ----------+----------+----------+----------+---------+---------------+----------- 1 | 2 | t | | {1,2,3} | {{1},{2},{3}} | [1, 2, 3] (1 row) SELECT 1::text; text ------ 1 (1 row) SELECT '{1,2,3}'::integer[], 1::text, 1::int, 'a'::text, '99999999'::int; int4 | text | int4 | text | int4 ---------+------+------+------+---------- {1,2,3} | 1 | 1 | a | 99999999 (1 row) EXPLAIN (COSTS OFF, VERBOSE) SELECT 1+1 = 3 * 10 AND 2 > 1 OR 1 IS NULL WHERE 1=1; QUERY PLAN ----------------- Result Output: false (2 rows) -- WHERE EXPLAIN (COSTS OFF, VERBOSE) SELECT o_orderkey FROM orders WHERE o_custkey > 10; QUERY PLAN ----------------------------------- Seq Scan on public.orders Output: o_orderkey Filter: (orders.o_custkey > 10) Optimizer: pg_orca (4 rows) -- Column aliases with WHERE EXPLAIN (COSTS OFF, VERBOSE) SELECT n_regionkey AS x, n_regionkey AS y FROM nation WHERE n_regionkey < 10; QUERY PLAN ------------------------------------------- Result Output: n_regionkey, n_regionkey -> Seq Scan on public.nation Output: n_regionkey Filter: (nation.n_regionkey < 10) Optimizer: pg_orca (6 rows) -- IN-list constant EXPLAIN (COSTS OFF, VERBOSE) SELECT 1 WHERE 1 IN (2, 3); QUERY PLAN -------------------------- Result Output: 1 One-Time Filter: false (3 rows) -- Expressions EXPLAIN (COSTS OFF, VERBOSE) SELECT n_regionkey AS x, n_regionkey + 1 AS y FROM nation; QUERY PLAN ------------------------------------------ Result Output: n_regionkey, (n_regionkey + 1) -> Seq Scan on public.nation Output: n_regionkey Optimizer: pg_orca (5 rows) -- generate_series EXPLAIN (COSTS OFF, VERBOSE) SELECT 1 FROM generate_series(1,10); QUERY PLAN --------------------------------------------------- Result Output: 1 -> Function Scan on pg_catalog.generate_series Output: generate_series Function Call: generate_series(1, 10) Optimizer: pg_orca (6 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT g FROM generate_series(1,10) g; QUERY PLAN ----------------------------------------------- Function Scan on pg_catalog.generate_series g Output: g Function Call: generate_series(1, 10) Optimizer: pg_orca (4 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT g + 1 FROM generate_series(1,10) g; QUERY PLAN ----------------------------------------------------- Result Output: (g + 1) -> Function Scan on pg_catalog.generate_series g Output: g Function Call: generate_series(1, 10) Optimizer: pg_orca (6 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT g + 1 AS x FROM generate_series(1,10) g WHERE 1 < 10; QUERY PLAN ----------------------------------------------------- Result Output: (g + 1) -> Function Scan on pg_catalog.generate_series g Output: g Function Call: generate_series(1, 10) Optimizer: pg_orca (6 rows) -- LIMIT EXPLAIN (COSTS OFF, VERBOSE) SELECT n_regionkey AS x, n_regionkey + 1 AS y FROM nation LIMIT 10; QUERY PLAN ------------------------------------------------ Limit Output: n_regionkey, ((n_regionkey + 1)) -> Result Output: n_regionkey, (n_regionkey + 1) -> Seq Scan on public.nation Output: n_regionkey Optimizer: pg_orca (7 rows) -- Arithmetic EXPLAIN (COSTS OFF, VERBOSE) SELECT o_totalprice + 1, o_totalprice - 1, o_totalprice * 1, o_totalprice / 1 FROM orders WHERE o_orderkey = 1000 AND o_shippriority + 1 > 10; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Result Output: (o_totalprice + '1'::double precision), (o_totalprice - '1'::double precision), (o_totalprice * '1'::double precision), (o_totalprice / '1'::double precision) -> Seq Scan on public.orders Output: o_totalprice Filter: ((orders.o_orderkey = 1000) AND ((orders.o_shippriority + 1) > 10)) Optimizer: pg_orca (6 rows) -- ORDER BY + LIMIT (index scan tests) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM nation ORDER BY 1 LIMIT 10; QUERY PLAN ------------------------------------------------------------------- Limit Output: n_nationkey, n_name, n_regionkey, n_comment -> Sort Output: n_nationkey, n_name, n_regionkey, n_comment Sort Key: nation.n_nationkey -> Seq Scan on public.nation Output: n_nationkey, n_name, n_regionkey, n_comment Optimizer: pg_orca (8 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM nation ORDER BY 2 LIMIT 10; QUERY PLAN ------------------------------------------------------------------- Limit Output: n_nationkey, n_name, n_regionkey, n_comment -> Sort Output: n_nationkey, n_name, n_regionkey, n_comment Sort Key: nation.n_name -> Seq Scan on public.nation Output: n_nationkey, n_name, n_regionkey, n_comment Optimizer: pg_orca (8 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM nation ORDER BY 3 LIMIT 10; QUERY PLAN ------------------------------------------------------------------- Limit Output: n_nationkey, n_name, n_regionkey, n_comment -> Sort Output: n_nationkey, n_name, n_regionkey, n_comment Sort Key: nation.n_regionkey -> Seq Scan on public.nation Output: n_nationkey, n_name, n_regionkey, n_comment Optimizer: pg_orca (8 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM nation ORDER BY 4 LIMIT 10; QUERY PLAN ------------------------------------------------------------------- Limit Output: n_nationkey, n_name, n_regionkey, n_comment -> Sort Output: n_nationkey, n_name, n_regionkey, n_comment Sort Key: nation.n_comment -> Seq Scan on public.nation Output: n_nationkey, n_name, n_regionkey, n_comment Optimizer: pg_orca (8 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM orders ORDER BY 1 DESC LIMIT 10; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------- Limit Output: o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment -> Sort Output: o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment Sort Key: orders.o_orderkey DESC -> Seq Scan on public.orders Output: o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment Optimizer: pg_orca (8 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM orders ORDER BY 1 DESC, 2 ASC LIMIT 10; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------- Limit Output: o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment -> Sort Output: o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment Sort Key: orders.o_orderkey DESC, orders.o_custkey -> Seq Scan on public.orders Output: o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment Optimizer: pg_orca (8 rows) -- Composite index equality prefix: leading key fixed by equality predicate -- should allow the scan to satisfy ORDER BY on trailing key without Sort. CREATE TABLE boolindex (b bool, i int); CREATE UNIQUE INDEX boolindex_b_i_key ON boolindex(b, i); -- b is fixed by equality => index output is ordered by i => no Sort node EXPLAIN (costs off) SELECT * FROM boolindex WHERE b ORDER BY i LIMIT 10; QUERY PLAN ------------------------------------------------------------ Limit -> Index Only Scan using boolindex_b_i_key on boolindex Index Cond: (b = true) Optimizer: pg_orca (4 rows) -- range predicate on b => b not fixed => Sort still needed EXPLAIN (costs off) SELECT * FROM boolindex WHERE b > false ORDER BY i LIMIT 10; QUERY PLAN ---------------------------------------------------------- Limit -> Sort Sort Key: i -> Bitmap Heap Scan on boolindex Recheck Cond: (b > false) -> Bitmap Index Scan on boolindex_b_i_key Index Cond: (b > false) Optimizer: pg_orca (8 rows) -- ORDER BY matches full index key order => no Sort EXPLAIN (costs off) SELECT * FROM boolindex ORDER BY b, i LIMIT 10; QUERY PLAN ------------------------------------------------------------ Limit -> Index Only Scan using boolindex_b_i_key on boolindex Optimizer: pg_orca (3 rows) DROP TABLE boolindex; -- Basic filter EXPLAIN (COSTS OFF, VERBOSE) SELECT o_totalprice + 1 FROM orders WHERE o_orderkey = 1000 AND o_shippriority + 1 < 10; QUERY PLAN ------------------------------------------------------------------------------------- Result Output: (o_totalprice + '1'::double precision) -> Seq Scan on public.orders Output: o_totalprice Filter: ((orders.o_orderkey = 1000) AND ((orders.o_shippriority + 1) < 10)) Optimizer: pg_orca (6 rows) -- IN-list EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM customer WHERE c_custkey IN (1,2,2,3,123,34,345,453,56,567,23,213); QUERY PLAN -------------------------------------------------------------------------------------------------- Seq Scan on public.customer Output: c_custkey, c_name, c_address, c_nationkey, c_phone, c_acctbal, c_mktsegment, c_comment Filter: (customer.c_custkey = ANY ('{1,2,2,3,123,34,345,453,56,567,23,213}'::integer[])) Optimizer: pg_orca (4 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM customer WHERE c_custkey IN (1,2,2,3,123,34,345,453,56,567,23,213) OR 1+1=2; QUERY PLAN -------------------------------------------------------------------------------------------------- Seq Scan on public.customer Output: c_custkey, c_name, c_address, c_nationkey, c_phone, c_acctbal, c_mktsegment, c_comment Optimizer: pg_orca (3 rows) -- Aggregates EXPLAIN (COSTS OFF, VERBOSE) SELECT sum(n_regionkey) FROM nation; QUERY PLAN --------------------------------- Aggregate Output: sum(n_regionkey) -> Seq Scan on public.nation Output: n_regionkey Optimizer: pg_orca (5 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT sum(n_regionkey) FROM nation GROUP BY n_name; QUERY PLAN ------------------------------------------- Result Output: (sum(n_regionkey)) -> HashAggregate Output: sum(n_regionkey), n_name Group Key: nation.n_name -> Seq Scan on public.nation Output: n_name, n_regionkey Optimizer: pg_orca (8 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT o_orderkey, sum(o_custkey) FROM orders GROUP BY o_orderkey ORDER BY o_orderkey; QUERY PLAN --------------------------------------------- Sort Output: o_orderkey, (sum(o_custkey)) Sort Key: orders.o_orderkey -> HashAggregate Output: o_orderkey, sum(o_custkey) Group Key: orders.o_orderkey -> Seq Scan on public.orders Output: o_orderkey, o_custkey Optimizer: pg_orca (9 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT o_orderkey, sum(o_custkey) FROM orders GROUP BY o_orderkey ORDER BY o_orderkey LIMIT 10; QUERY PLAN --------------------------------------------------- Limit Output: o_orderkey, (sum(o_custkey)) -> Sort Output: o_orderkey, (sum(o_custkey)) Sort Key: orders.o_orderkey -> HashAggregate Output: o_orderkey, sum(o_custkey) Group Key: orders.o_orderkey -> Seq Scan on public.orders Output: o_orderkey, o_custkey Optimizer: pg_orca (11 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT o_orderkey, sum(o_custkey + o_orderkey) FROM orders GROUP BY o_orderkey ORDER BY o_orderkey LIMIT 10; QUERY PLAN ----------------------------------------------------------------- Limit Output: o_orderkey, (sum((o_custkey + o_orderkey))) -> Sort Output: o_orderkey, (sum((o_custkey + o_orderkey))) Sort Key: orders.o_orderkey -> HashAggregate Output: o_orderkey, sum((o_custkey + o_orderkey)) Group Key: orders.o_orderkey -> Seq Scan on public.orders Output: o_orderkey, o_custkey Optimizer: pg_orca (11 rows) -- UNION / INTERSECT / EXCEPT EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM nation UNION SELECT * FROM nation ORDER BY 2 LIMIT 10; QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Limit Output: nation.n_nationkey, nation.n_name, nation.n_regionkey, nation.n_comment -> Sort Output: nation.n_nationkey, nation.n_name, nation.n_regionkey, nation.n_comment Sort Key: nation.n_name -> HashAggregate Output: nation.n_nationkey, nation.n_name, nation.n_regionkey, nation.n_comment Group Key: nation.n_nationkey, nation.n_name, nation.n_regionkey, nation.n_comment -> Append -> Seq Scan on public.nation Output: nation.n_nationkey, nation.n_name, nation.n_regionkey, nation.n_comment -> Seq Scan on public.nation nation_1 Output: nation_1.n_nationkey, nation_1.n_name, nation_1.n_regionkey, nation_1.n_comment Optimizer: pg_orca (14 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM nation UNION ALL SELECT * FROM nation ORDER BY 2 LIMIT 10; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Limit Output: nation.n_nationkey, nation.n_name, nation.n_regionkey, nation.n_comment -> Sort Output: nation.n_nationkey, nation.n_name, nation.n_regionkey, nation.n_comment Sort Key: nation.n_name -> Append -> Seq Scan on public.nation Output: nation.n_nationkey, nation.n_name, nation.n_regionkey, nation.n_comment -> Seq Scan on public.nation nation_1 Output: nation_1.n_nationkey, nation_1.n_name, nation_1.n_regionkey, nation_1.n_comment Optimizer: pg_orca (11 rows) EXPLAIN (COSTS OFF) SELECT * FROM nation EXCEPT SELECT * FROM nation ORDER BY 3 LIMIT 10; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit -> Sort Sort Key: nation.n_regionkey -> HashAggregate Group Key: nation.n_nationkey, nation.n_name, nation.n_regionkey, nation.n_comment -> Hash Anti Join Hash Cond: ((NOT (nation.n_nationkey IS DISTINCT FROM nation_1.n_nationkey)) AND (NOT (nation.n_name IS DISTINCT FROM nation_1.n_name)) AND (NOT (nation.n_regionkey IS DISTINCT FROM nation_1.n_regionkey)) AND (NOT (nation.n_comment IS DISTINCT FROM nation_1.n_comment))) -> Seq Scan on nation -> Hash -> Seq Scan on nation nation_1 Optimizer: pg_orca (11 rows) EXPLAIN (COSTS OFF) SELECT * FROM nation INTERSECT SELECT * FROM nation ORDER BY 3 LIMIT 10; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit -> Sort Sort Key: nation.n_regionkey -> Hash Join Hash Cond: ((NOT (nation.n_nationkey IS DISTINCT FROM nation_1.n_nationkey)) AND (NOT (nation.n_name IS DISTINCT FROM nation_1.n_name)) AND (NOT (nation.n_regionkey IS DISTINCT FROM nation_1.n_regionkey)) AND (NOT (nation.n_comment IS DISTINCT FROM nation_1.n_comment))) -> HashAggregate Group Key: nation.n_nationkey, nation.n_name, nation.n_regionkey, nation.n_comment -> Seq Scan on nation -> Hash -> HashAggregate Group Key: nation_1.n_nationkey, nation_1.n_name, nation_1.n_regionkey, nation_1.n_comment -> Seq Scan on nation nation_1 Optimizer: pg_orca (13 rows) -- JOINs EXPLAIN (COSTS OFF) SELECT * FROM orders JOIN nation ON orders.o_custkey = nation.n_regionkey; QUERY PLAN ------------------------------------------------------- Merge Join Merge Cond: (orders.o_custkey = nation.n_regionkey) -> Sort Sort Key: orders.o_custkey -> Seq Scan on orders -> Sort Sort Key: nation.n_regionkey -> Seq Scan on nation Optimizer: pg_orca (9 rows) EXPLAIN (COSTS OFF) SELECT * FROM orders JOIN nation ON orders.o_custkey = nation.n_regionkey AND nation.n_regionkey = 1; QUERY PLAN ------------------------------------------------------- Merge Join Merge Cond: (orders.o_custkey = nation.n_regionkey) -> Sort Sort Key: orders.o_custkey -> Seq Scan on orders Filter: (o_custkey = 1) -> Sort Sort Key: nation.n_regionkey -> Seq Scan on nation Filter: (n_regionkey = 1) Optimizer: pg_orca (11 rows) EXPLAIN (COSTS OFF) SELECT * FROM orders JOIN nation ON orders.o_custkey = nation.n_regionkey WHERE nation.n_regionkey = 1; QUERY PLAN ------------------------------------------------------- Merge Join Merge Cond: (orders.o_custkey = nation.n_regionkey) -> Sort Sort Key: orders.o_custkey -> Seq Scan on orders Filter: (o_custkey = 1) -> Sort Sort Key: nation.n_regionkey -> Seq Scan on nation Filter: (n_regionkey = 1) Optimizer: pg_orca (11 rows) -- LEFT JOIN EXPLAIN (COSTS OFF) SELECT * FROM orders LEFT JOIN nation ON orders.o_custkey = nation.n_regionkey; QUERY PLAN ------------------------------------------------------ Hash Left Join Hash Cond: (orders.o_custkey = nation.n_regionkey) -> Seq Scan on orders -> Hash -> Seq Scan on nation Optimizer: pg_orca (6 rows) EXPLAIN (COSTS OFF) SELECT * FROM orders LEFT JOIN nation ON orders.o_custkey = nation.n_regionkey AND nation.n_regionkey = 1; QUERY PLAN ------------------------------------------------------ Hash Left Join Hash Cond: (orders.o_custkey = nation.n_regionkey) -> Seq Scan on orders -> Hash -> Seq Scan on nation Filter: (n_regionkey = 1) Optimizer: pg_orca (7 rows) EXPLAIN (COSTS OFF) SELECT * FROM orders LEFT JOIN nation ON orders.o_custkey = nation.n_regionkey WHERE nation.n_regionkey = 1; QUERY PLAN ------------------------------------------------------- Merge Join Merge Cond: (orders.o_custkey = nation.n_regionkey) -> Sort Sort Key: orders.o_custkey -> Seq Scan on orders Filter: (o_custkey = 1) -> Sort Sort Key: nation.n_regionkey -> Seq Scan on nation Filter: (n_regionkey = 1) Optimizer: pg_orca (11 rows) -- Subqueries (IN / EXISTS) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM customer WHERE c_custkey IN (SELECT c_custkey FROM nation); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on public.customer Output: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment Filter: ((customer.c_custkey = customer.c_custkey) AND EXISTS(SubPlan 1)) SubPlan 1 -> Limit Output: nation.ctid -> Seq Scan on public.nation Output: nation.ctid Optimizer: pg_orca (9 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM customer x WHERE c_custkey IN (SELECT x.c_nationkey FROM nation); QUERY PLAN ------------------------------------------------------------------------------------------------------------------ Seq Scan on public.customer x Output: x.c_custkey, x.c_name, x.c_address, x.c_nationkey, x.c_phone, x.c_acctbal, x.c_mktsegment, x.c_comment Filter: ((x.c_custkey = x.c_nationkey) AND EXISTS(SubPlan 1)) SubPlan 1 -> Limit Output: nation.ctid -> Seq Scan on public.nation Output: nation.ctid Optimizer: pg_orca (9 rows) -- Self-join (non-equi) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM customer a JOIN customer b ON a.c_custkey != b.c_custkey; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop Output: a.c_custkey, a.c_name, a.c_address, a.c_nationkey, a.c_phone, a.c_acctbal, a.c_mktsegment, a.c_comment, b.c_custkey, b.c_name, b.c_address, b.c_nationkey, b.c_phone, b.c_acctbal, b.c_mktsegment, b.c_comment Join Filter: (a.c_custkey <> b.c_custkey) -> Seq Scan on public.customer a Output: a.c_custkey, a.c_name, a.c_address, a.c_nationkey, a.c_phone, a.c_acctbal, a.c_mktsegment, a.c_comment -> Seq Scan on public.customer b Output: b.c_custkey, b.c_name, b.c_address, b.c_nationkey, b.c_phone, b.c_acctbal, b.c_mktsegment, b.c_comment Optimizer: pg_orca (8 rows) -- Nested subquery EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM customer WHERE customer.c_custkey IN (SELECT customer.c_nationkey + 1 FROM ( SELECT * FROM nation WHERE nation.n_nationkey IN (SELECT nation.n_nationkey + 1 FROM orders) ) i); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop Output: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment Join Filter: ((count(*)) > '0'::bigint) -> Aggregate Output: count(*) -> HashAggregate Output: nation.ctid Group Key: nation.ctid -> Nested Loop Output: nation.ctid Join Filter: true -> Limit -> Seq Scan on public.orders -> Materialize Output: nation.ctid -> Seq Scan on public.nation Output: nation.ctid Filter: (nation.n_nationkey = (nation.n_nationkey + 1)) -> Materialize Output: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment -> Seq Scan on public.customer Output: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment Filter: (customer.c_custkey = (customer.c_nationkey + 1)) Optimizer: pg_orca (24 rows) -- CTEs EXPLAIN (COSTS OFF, VERBOSE) WITH cte AS (SELECT * FROM orders) SELECT * FROM cte; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Seq Scan on public.orders Output: o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment Optimizer: pg_orca (3 rows) EXPLAIN (COSTS OFF, VERBOSE) WITH x AS (SELECT o_custkey a1, o_custkey a2, o_custkey FROM orders) SELECT * FROM x WHERE a1 = 1 AND a2 = 2; QUERY PLAN ------------------------------------------------------- Result Output: NULL::integer, NULL::integer, NULL::integer One-Time Filter: false Optimizer: pg_orca (4 rows) EXPLAIN (COSTS OFF, VERBOSE) WITH cte AS (SELECT * FROM orders LEFT JOIN nation ON orders.o_custkey = nation.n_regionkey WHERE nation.n_regionkey = 1) SELECT * FROM cte WHERE o_orderkey = 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Merge Join Output: orders.o_orderkey, orders.o_custkey, orders.o_orderstatus, orders.o_totalprice, orders.o_orderdate, orders.o_orderpriority, orders.o_clerk, orders.o_shippriority, orders.o_comment, nation.n_nationkey, nation.n_name, nation.n_regionkey, nation.n_comment Merge Cond: (orders.o_custkey = nation.n_regionkey) -> Sort Output: orders.o_orderkey, orders.o_custkey, orders.o_orderstatus, orders.o_totalprice, orders.o_orderdate, orders.o_orderpriority, orders.o_clerk, orders.o_shippriority, orders.o_comment Sort Key: orders.o_custkey -> Seq Scan on public.orders Output: orders.o_orderkey, orders.o_custkey, orders.o_orderstatus, orders.o_totalprice, orders.o_orderdate, orders.o_orderpriority, orders.o_clerk, orders.o_shippriority, orders.o_comment Filter: ((orders.o_custkey = 1) AND (orders.o_custkey = 1) AND (orders.o_orderkey = 1)) -> Sort Output: nation.n_nationkey, nation.n_name, nation.n_regionkey, nation.n_comment Sort Key: nation.n_regionkey -> Seq Scan on public.nation Output: nation.n_nationkey, nation.n_name, nation.n_regionkey, nation.n_comment Filter: ((nation.n_regionkey = 1) AND (nation.n_regionkey = 1)) Optimizer: pg_orca (16 rows) -- Semi-join: EXISTS EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM customer WHERE EXISTS (SELECT * FROM nation WHERE c_custkey = n_nationkey AND c_nationkey <> n_regionkey); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash Semi Join Output: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment Hash Cond: (customer.c_custkey = nation.n_nationkey) Join Filter: (customer.c_nationkey <> nation.n_regionkey) -> Seq Scan on public.customer Output: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment -> Hash Output: nation.n_nationkey, nation.n_regionkey -> Seq Scan on public.nation Output: nation.n_nationkey, nation.n_regionkey Optimizer: pg_orca (11 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM customer WHERE c_custkey IN (SELECT DISTINCT c_custkey FROM nation); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on public.customer Output: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment Filter: (ANY (customer.c_custkey = (SubPlan 1).col1)) SubPlan 1 -> Result Output: customer.c_custkey -> Seq Scan on public.nation Optimizer: pg_orca (8 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM customer WHERE c_custkey IN (SELECT DISTINCT n_regionkey FROM nation); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash Semi Join Output: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment Hash Cond: (customer.c_custkey = nation.n_regionkey) -> Seq Scan on public.customer Output: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment -> Hash Output: nation.n_regionkey -> Seq Scan on public.nation Output: nation.n_regionkey Optimizer: pg_orca (10 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM customer WHERE c_custkey IN (SELECT c_custkey FROM nation LIMIT 3); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on public.customer Output: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment Filter: (ANY (customer.c_custkey = (SubPlan 1).col1)) SubPlan 1 -> Limit Output: (customer.c_custkey) -> Result Output: customer.c_custkey -> Seq Scan on public.nation Optimizer: pg_orca (10 rows) -- Scalar subquery EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM customer WHERE c_custkey > (SELECT sum(n_nationkey) FROM nation); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop Output: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment Join Filter: (customer.c_custkey > (sum(nation.n_nationkey))) -> Aggregate Output: sum(nation.n_nationkey) -> Seq Scan on public.nation Output: nation.n_nationkey -> Seq Scan on public.customer Output: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment Optimizer: pg_orca (10 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM customer WHERE c_custkey IN (SELECT n_nationkey FROM nation); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash Semi Join Output: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment Hash Cond: (customer.c_custkey = nation.n_nationkey) -> Seq Scan on public.customer Output: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment -> Hash Output: nation.n_nationkey -> Seq Scan on public.nation Output: nation.n_nationkey Optimizer: pg_orca (10 rows) EXPLAIN (COSTS OFF) SELECT * FROM customer WHERE 1+c_custkey IN (SELECT c_nationkey+1 FROM nation); QUERY PLAN ------------------------------------------------------------------------- Seq Scan on customer Filter: (((1 + c_custkey) = (c_nationkey + 1)) AND EXISTS(SubPlan 1)) SubPlan 1 -> Limit -> Seq Scan on nation Optimizer: pg_orca (6 rows) -- start_ignore -- Plan shape for non-correlated `2 IN (subq)` flips between Semi Join and the -- count/case-rewrite based on minor cost-model perturbations; the result is -- semantically identical, but the EXPLAIN text isn't stable. Ignore output. EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM customer WHERE 2 IN (SELECT n_nationkey + 1 FROM nation); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Result Output: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment -> HashAggregate Output: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment, customer.ctid Group Key: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment, customer.ctid -> Nested Loop Output: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment, customer.ctid Join Filter: true -> Result Filter: (2 = ((nation.n_nationkey + 1))) -> Result Output: (nation.n_nationkey + 1) -> Seq Scan on public.nation Output: nation.n_nationkey -> Seq Scan on public.customer Output: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment, customer.ctid Optimizer: pg_orca (17 rows) -- end_ignore -- Correlated EXISTS EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM customer WHERE EXISTS(SELECT n_nationkey FROM nation WHERE nation.n_nationkey<>customer.c_custkey GROUP BY nation.n_nationkey); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop Semi Join Output: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment Join Filter: (nation.n_nationkey <> customer.c_custkey) -> Seq Scan on public.customer Output: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment -> Seq Scan on public.nation Output: nation.n_nationkey Optimizer: pg_orca (8 rows) -- Nested EXISTS EXPLAIN (COSTS OFF, VERBOSE) SELECT pn, cn, vn FROM sale s WHERE EXISTS (SELECT * FROM customer WHERE EXISTS (SELECT * FROM product WHERE pn = s.pn)); QUERY PLAN --------------------------------------------------------------------- Result Output: s.pn, s.cn, s.vn Filter: (COALESCE(((SubPlan 1)), '0'::bigint) > '0'::bigint) -> Result Output: (SubPlan 1), s.cn, s.vn, s.pn -> Seq Scan on public.sale s Output: s.cn, s.vn, s.pn SubPlan 1 -> Aggregate Output: count(*) -> Result -> HashAggregate Output: customer.ctid Group Key: customer.ctid -> Nested Loop Output: customer.ctid Join Filter: true -> Seq Scan on public.product Filter: (product.pn = s.pn) -> Seq Scan on public.customer Output: customer.ctid Optimizer: pg_orca (22 rows) EXPLAIN (COSTS OFF) SELECT pn, cn, vn FROM sale s WHERE cn IN (SELECT s.pn FROM customer WHERE cn NOT IN (SELECT pn FROM product WHERE pn = s.pn)); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Result Filter: (COALESCE(((SubPlan 1)), '0'::bigint) > '0'::bigint) -> Result -> Seq Scan on sale s Filter: (cn = pn) SubPlan 1 -> Aggregate -> Nested Loop Join Filter: true -> Result Filter: ((CASE WHEN ((sum((CASE WHEN (s.cn = product.pn) THEN 1 ELSE 0 END))) IS NULL) THEN true WHEN ((sum((CASE WHEN (product.pn IS NULL) THEN 1 ELSE 0 END))) > '0'::bigint) THEN NULL::boolean WHEN (s.cn IS NULL) THEN NULL::boolean WHEN ((sum((CASE WHEN (s.cn = product.pn) THEN 1 ELSE 0 END))) = '0'::bigint) THEN true ELSE false END) = true) -> Result -> Aggregate -> Result -> Seq Scan on product Filter: (pn = s.pn) -> Seq Scan on customer Optimizer: pg_orca (18 rows) EXPLAIN (COSTS OFF) SELECT * FROM customer WHERE EXISTS (SELECT 1 FROM nation WHERE nation.n_nationkey = customer.c_custkey AND customer.c_custkey > 1); QUERY PLAN -------------------------------------------------------- Hash Semi Join Hash Cond: (customer.c_custkey = nation.n_nationkey) -> Result Filter: (customer.c_custkey > 1) -> Seq Scan on customer Filter: (c_custkey > 1) -> Hash -> Result -> Seq Scan on nation Filter: (n_nationkey > 1) Optimizer: pg_orca (11 rows) -- Actual query execution with aggregate SELECT o_orderkey, sum(o_custkey + o_orderkey)/20 FROM orders GROUP BY o_orderkey ORDER BY o_orderkey LIMIT 10; o_orderkey | ?column? ------------+---------- (0 rows) -- Correlated subquery with execution SELECT * FROM orders WHERE EXISTS (SELECT 1 FROM nation WHERE nation.n_regionkey = orders.o_custkey AND nation.n_regionkey = 10); o_orderkey | o_custkey | o_orderstatus | o_totalprice | o_orderdate | o_orderpriority | o_clerk | o_shippriority | o_comment ------------+-----------+---------------+--------------+-------------+-----------------+---------+----------------+----------- (0 rows) SELECT *, (SELECT 2 FROM nation WHERE nation.n_regionkey = orders.o_custkey AND nation.n_regionkey = 10) FROM orders WHERE EXISTS (SELECT 1 FROM nation WHERE nation.n_regionkey = orders.o_custkey AND nation.n_regionkey = 10); o_orderkey | o_custkey | o_orderstatus | o_totalprice | o_orderdate | o_orderpriority | o_clerk | o_shippriority | o_comment | ?column? ------------+-----------+---------------+--------------+-------------+-----------------+---------+----------------+-----------+---------- (0 rows) -- Cleanup: drop tables created in this test so subsequent tests start clean. DROP TABLE IF EXISTS nation, customer, orders, product, sale, test_table, boolindex CASCADE; NOTICE: table "boolindex" does not exist, skipping