-- pg_orca static partition selection regression tests -- Ported from testrepo/partitioning/staticselection/sql/ (static_selection_1..7) -- MPP-24709, GPSQL-2879: Static partition pruning with RANGE partitioning LOAD 'pg_orca'; SET pg_orca.enable_orca = on; SET client_min_messages = warning; SET enable_mergejoin TO off; -- =================================================================== -- Setup: foo(a int, b int, c int) RANGE partitioned on b -- 10 partitions: [1,11), [11,21), ..., [91,101) -- =================================================================== DROP TABLE IF EXISTS foo CASCADE; CREATE TABLE foo (a int, b int, c int) PARTITION BY RANGE (b); CREATE TABLE foo_p1 PARTITION OF foo FOR VALUES FROM (1) TO (11); CREATE TABLE foo_p2 PARTITION OF foo FOR VALUES FROM (11) TO (21); CREATE TABLE foo_p3 PARTITION OF foo FOR VALUES FROM (21) TO (31); CREATE TABLE foo_p4 PARTITION OF foo FOR VALUES FROM (31) TO (41); CREATE TABLE foo_p5 PARTITION OF foo FOR VALUES FROM (41) TO (51); CREATE TABLE foo_p6 PARTITION OF foo FOR VALUES FROM (51) TO (61); CREATE TABLE foo_p7 PARTITION OF foo FOR VALUES FROM (61) TO (71); CREATE TABLE foo_p8 PARTITION OF foo FOR VALUES FROM (71) TO (81); CREATE TABLE foo_p9 PARTITION OF foo FOR VALUES FROM (81) TO (91); CREATE TABLE foo_p10 PARTITION OF foo FOR VALUES FROM (91) TO (101); INSERT INTO foo SELECT generate_series(1,5), generate_series(1,100), generate_series(1,10); ANALYZE foo; -- =================================================================== -- Test 1: Full scan -- all 10 partitions selected -- =================================================================== EXPLAIN (costs off) SELECT * FROM foo; QUERY PLAN -------------------------------- Custom Scan (DynamicTableScan) Root Table: foo Optimizer: pg_orca (3 rows) SELECT * FROM foo ORDER BY a, b, c; a | b | c ---+-----+---- 1 | 1 | 1 2 | 2 | 2 3 | 3 | 3 4 | 4 | 4 5 | 5 | 5 | 6 | 6 | 7 | 7 | 8 | 8 | 9 | 9 | 10 | 10 | 11 | | 12 | | 13 | | 14 | | 15 | | 16 | | 17 | | 18 | | 19 | | 20 | | 21 | | 22 | | 23 | | 24 | | 25 | | 26 | | 27 | | 28 | | 29 | | 30 | | 31 | | 32 | | 33 | | 34 | | 35 | | 36 | | 37 | | 38 | | 39 | | 40 | | 41 | | 42 | | 43 | | 44 | | 45 | | 46 | | 47 | | 48 | | 49 | | 50 | | 51 | | 52 | | 53 | | 54 | | 55 | | 56 | | 57 | | 58 | | 59 | | 60 | | 61 | | 62 | | 63 | | 64 | | 65 | | 66 | | 67 | | 68 | | 69 | | 70 | | 71 | | 72 | | 73 | | 74 | | 75 | | 76 | | 77 | | 78 | | 79 | | 80 | | 81 | | 82 | | 83 | | 84 | | 85 | | 86 | | 87 | | 88 | | 89 | | 90 | | 91 | | 92 | | 93 | | 94 | | 95 | | 96 | | 97 | | 98 | | 99 | | 100 | (100 rows) -- =================================================================== -- Test 2: Point filter b = 35 -- 1 partition (p4: [31,41)) -- =================================================================== EXPLAIN (costs off) SELECT * FROM foo WHERE b = 35; QUERY PLAN -------------------------------- Custom Scan (DynamicTableScan) Filter: (b = 35) Root Table: foo Partitions Selected: 1 Optimizer: pg_orca (5 rows) SELECT * FROM foo WHERE b = 35 ORDER BY a, b, c; a | b | c ---+----+--- | 35 | (1 row) -- =================================================================== -- Test 3: Range filter b < 35 -- 4 partitions ([1,11), [11,21), [21,31), [31,41)) -- =================================================================== EXPLAIN (costs off) SELECT * FROM foo WHERE b < 35; QUERY PLAN -------------------------------- Custom Scan (DynamicTableScan) Filter: (b < 35) Root Table: foo Partitions Selected: 4 Optimizer: pg_orca (5 rows) SELECT * FROM foo WHERE b < 35 ORDER BY a, b, c; a | b | c ---+----+---- 1 | 1 | 1 2 | 2 | 2 3 | 3 | 3 4 | 4 | 4 5 | 5 | 5 | 6 | 6 | 7 | 7 | 8 | 8 | 9 | 9 | 10 | 10 | 11 | | 12 | | 13 | | 14 | | 15 | | 16 | | 17 | | 18 | | 19 | | 20 | | 21 | | 22 | | 23 | | 24 | | 25 | | 26 | | 27 | | 28 | | 29 | | 30 | | 31 | | 32 | | 33 | | 34 | (34 rows) -- =================================================================== -- Test 4: IN list b IN (5, 6, 14, 23) -- 3 partitions ([1,11), [11,21), [21,31)) -- =================================================================== EXPLAIN (costs off) SELECT * FROM foo WHERE b IN (5, 6, 14, 23); QUERY PLAN ------------------------------------------------ Custom Scan (DynamicTableScan) Filter: (b = ANY ('{5,6,14,23}'::integer[])) Root Table: foo Partitions Selected: 3 Optimizer: pg_orca (5 rows) SELECT * FROM foo WHERE b IN (5, 6, 14, 23) ORDER BY a, b, c; a | b | c ---+----+--- 5 | 5 | 5 | 6 | 6 | 14 | | 23 | (4 rows) -- =================================================================== -- Test 5: OR range b < 15 OR b > 60 -- 6 partitions ([1,11), [11,21), [61,71), [71,81), [81,91), [91,101)) -- =================================================================== EXPLAIN (costs off) SELECT * FROM foo WHERE b < 15 OR b > 60; QUERY PLAN ---------------------------------- Custom Scan (DynamicTableScan) Filter: ((b < 15) OR (b > 60)) Root Table: foo Partitions Selected: 7 Optimizer: pg_orca (5 rows) SELECT * FROM foo WHERE b < 15 OR b > 60 ORDER BY a, b, c; a | b | c ---+-----+---- 1 | 1 | 1 2 | 2 | 2 3 | 3 | 3 4 | 4 | 4 5 | 5 | 5 | 6 | 6 | 7 | 7 | 8 | 8 | 9 | 9 | 10 | 10 | 11 | | 12 | | 13 | | 14 | | 61 | | 62 | | 63 | | 64 | | 65 | | 66 | | 67 | | 68 | | 69 | | 70 | | 71 | | 72 | | 73 | | 74 | | 75 | | 76 | | 77 | | 78 | | 79 | | 80 | | 81 | | 82 | | 83 | | 84 | | 85 | | 86 | | 87 | | 88 | | 89 | | 90 | | 91 | | 92 | | 93 | | 94 | | 95 | | 96 | | 97 | | 98 | | 99 | | 100 | (54 rows) -- =================================================================== -- Test 6: Out-of-range b = 150 -- 0 partitions (empty result) -- =================================================================== EXPLAIN (costs off) SELECT * FROM foo WHERE b = 150; QUERY PLAN -------------------------------- Result -> Result One-Time Filter: false Optimizer: pg_orca (4 rows) SELECT * FROM foo WHERE b = 150 ORDER BY a, b, c; a | b | c ---+---+--- (0 rows) -- =================================================================== -- Test 7: Non-constant predicate b = a*5 -- cannot prune statically, all 10 partitions -- =================================================================== EXPLAIN (costs off) SELECT * FROM foo WHERE b = a*5; QUERY PLAN -------------------------------- Custom Scan (DynamicTableScan) Filter: (b = (a * 5)) Root Table: foo Partitions Selected: 10 Optimizer: pg_orca (5 rows) SELECT * FROM foo WHERE b = a*5 ORDER BY a, b, c; a | b | c ---+---+--- (0 rows) -- =================================================================== -- Cleanup -- =================================================================== DROP TABLE foo CASCADE;