Google Git
Sign in
apache / cloudberry / refs/heads/cbdb-postgres-merge / . / src / test / regress / expected / partition_pruning_optimizer.out
blob: 89243051378c9cfbd25666f3d2389dd62de32f2b [file] [log] [blame]
-- TODO: incremental sort is turned off by default, because it may have
-- wrong result for some core case. Turn it on to run the existing tests
-- and minimize the difference from upstream.
set enable_incremental_sort=on;
--
-- Tests on partition pruning (with ORCA) or constraint exclusion (with the
-- Postgres planner). These tests check that you get an "expected" plan, that
-- only scans the partitions that are needed.
--
-- The "correct" plan for a given query depends a lot on the capabilities of
-- the planner and the rest of the system, so the expected output can need
-- updating, as the system improves.
--
-- Create test table with two partitions, for values equal to '1' and values equal to '2'.
create table parttab (n numeric, t text)
partition by list (n)(partition one values ('1'), partition two values('2'));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'n' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
-- Insert three rows. They're all equal to '1', but different number of zeros after decimal point.
insert into parttab values
('1', 'one'),
('1.0', 'one point zero'),
('1.00', 'one point zero zero');
-- select rows whose text representation is three characters long. This should return the '1.0' row.
select * from parttab where length(n::text) = 3;
n | t
-----+----------------
1.0 | one point zero
(1 row)
explain select * from parttab where length(n::text) = 3;
QUERY PLAN
-------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=16)
-> Dynamic Seq Scan on parttab (cost=0.00..431.00 rows=1 width=16)
Number of partitions to scan: 2 (out of 2)
Filter: (length((n)::text) = 3)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
-- Use index scans when possible. That exercises more code, and allows us to
-- spot the cases where the planner cannot use even when it exists.
set enable_seqscan=off;
set enable_bitmapscan=on;
set enable_indexscan=on;
create schema partition_pruning;
set search_path to partition_pruning;
-- Set up common test tables.
CREATE TABLE pt_lt_tab
(
col1 int,
col2 decimal,
col3 text,
col4 bool
)
distributed by (col1)
partition by list(col2)
(
partition part1 values(1,2,3,4,5,6,7,8,9,10),
partition part2 values(11,12,13,14,15,16,17,18,19,20),
partition part3 values(21,22,23,24,25,26,27,28,29,30),
partition part4 values(31,32,33,34,35,36,37,38,39,40),
partition part5 values(41,42,43,44,45,46,47,48,49,50)
);
INSERT INTO pt_lt_tab SELECT i, i,'a',True FROM generate_series(1,3)i;
INSERT INTO pt_lt_tab SELECT i, i,'b',True FROM generate_series(4,6)i;
INSERT INTO pt_lt_tab SELECT i, i,'c',True FROM generate_series(7,10)i;
INSERT INTO pt_lt_tab SELECT i, i,'e',True FROM generate_series(11,13)i;
INSERT INTO pt_lt_tab SELECT i, i,'f',True FROM generate_series(14,16)i;
INSERT INTO pt_lt_tab SELECT i, i,'g',True FROM generate_series(17,20)i;
INSERT INTO pt_lt_tab SELECT i, i,'i',False FROM generate_series(21,23)i;
INSERT INTO pt_lt_tab SELECT i, i,'k',False FROM generate_series(24,26)i;
INSERT INTO pt_lt_tab SELECT i, i,'h',False FROM generate_series(27,30)i;
INSERT INTO pt_lt_tab SELECT i, i,'m',False FROM generate_series(31,33)i;
INSERT INTO pt_lt_tab SELECT i, i,'o',False FROM generate_series(34,36)i;
INSERT INTO pt_lt_tab SELECT i, i,'n',False FROM generate_series(37,40)i;
INSERT INTO pt_lt_tab SELECT i, i,'p',False FROM generate_series(41,43)i;
INSERT INTO pt_lt_tab SELECT i, i,'s',False FROM generate_series(44,46)i;
INSERT INTO pt_lt_tab SELECT i, i,'q',False FROM generate_series(47,50)i;
ANALYZE pt_lt_tab;
-- pt_lt_tab_df is the same as pt_lt_tab, but with a default partition (and some
-- values in the default partition, including NULLs).
CREATE TABLE pt_lt_tab_df
(
col1 int,
col2 decimal,
col3 text,
col4 bool
)
distributed by (col1)
partition by list(col2)
(
partition part1 VALUES(1,2,3,4,5,6,7,8,9,10),
partition part2 VALUES(11,12,13,14,15,16,17,18,19,20),
partition part3 VALUES(21,22,23,24,25,26,27,28,29,30),
partition part4 VALUES(31,32,33,34,35,36,37,38,39,40),
partition part5 VALUES(41,42,43,44,45,46,47,48,49,50),
default partition def
);
INSERT INTO pt_lt_tab_df SELECT i, i,'a',True FROM generate_series(1,3)i;
INSERT INTO pt_lt_tab_df SELECT i, i,'b',True FROM generate_series(4,6)i;
INSERT INTO pt_lt_tab_df SELECT i, i,'c',True FROM generate_series(7,10)i;
INSERT INTO pt_lt_tab_df SELECT i, i,'e',True FROM generate_series(11,13)i;
INSERT INTO pt_lt_tab_df SELECT i, i,'f',True FROM generate_series(14,16)i;
INSERT INTO pt_lt_tab_df SELECT i, i,'g',True FROM generate_series(17,20)i;
INSERT INTO pt_lt_tab_df SELECT i, i,'i',False FROM generate_series(21,23)i;
INSERT INTO pt_lt_tab_df SELECT i, i,'k',False FROM generate_series(24,26)i;
INSERT INTO pt_lt_tab_df SELECT i, i,'h',False FROM generate_series(27,30)i;
INSERT INTO pt_lt_tab_df SELECT i, i,'m',False FROM generate_series(31,33)i;
INSERT INTO pt_lt_tab_df SELECT i, i,'o',False FROM generate_series(34,36)i;
INSERT INTO pt_lt_tab_df SELECT i, i,'n',False FROM generate_series(37,40)i;
INSERT INTO pt_lt_tab_df SELECT i, i,'p',False FROM generate_series(41,43)i;
INSERT INTO pt_lt_tab_df SELECT i, i,'s',False FROM generate_series(44,46)i;
INSERT INTO pt_lt_tab_df SELECT i, i,'q',False FROM generate_series(47,50)i;
INSERT INTO pt_lt_tab_df SELECT i, i,'u',True FROM generate_series(51,53)i;
INSERT INTO pt_lt_tab_df SELECT i, i,'x',True FROM generate_series(54,56)i;
INSERT INTO pt_lt_tab_df SELECT i, i,'w',True FROM generate_series(57,60)i;
INSERT INTO pt_lt_tab_df VALUES(NULL,NULL,NULL,NULL);
INSERT INTO pt_lt_tab_df VALUES(NULL,NULL,NULL,NULL);
INSERT INTO pt_lt_tab_df VALUES(NULL,NULL,NULL,NULL);
ANALYZE pt_lt_tab_df;
--
-- Test that stable functions are evaluated when constructing the plan. This
-- differs from PostgreSQL. In PostgreSQL, PREPARE/EXECUTE creates a reusable
-- plan, while in GPDB, we re-plan the query on every execution, so that the
-- stable function is executed during planning, and we can therefore do
-- partition pruning based on its result.
--
create or replace function stabletestfunc() returns integer as $$
begin
return 10;
end;
$$ language plpgsql stable;
PREPARE prep_prune AS select * from pt_lt_tab WHERE col2 = stabletestfunc();
-- The plan should only scan one partition, where col2 = 10.
EXPLAIN EXECUTE prep_prune;
QUERY PLAN
-------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=12)
-> Dynamic Seq Scan on pt_lt_tab (cost=0.00..431.00 rows=1 width=12)
Number of partitions to scan: 1
Filter: (col2 = '10'::numeric)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
-- Also test that Params are const-evaluated.
PREPARE prep_prune_param AS select * from pt_lt_tab WHERE col2 = $1;
EXPLAIN EXECUTE prep_prune_param(10);
QUERY PLAN
-------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=12)
-> Dynamic Seq Scan on pt_lt_tab (cost=0.00..431.00 rows=1 width=12)
Number of partitions to scan: 1
Filter: (col2 = '10'::numeric)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
-- @description B-tree single index key = non-partitioning key
CREATE INDEX idx1 on pt_lt_tab(col1);
SELECT * FROM pt_lt_tab WHERE col1 < 10 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
1 | 1 | a | t
2 | 2 | a | t
3 | 3 | a | t
4 | 4 | b | t
5 | 5 | b | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col1 < 10 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Limit (cost=0.00..6.00 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..6.00 rows=2 width=12)
-> Sort (cost=0.00..6.00 rows=4 width=12)
Sort Key: col2, col3
-> Dynamic Index Scan on idx1 on pt_lt_tab (cost=0.00..6.00 rows=4 width=12)
Index Cond: (col1 < 10)
Number of partitions to scan: 5
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab WHERE col1 > 50 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
(0 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col1 > 50 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
----------------------------------------------------------------------------------------------
Limit (cost=0.00..6.00 rows=1 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=1 width=12)
Merge Key: col2, col3
-> Sort (cost=0.00..6.00 rows=1 width=12)
Sort Key: col2, col3
-> Dynamic Index Scan on idx1 on pt_lt_tab (cost=0.00..6.00 rows=1 width=12)
Index Cond: (col1 > 50)
Number of partitions to scan: 5
Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)
SELECT * FROM pt_lt_tab WHERE col1 = 25 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
25 | 25 | k | f
(1 row)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col1 = 25 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
----------------------------------------------------------------------------------------------
Limit (cost=0.00..6.00 rows=1 width=12)
-> Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..6.00 rows=1 width=12)
Merge Key: col2, col3
-> Sort (cost=0.00..6.00 rows=1 width=12)
Sort Key: col2, col3
-> Dynamic Index Scan on idx1 on pt_lt_tab (cost=0.00..6.00 rows=1 width=12)
Index Cond: (col1 = 25)
Number of partitions to scan: 5
Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)
SELECT * FROM pt_lt_tab WHERE col1 <> 10 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
1 | 1 | a | t
2 | 2 | a | t
3 | 3 | a | t
4 | 4 | b | t
5 | 5 | b | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col1 <> 10 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
---------------------------------------------------------------------------------------------
Limit (cost=0.00..431.01 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.01 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..431.01 rows=2 width=12)
-> Sort (cost=0.00..431.01 rows=17 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab (cost=0.00..431.00 rows=17 width=12)
Number of partitions to scan: 5
Filter: (col1 <> 10)
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab WHERE col1 > 10 AND col1 < 50 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
11 | 11 | e | t
12 | 12 | e | t
13 | 13 | e | t
14 | 14 | f | t
15 | 15 | f | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col1 > 10 AND col1 < 50 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Limit (cost=0.00..6.01 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.01 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..6.01 rows=2 width=12)
-> Sort (cost=0.00..6.01 rows=13 width=12)
Sort Key: col2, col3
-> Dynamic Index Scan on idx1 on pt_lt_tab (cost=0.00..6.00 rows=13 width=12)
Index Cond: ((col1 > 10) AND (col1 < 50))
Number of partitions to scan: 5
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab WHERE col1 > 10 OR col1 = 25 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
11 | 11 | e | t
12 | 12 | e | t
13 | 13 | e | t
14 | 14 | f | t
15 | 15 | f | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col1 > 10 OR col1 = 25 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
---------------------------------------------------------------------------------------------
Limit (cost=0.00..431.00 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..431.00 rows=2 width=12)
-> Sort (cost=0.00..431.00 rows=14 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab (cost=0.00..431.00 rows=14 width=12)
Number of partitions to scan: 5
Filter: ((col1 > 10) OR (col1 = 25))
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab WHERE col1 between 10 AND 25 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
10 | 10 | c | t
11 | 11 | e | t
12 | 12 | e | t
13 | 13 | e | t
14 | 14 | f | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col1 between 10 AND 25 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Limit (cost=0.00..6.00 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..6.00 rows=2 width=12)
-> Sort (cost=0.00..6.00 rows=6 width=12)
Sort Key: col2, col3
-> Dynamic Index Scan on idx1 on pt_lt_tab (cost=0.00..6.00 rows=6 width=12)
Index Cond: ((col1 >= 10) AND (col1 <= 25))
Number of partitions to scan: 5
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
DROP INDEX idx1;
-- @description B-tree single index key = partitioning key
CREATE INDEX idx1 on pt_lt_tab(col2);
SELECT * FROM pt_lt_tab WHERE col2 < 10 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
1 | 1 | a | t
2 | 2 | a | t
3 | 3 | a | t
4 | 4 | b | t
5 | 5 | b | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col2 < 10 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Limit (cost=0.00..6.00 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..6.00 rows=2 width=12)
-> Sort (cost=0.00..6.00 rows=4 width=12)
Sort Key: col2, col3
-> Dynamic Index Scan on idx1 on pt_lt_tab (cost=0.00..6.00 rows=4 width=12)
Index Cond: (col2 < '10'::numeric)
Number of partitions to scan: 1
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab WHERE col2 > 50 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
(0 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col2 > 50 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
-------------------------------------------------------------
Limit (cost=0.00..0.00 rows=0 width=21)
-> Result (cost=0.00..0.00 rows=0 width=21)
-> Sort (cost=0.00..0.00 rows=0 width=21)
Sort Key: (NULL::numeric), (NULL::text)
-> Result (cost=0.00..0.00 rows=0 width=21)
One-Time Filter: false
Optimizer: Pivotal Optimizer (GPORCA)
(7 rows)
SELECT * FROM pt_lt_tab WHERE col2 = 25 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
25 | 25 | k | f
(1 row)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col2 = 25 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
----------------------------------------------------------------------------------------------
Limit (cost=0.00..6.00 rows=2 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=2 width=12)
Merge Key: col2, col3
-> Sort (cost=0.00..6.00 rows=1 width=12)
Sort Key: col2, col3
-> Dynamic Index Scan on idx1 on pt_lt_tab (cost=0.00..6.00 rows=1 width=12)
Index Cond: (col2 = '25'::numeric)
Number of partitions to scan: 1
Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)
SELECT * FROM pt_lt_tab WHERE col2 <> 10 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
1 | 1 | a | t
2 | 2 | a | t
3 | 3 | a | t
4 | 4 | b | t
5 | 5 | b | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col2 <> 10 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
---------------------------------------------------------------------------------------------
Limit (cost=0.00..431.01 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.01 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..431.01 rows=2 width=12)
-> Sort (cost=0.00..431.01 rows=16 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab (cost=0.00..431.00 rows=16 width=12)
Number of partitions to scan: 5
Filter: (col2 <> '10'::numeric)
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab WHERE col2 > 10 AND col2 < 50 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
11 | 11 | e | t
12 | 12 | e | t
13 | 13 | e | t
14 | 14 | f | t
15 | 15 | f | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col2 > 10 AND col2 < 50 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Limit (cost=0.00..6.01 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.01 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..6.01 rows=2 width=12)
-> Sort (cost=0.00..6.01 rows=12 width=12)
Sort Key: col2, col3
-> Dynamic Index Scan on idx1 on pt_lt_tab (cost=0.00..6.00 rows=13 width=12)
Index Cond: ((col2 > '10'::numeric) AND (col2 < '50'::numeric))
Number of partitions to scan: 4
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab WHERE col2 > 10 OR col2 = 50 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
11 | 11 | e | t
12 | 12 | e | t
13 | 13 | e | t
14 | 14 | f | t
15 | 15 | f | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col2 > 10 OR col2 = 50 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
---------------------------------------------------------------------------------------------
Limit (cost=0.00..431.00 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..431.00 rows=2 width=12)
-> Sort (cost=0.00..431.00 rows=13 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab (cost=0.00..431.00 rows=13 width=12)
Number of partitions to scan: 4
Filter: ((col2 > '10'::numeric) OR (col2 = '50'::numeric))
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab WHERE col2 between 10 AND 50 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
10 | 10 | c | t
11 | 11 | e | t
12 | 12 | e | t
13 | 13 | e | t
14 | 14 | f | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col2 between 10 AND 50 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Limit (cost=0.00..6.01 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.01 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..6.01 rows=2 width=12)
-> Sort (cost=0.00..6.01 rows=13 width=12)
Sort Key: col2, col3
-> Dynamic Index Scan on idx1 on pt_lt_tab (cost=0.00..6.00 rows=14 width=12)
Index Cond: ((col2 >= '10'::numeric) AND (col2 <= '50'::numeric))
Number of partitions to scan: 5
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
DROP INDEX idx1;
-- @description multiple column b-tree index
CREATE INDEX idx1 on pt_lt_tab(col1,col2);
SELECT * FROM pt_lt_tab WHERE col1 < 10 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
1 | 1 | a | t
2 | 2 | a | t
3 | 3 | a | t
4 | 4 | b | t
5 | 5 | b | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col1 < 10 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Limit (cost=0.00..6.00 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..6.00 rows=2 width=12)
-> Sort (cost=0.00..6.00 rows=4 width=12)
Sort Key: col2, col3
-> Dynamic Index Scan on idx1 on pt_lt_tab (cost=0.00..6.00 rows=4 width=12)
Index Cond: (col1 < 10)
Number of partitions to scan: 5
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab WHERE col1 > 50 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
(0 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col1 > 50 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
----------------------------------------------------------------------------------------------
Limit (cost=0.00..6.00 rows=1 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=1 width=12)
Merge Key: col2, col3
-> Sort (cost=0.00..6.00 rows=1 width=12)
Sort Key: col2, col3
-> Dynamic Index Scan on idx1 on pt_lt_tab (cost=0.00..6.00 rows=1 width=12)
Index Cond: (col1 > 50)
Number of partitions to scan: 5
Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)
SELECT * FROM pt_lt_tab WHERE col2 = 25 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
25 | 25 | k | f
(1 row)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col2 = 25 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
----------------------------------------------------------------------------------------------
Limit (cost=0.00..6.00 rows=2 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=2 width=12)
Merge Key: col2, col3
-> Sort (cost=0.00..6.00 rows=1 width=12)
Sort Key: col2, col3
-> Dynamic Index Scan on idx1 on pt_lt_tab (cost=0.00..6.00 rows=1 width=12)
Index Cond: (col2 = '25'::numeric)
Number of partitions to scan: 1
Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)
SELECT * FROM pt_lt_tab WHERE col2 <> 10 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
1 | 1 | a | t
2 | 2 | a | t
3 | 3 | a | t
4 | 4 | b | t
5 | 5 | b | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col2 <> 10 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
---------------------------------------------------------------------------------------------
Limit (cost=0.00..431.01 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.01 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..431.01 rows=2 width=12)
-> Sort (cost=0.00..431.01 rows=16 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab (cost=0.00..431.00 rows=16 width=12)
Number of partitions to scan: 5
Filter: (col2 <> '10'::numeric)
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab WHERE col2 > 10 AND col1 = 10 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
(0 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col2 > 10 AND col1 = 10 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
----------------------------------------------------------------------------------------------
Limit (cost=0.00..6.00 rows=1 width=12)
-> Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..6.00 rows=1 width=12)
Merge Key: col2, col3
-> Sort (cost=0.00..6.00 rows=1 width=12)
Sort Key: col2, col3
-> Dynamic Index Scan on idx1 on pt_lt_tab (cost=0.00..6.00 rows=1 width=12)
Index Cond: ((col1 = 10) AND (col2 > '10'::numeric))
Number of partitions to scan: 4
Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)
SELECT * FROM pt_lt_tab WHERE col2 > 10.00 OR col1 = 50 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
11 | 11 | e | t
12 | 12 | e | t
13 | 13 | e | t
14 | 14 | f | t
15 | 15 | f | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col2 > 10.00 OR col1 = 50 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
---------------------------------------------------------------------------------------------
Limit (cost=0.00..431.01 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.01 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..431.01 rows=2 width=12)
-> Sort (cost=0.00..431.01 rows=14 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab (cost=0.00..431.00 rows=14 width=12)
Number of partitions to scan: 5
Filter: ((col2 > 10.00) OR (col1 = 50))
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab WHERE col2 between 10 AND 50 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
10 | 10 | c | t
11 | 11 | e | t
12 | 12 | e | t
13 | 13 | e | t
14 | 14 | f | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col2 between 10 AND 50 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Limit (cost=0.00..6.01 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.01 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..6.01 rows=2 width=12)
-> Sort (cost=0.00..6.01 rows=13 width=12)
Sort Key: col2, col3
-> Dynamic Index Scan on idx1 on pt_lt_tab (cost=0.00..6.01 rows=14 width=12)
Index Cond: ((col2 >= '10'::numeric) AND (col2 <= '50'::numeric))
Number of partitions to scan: 5
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
DROP INDEX idx1;
-- @description multi-column unique constraint (= b-tree index). Essentially the
-- same as the previous case, but the columns are the other way 'round, and we
-- do this on the table with default partition.
ALTER TABLE pt_lt_tab_df ADD CONSTRAINT col2_col1_unique unique(col2,col1);
SELECT * FROM pt_lt_tab_df WHERE col1 < 10 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
1 | 1 | a | t
2 | 2 | a | t
3 | 3 | a | t
4 | 4 | b | t
5 | 5 | b | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col1 < 10 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..6.00 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..6.00 rows=2 width=12)
-> Sort (cost=0.00..6.00 rows=4 width=12)
Sort Key: col2, col3
-> Dynamic Index Scan on col2_col1_unique on pt_lt_tab_df (cost=0.00..6.00 rows=4 width=12)
Index Cond: (col1 < 10)
Number of partitions to scan: 6
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab_df WHERE col1 > 50 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
51 | 51 | u | t
52 | 52 | u | t
53 | 53 | u | t
54 | 54 | x | t
55 | 55 | x | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col1 > 50 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..6.00 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..6.00 rows=2 width=12)
-> Sort (cost=0.00..6.00 rows=4 width=12)
Sort Key: col2, col3
-> Dynamic Index Scan on col2_col1_unique on pt_lt_tab_df (cost=0.00..6.00 rows=4 width=12)
Index Cond: (col1 > 50)
Number of partitions to scan: 6
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab_df WHERE col2 = 25 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
25 | 25 | k | f
(1 row)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col2 = 25 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..6.00 rows=2 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=2 width=12)
Merge Key: col2, col3
-> Sort (cost=0.00..6.00 rows=1 width=12)
Sort Key: col2, col3
-> Dynamic Index Scan on col2_col1_unique on pt_lt_tab_df (cost=0.00..6.00 rows=1 width=12)
Index Cond: (col2 = '25'::numeric)
Number of partitions to scan: 1
Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)
SELECT * FROM pt_lt_tab_df WHERE col2 <> 10 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
1 | 1 | a | t
2 | 2 | a | t
3 | 3 | a | t
4 | 4 | b | t
5 | 5 | b | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col2 <> 10 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
------------------------------------------------------------------------------------------------
Limit (cost=0.00..431.01 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.01 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..431.01 rows=2 width=12)
-> Sort (cost=0.00..431.01 rows=20 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab_df (cost=0.00..431.00 rows=20 width=12)
Number of partitions to scan: 6
Filter: (col2 <> '10'::numeric)
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab_df WHERE col2 > 10 AND col1 = 10 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
(0 rows)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col2 > 10 AND col1 = 10 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..6.00 rows=1 width=12)
-> Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..6.00 rows=1 width=12)
Merge Key: col2, col3
-> Sort (cost=0.00..6.00 rows=1 width=12)
Sort Key: col2, col3
-> Dynamic Index Scan on col2_col1_unique on pt_lt_tab_df (cost=0.00..6.00 rows=1 width=12)
Index Cond: ((col2 > '10'::numeric) AND (col1 = 10))
Number of partitions to scan: 5
Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)
SELECT * FROM pt_lt_tab_df WHERE col2 > 10.00 OR col1 = 50 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
11 | 11 | e | t
12 | 12 | e | t
13 | 13 | e | t
14 | 14 | f | t
15 | 15 | f | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col2 > 10.00 OR col1 = 50 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
------------------------------------------------------------------------------------------------
Limit (cost=0.00..431.01 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.01 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..431.01 rows=2 width=12)
-> Sort (cost=0.00..431.01 rows=17 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab_df (cost=0.00..431.00 rows=17 width=12)
Number of partitions to scan: 6
Filter: ((col2 > 10.00) OR (col1 = 50))
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab_df WHERE col2 between 10 AND 50 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
10 | 10 | c | t
11 | 11 | e | t
12 | 12 | e | t
13 | 13 | e | t
14 | 14 | f | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col2 between 10 AND 50 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..6.01 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.01 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..6.01 rows=2 width=12)
-> Sort (cost=0.00..6.01 rows=14 width=12)
Sort Key: col2, col3
-> Dynamic Index Scan on col2_col1_unique on pt_lt_tab_df (cost=0.00..6.01 rows=14 width=12)
Index Cond: ((col2 >= '10'::numeric) AND (col2 <= '50'::numeric))
Number of partitions to scan: 6
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
ALTER TABLE pt_lt_tab_df DROP CONSTRAINT col2_col1_unique;
-- @description Heterogeneous index, index on partition key, b-tree index on all partitions
CREATE INDEX idx1 on pt_lt_tab_1_prt_part1(col2);
CREATE INDEX idx2 on pt_lt_tab_1_prt_part2(col2);
CREATE INDEX idx3 on pt_lt_tab_1_prt_part3(col2);
CREATE INDEX idx4 on pt_lt_tab_1_prt_part4(col2);
CREATE INDEX idx5 on pt_lt_tab_1_prt_part5(col2);
SELECT * FROM pt_lt_tab WHERE col2 between 1 AND 50 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
1 | 1 | a | t
2 | 2 | a | t
3 | 3 | a | t
4 | 4 | b | t
5 | 5 | b | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col2 between 1 AND 50 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
---------------------------------------------------------------------------------------------
Limit (cost=0.00..431.01 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.01 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..431.01 rows=2 width=12)
-> Sort (cost=0.00..431.01 rows=16 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab (cost=0.00..431.00 rows=16 width=12)
Number of partitions to scan: 5
Filter: ((col2 >= '1'::numeric) AND (col2 <= '50'::numeric))
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab WHERE col2 > 5 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
6 | 6 | b | t
7 | 7 | c | t
8 | 8 | c | t
9 | 9 | c | t
10 | 10 | c | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col2 > 5 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
---------------------------------------------------------------------------------------------
Limit (cost=0.00..431.01 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..431.00 rows=2 width=12)
-> Sort (cost=0.00..431.00 rows=14 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab (cost=0.00..431.00 rows=14 width=12)
Number of partitions to scan: 5
Filter: (col2 > '5'::numeric)
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab WHERE col2 = 5 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
5 | 5 | b | t
(1 row)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col2 = 5 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
--------------------------------------------------------------------------------------
Limit (cost=0.00..431.00 rows=2 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=12)
Merge Key: pt_lt_tab_1_prt_part1.col2, pt_lt_tab_1_prt_part1.col3
-> Sort (cost=0.00..431.00 rows=1 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab (cost=0.00..431.00 rows=1 width=12)
Number of partitions to scan: 1
Filter: (col2 = '5'::numeric)
Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)
DROP INDEX idx1;
DROP INDEX idx2;
DROP INDEX idx3;
DROP INDEX idx4;
DROP INDEX idx5;
-- @description Heterogeneous index,b-tree index on all parts,index, index on non-partition col
CREATE INDEX idx1 on pt_lt_tab_df_1_prt_part1(col1);
CREATE INDEX idx2 on pt_lt_tab_df_1_prt_part2(col1);
CREATE INDEX idx3 on pt_lt_tab_df_1_prt_part3(col1);
CREATE INDEX idx4 on pt_lt_tab_df_1_prt_part4(col1);
CREATE INDEX idx5 on pt_lt_tab_df_1_prt_part5(col1);
CREATE INDEX idx6 on pt_lt_tab_df_1_prt_def(col1);
SELECT * FROM pt_lt_tab_df WHERE col1 between 1 AND 100 ORDER BY col1 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
1 | 1 | a | t
2 | 2 | a | t
3 | 3 | a | t
4 | 4 | b | t
5 | 5 | b | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col1 between 1 AND 100 ORDER BY col1 LIMIT 5;
QUERY PLAN
------------------------------------------------------------------------------------------------
Limit (cost=0.00..431.01 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.01 rows=5 width=12)
Merge Key: col1
-> Limit (cost=0.00..431.01 rows=2 width=12)
-> Sort (cost=0.00..431.01 rows=20 width=12)
Sort Key: col1
-> Dynamic Seq Scan on pt_lt_tab_df (cost=0.00..431.00 rows=20 width=12)
Number of partitions to scan: 6
Filter: ((col1 >= 1) AND (col1 <= 100))
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab_df WHERE col1 > 50 ORDER BY col1 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
51 | 51 | u | t
52 | 52 | u | t
53 | 53 | u | t
54 | 54 | x | t
55 | 55 | x | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col1 > 50 ORDER BY col1 LIMIT 5;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Limit (cost=0.00..431.00 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=5 width=12)
Merge Key: col1
-> Limit (cost=0.00..431.00 rows=2 width=12)
-> Sort (cost=0.00..431.00 rows=4 width=12)
Sort Key: col1
-> Dynamic Seq Scan on pt_lt_tab_df (cost=0.00..431.00 rows=4 width=12)
Number of partitions to scan: 6
Filter: (col1 > 50)
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab_df WHERE col1 < 50 ORDER BY col1 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
1 | 1 | a | t
2 | 2 | a | t
3 | 3 | a | t
4 | 4 | b | t
5 | 5 | b | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col1 < 50 ORDER BY col1 LIMIT 5;
QUERY PLAN
------------------------------------------------------------------------------------------------
Limit (cost=0.00..431.01 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.01 rows=5 width=12)
Merge Key: col1
-> Limit (cost=0.00..431.01 rows=2 width=12)
-> Sort (cost=0.00..431.01 rows=17 width=12)
Sort Key: col1
-> Dynamic Seq Scan on pt_lt_tab_df (cost=0.00..431.00 rows=17 width=12)
Number of partitions to scan: 6
Filter: (col1 < 50)
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
DROP INDEX idx1;
DROP INDEX idx2;
DROP INDEX idx3;
DROP INDEX idx4;
DROP INDEX idx5;
DROP INDEX idx6;
-- @description Heterogeneous index,b-tree index on all parts including default, index on partition col
CREATE INDEX idx1 on pt_lt_tab_df_1_prt_part1(col2);
CREATE INDEX idx2 on pt_lt_tab_df_1_prt_part2(col2);
CREATE INDEX idx3 on pt_lt_tab_df_1_prt_part3(col2);
CREATE INDEX idx4 on pt_lt_tab_df_1_prt_part4(col2);
CREATE INDEX idx5 on pt_lt_tab_df_1_prt_part5(col2);
CREATE INDEX idx6 on pt_lt_tab_df_1_prt_def(col2);
SELECT * FROM pt_lt_tab_df WHERE col2 between 1 AND 100 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
1 | 1 | a | t
2 | 2 | a | t
3 | 3 | a | t
4 | 4 | b | t
5 | 5 | b | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col2 between 1 AND 100 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
------------------------------------------------------------------------------------------------
Limit (cost=0.00..431.01 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.01 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..431.01 rows=2 width=12)
-> Sort (cost=0.00..431.01 rows=20 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab_df (cost=0.00..431.00 rows=20 width=12)
Number of partitions to scan: 6
Filter: ((col2 >= '1'::numeric) AND (col2 <= '100'::numeric))
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab_df WHERE col2 > 50 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
51 | 51 | u | t
52 | 52 | u | t
53 | 53 | u | t
54 | 54 | x | t
55 | 55 | x | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col2 > 50 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Limit (cost=0.00..431.00 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=5 width=12)
Merge Key: pt_lt_tab_df_1_prt_def.col2, pt_lt_tab_df_1_prt_def.col3
-> Limit (cost=0.00..431.00 rows=2 width=12)
-> Sort (cost=0.00..431.00 rows=4 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab_df (cost=0.00..431.00 rows=4 width=12)
Number of partitions to scan: 1
Filter: (col2 > '50'::numeric)
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab_df WHERE col2 = 50 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
50 | 50 | q | f
(1 row)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col2 = 50 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
-----------------------------------------------------------------------------------------
Limit (cost=0.00..431.00 rows=2 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=12)
Merge Key: col2, col3
-> Sort (cost=0.00..431.00 rows=1 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab_df (cost=0.00..431.00 rows=1 width=12)
Number of partitions to scan: 1
Filter: (col2 = '50'::numeric)
Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)
SELECT * FROM pt_lt_tab_df WHERE col2 <> 10 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
1 | 1 | a | t
2 | 2 | a | t
3 | 3 | a | t
4 | 4 | b | t
5 | 5 | b | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col2 <> 10 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
------------------------------------------------------------------------------------------------
Limit (cost=0.00..431.01 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.01 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..431.01 rows=2 width=12)
-> Sort (cost=0.00..431.01 rows=20 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab_df (cost=0.00..431.00 rows=20 width=12)
Number of partitions to scan: 6
Filter: (col2 <> '10'::numeric)
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab_df WHERE col2 between 1 AND 100 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
1 | 1 | a | t
2 | 2 | a | t
3 | 3 | a | t
4 | 4 | b | t
5 | 5 | b | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col2 between 1 AND 100 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
------------------------------------------------------------------------------------------------
Limit (cost=0.00..431.01 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.01 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..431.01 rows=2 width=12)
-> Sort (cost=0.00..431.01 rows=20 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab_df (cost=0.00..431.00 rows=20 width=12)
Number of partitions to scan: 6
Filter: ((col2 >= '1'::numeric) AND (col2 <= '100'::numeric))
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab_df WHERE col2 < 50 AND col1 > 10 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
11 | 11 | e | t
12 | 12 | e | t
13 | 13 | e | t
14 | 14 | f | t
15 | 15 | f | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col2 < 50 AND col1 > 10 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
------------------------------------------------------------------------------------------------
Limit (cost=0.00..431.01 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.01 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..431.01 rows=2 width=12)
-> Sort (cost=0.00..431.01 rows=17 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab_df (cost=0.00..431.00 rows=17 width=12)
Number of partitions to scan: 6
Filter: ((col2 < '50'::numeric) AND (col1 > 10))
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
DROP INDEX idx1;
DROP INDEX idx2;
DROP INDEX idx3;
DROP INDEX idx4;
DROP INDEX idx5;
DROP INDEX idx6;
-- @description Negative tests Combination tests, no index on default partition
CREATE INDEX idx1 on pt_lt_tab_df_1_prt_part1(col2);
CREATE INDEX idx2 on pt_lt_tab_df_1_prt_part2(col2);
CREATE INDEX idx3 on pt_lt_tab_df_1_prt_part3(col2);
CREATE INDEX idx4 on pt_lt_tab_df_1_prt_part4(col2);
CREATE INDEX idx5 on pt_lt_tab_df_1_prt_part5(col2);
SELECT * FROM pt_lt_tab_df WHERE col2 > 51 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
52 | 52 | u | t
53 | 53 | u | t
54 | 54 | x | t
55 | 55 | x | t
56 | 56 | x | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col2 > 51 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Limit (cost=0.00..431.00 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=5 width=12)
Merge Key: pt_lt_tab_df_1_prt_def.col2, pt_lt_tab_df_1_prt_def.col3
-> Limit (cost=0.00..431.00 rows=2 width=12)
-> Sort (cost=0.00..431.00 rows=4 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab_df (cost=0.00..431.00 rows=4 width=12)
Number of partitions to scan: 1
Filter: (col2 > '51'::numeric)
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab_df WHERE col2 = 50 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
50 | 50 | q | f
(1 row)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col2 = 50 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
-----------------------------------------------------------------------------------------
Limit (cost=0.00..431.00 rows=2 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=12)
Merge Key: col2, col3
-> Sort (cost=0.00..431.00 rows=1 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab_df (cost=0.00..431.00 rows=1 width=12)
Number of partitions to scan: 1
Filter: (col2 = '50'::numeric)
Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)
DROP INDEX idx1;
DROP INDEX idx2;
DROP INDEX idx3;
DROP INDEX idx4;
DROP INDEX idx5;
-- @description Negative tests Combination tests ,index exists on some regular partitions and not on the default partition
CREATE INDEX idx1 on pt_lt_tab_df_1_prt_part1(col2);
CREATE INDEX idx5 on pt_lt_tab_df_1_prt_part5(col2);
SELECT * FROM pt_lt_tab_df WHERE col2 is NULL ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
| | |
| | |
| | |
(3 rows)
DROP INDEX idx1;
DROP INDEX idx5;
-- @description Heterogeneous index,b-tree index on all parts,index , multiple index
CREATE INDEX idx1 on pt_lt_tab_df_1_prt_part1(col2,col1);
CREATE INDEX idx2 on pt_lt_tab_df_1_prt_part2(col2,col1);
CREATE INDEX idx3 on pt_lt_tab_df_1_prt_part3(col2,col1);
CREATE INDEX idx4 on pt_lt_tab_df_1_prt_part4(col2,col1);
CREATE INDEX idx5 on pt_lt_tab_df_1_prt_part5(col2,col1);
CREATE INDEX idx6 on pt_lt_tab_df_1_prt_def(col2,col1);
SELECT * FROM pt_lt_tab_df WHERE col2 = 50 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
50 | 50 | q | f
(1 row)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col2 = 50 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
-----------------------------------------------------------------------------------------
Limit (cost=0.00..431.00 rows=2 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=12)
Merge Key: col2, col3
-> Sort (cost=0.00..431.00 rows=1 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab_df (cost=0.00..431.00 rows=1 width=12)
Number of partitions to scan: 1
Filter: (col2 = '50'::numeric)
Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)
SELECT * FROM pt_lt_tab_df WHERE col2 > 10 AND col1 between 1 AND 100 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
11 | 11 | e | t
12 | 12 | e | t
13 | 13 | e | t
14 | 14 | f | t
15 | 15 | f | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col2 > 10 AND col1 between 1 AND 100 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
------------------------------------------------------------------------------------------------
Limit (cost=0.00..431.01 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.01 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..431.01 rows=2 width=12)
-> Sort (cost=0.00..431.01 rows=17 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab_df (cost=0.00..431.00 rows=17 width=12)
Number of partitions to scan: 5
Filter: ((col2 > '10'::numeric) AND (col1 >= 1) AND (col1 <= 100))
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab_df WHERE col1 = 10 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
10 | 10 | c | t
(1 row)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col1 = 10 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
-----------------------------------------------------------------------------------------
Limit (cost=0.00..431.00 rows=2 width=12)
-> Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..431.00 rows=2 width=12)
Merge Key: col2, col3
-> Sort (cost=0.00..431.00 rows=1 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab_df (cost=0.00..431.00 rows=1 width=12)
Number of partitions to scan: 6
Filter: (col1 = 10)
Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)
DROP INDEX idx1;
DROP INDEX idx2;
DROP INDEX idx3;
DROP INDEX idx4;
DROP INDEX idx5;
DROP INDEX idx6;
-- @description Index exists on some continuous set of partitions, e.g. p1,p2,p3
CREATE INDEX idx1 on pt_lt_tab_df_1_prt_part1(col2);
CREATE INDEX idx2 on pt_lt_tab_df_1_prt_part2(col2);
CREATE INDEX idx3 on pt_lt_tab_df_1_prt_part3(col2);
SELECT * FROM pt_lt_tab_df WHERE col2 = 35 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
35 | 35 | o | f
(1 row)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col2 = 35 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
-----------------------------------------------------------------------------------------
Limit (cost=0.00..431.00 rows=2 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=12)
Merge Key: pt_lt_tab_df_1_prt_part4.col2, pt_lt_tab_df_1_prt_part4.col3
-> Sort (cost=0.00..431.00 rows=1 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab_df (cost=0.00..431.00 rows=1 width=12)
Number of partitions to scan: 1
Filter: (col2 = '35'::numeric)
Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)
DROP INDEX idx1;
DROP INDEX idx2;
DROP INDEX idx3;
-- @description Index exists on some regular partitions and on the default partition [INDEX exists on non-consecutive partitions, e.g. p1,p3,p5]
CREATE INDEX idx1 on pt_lt_tab_df_1_prt_part1(col2);
CREATE INDEX idx5 on pt_lt_tab_df_1_prt_part5(col2);
CREATE INDEX idx6 on pt_lt_tab_df_1_prt_def(col2);
SELECT * FROM pt_lt_tab_df WHERE col2 > 15 ORDER BY col2,col3 LIMIT 5;
col1 | col2 | col3 | col4
------+------+------+------
16 | 16 | f | t
17 | 17 | g | t
18 | 18 | g | t
19 | 19 | g | t
20 | 20 | g | t
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab_df WHERE col2 > 15 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
------------------------------------------------------------------------------------------------
Limit (cost=0.00..431.01 rows=5 width=12)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.01 rows=5 width=12)
Merge Key: col2, col3
-> Limit (cost=0.00..431.01 rows=2 width=12)
-> Sort (cost=0.00..431.01 rows=16 width=12)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab_df (cost=0.00..431.00 rows=16 width=12)
Number of partitions to scan: 5
Filter: (col2 > '15'::numeric)
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
DROP INDEX idx1;
DROP INDEX idx5;
DROP INDEX idx6;
--
-- Finally, after running all the other tests on pg_lt_tab, test that
-- partition pruning still works after dropping a column
--
CREATE INDEX idx1 on pt_lt_tab(col4);
ALTER TABLE pt_lt_tab DROP column col1;
NOTICE: dropping a column that is part of the distribution policy forces a random distribution policy
NOTICE: dropping a column that is part of the distribution policy forces a random distribution policy
NOTICE: dropping a column that is part of the distribution policy forces a random distribution policy
NOTICE: dropping a column that is part of the distribution policy forces a random distribution policy
NOTICE: dropping a column that is part of the distribution policy forces a random distribution policy
NOTICE: dropping a column that is part of the distribution policy forces a random distribution policy
SELECT * FROM pt_lt_tab WHERE col4 is False ORDER BY col2,col3 LIMIT 5;
col2 | col3 | col4
------+------+------
21 | i | f
22 | i | f
23 | i | f
24 | k | f
25 | k | f
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col4 is False ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
-------------------------------------------------------------------------------------------
Limit (cost=0.00..431.00 rows=5 width=8)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=5 width=8)
Merge Key: col2, col3
-> Limit (cost=0.00..431.00 rows=2 width=8)
-> Sort (cost=0.00..431.00 rows=7 width=8)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab (cost=0.00..431.00 rows=7 width=8)
Number of partitions to scan: 5
Filter: (col4 IS FALSE)
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab WHERE col4 = False ORDER BY col2,col3 LIMIT 5;
col2 | col3 | col4
------+------+------
21 | i | f
22 | i | f
23 | i | f
24 | k | f
25 | k | f
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col4 = False ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Limit (cost=0.00..6.01 rows=5 width=8)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.01 rows=5 width=8)
Merge Key: col2, col3
-> Limit (cost=0.00..6.01 rows=2 width=8)
-> Sort (cost=0.00..6.01 rows=10 width=8)
Sort Key: col2, col3
-> Dynamic Index Scan on idx1 on pt_lt_tab (cost=0.00..6.00 rows=10 width=8)
Index Cond: (col4 = false)
Number of partitions to scan: 5
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
SELECT * FROM pt_lt_tab WHERE col2 > 41 ORDER BY col2,col3 LIMIT 5;
col2 | col3 | col4
------+------+------
42 | p | f
43 | p | f
44 | s | f
45 | s | f
46 | s | f
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col2 > 41 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
-------------------------------------------------------------------------------------------
Limit (cost=0.00..431.00 rows=5 width=8)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=5 width=8)
Merge Key: pt_lt_tab_1_prt_part5.col2, pt_lt_tab_1_prt_part5.col3
-> Limit (cost=0.00..431.00 rows=2 width=8)
-> Sort (cost=0.00..431.00 rows=4 width=8)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab (cost=0.00..431.00 rows=4 width=8)
Number of partitions to scan: 1
Filter: (col2 > '41'::numeric)
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
ALTER TABLE pt_lt_tab DROP column col4;
SELECT * FROM pt_lt_tab WHERE col2 > 41 ORDER BY col2,col3 LIMIT 5;
col2 | col3
------+------
42 | p
43 | p
44 | s
45 | s
46 | s
(5 rows)
EXPLAIN SELECT * FROM pt_lt_tab WHERE col2 > 41 ORDER BY col2,col3 LIMIT 5;
QUERY PLAN
-------------------------------------------------------------------------------------------
Limit (cost=0.00..431.00 rows=5 width=7)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=5 width=7)
Merge Key: pt_lt_tab_1_prt_part5.col2, pt_lt_tab_1_prt_part5.col3
-> Limit (cost=0.00..431.00 rows=2 width=7)
-> Sort (cost=0.00..431.00 rows=4 width=7)
Sort Key: col2, col3
-> Dynamic Seq Scan on pt_lt_tab (cost=0.00..431.00 rows=4 width=7)
Number of partitions to scan: 1
Filter: (col2 > '41'::numeric)
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
--
-- Test a more complicated partitioning scheme, with subpartitions.
--
CREATE TABLE pt_complex (i int, j int, k int, l int, m int) DISTRIBUTED BY (i)
PARTITION BY list(k)
SUBPARTITION BY list(j) SUBPARTITION TEMPLATE (subpartition p11 values (1), subpartition p12 values(2))
SUBPARTITION BY list(l) SUBPARTITION TEMPLATE (subpartition p11 values (1), subpartition p12 values(2))
( partition p1 values(1), partition p2 values(2));
INSERT INTO pt_complex VALUES (1, 1, 1, 1, 1), (2, 2, 2, 2, 2);
CREATE INDEX i_pt_complex ON pt_complex (i);
SELECT * FROM pt_complex WHERE i = 1 AND j = 1;
i | j | k | l | m
---+---+---+---+---
1 | 1 | 1 | 1 | 1
(1 row)
EXPLAIN SELECT * FROM pt_complex WHERE i = 1 AND j = 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=4.65..147.85 rows=12 width=20)
-> Append (cost=4.65..147.69 rows=4 width=20)
-> Bitmap Heap Scan on pt_complex_1_prt_p1_2_prt_p11_3_prt_p11 pt_complex_1 (cost=4.65..36.92 rows=1 width=20)
Recheck Cond: (i = 1)
Filter: (j = 1)
-> Bitmap Index Scan on pt_complex_1_prt_p1_2_prt_p11_3_prt_p11_i_idx (cost=0.00..4.65 rows=22 width=0)
Index Cond: (i = 1)
-> Bitmap Heap Scan on pt_complex_1_prt_p1_2_prt_p11_3_prt_p12 pt_complex_2 (cost=4.65..36.92 rows=1 width=20)
Recheck Cond: (i = 1)
Filter: (j = 1)
-> Bitmap Index Scan on pt_complex_1_prt_p1_2_prt_p11_3_prt_p12_i_idx (cost=0.00..4.65 rows=22 width=0)
Index Cond: (i = 1)
-> Bitmap Heap Scan on pt_complex_1_prt_p2_2_prt_p11_3_prt_p11 pt_complex_3 (cost=4.65..36.92 rows=1 width=20)
Recheck Cond: (i = 1)
Filter: (j = 1)
-> Bitmap Index Scan on pt_complex_1_prt_p2_2_prt_p11_3_prt_p11_i_idx (cost=0.00..4.65 rows=22 width=0)
Index Cond: (i = 1)
-> Bitmap Heap Scan on pt_complex_1_prt_p2_2_prt_p11_3_prt_p12 pt_complex_4 (cost=4.65..36.92 rows=1 width=20)
Recheck Cond: (i = 1)
Filter: (j = 1)
-> Bitmap Index Scan on pt_complex_1_prt_p2_2_prt_p11_3_prt_p12_i_idx (cost=0.00..4.65 rows=22 width=0)
Index Cond: (i = 1)
Optimizer: Postgres query optimizer
(23 rows)
--
-- See MPP-6861
--
CREATE TABLE ds_4
(
month_id character varying(6),
cust_group_acc numeric(10),
mobile_no character varying(10),
source character varying(12),
vas_group numeric(10),
vas_type numeric(10),
count_vas integer,
amt_vas numeric(10,2),
network_type character varying(3),
execution_id integer
)
WITH (
OIDS=FALSE
)
DISTRIBUTED BY (cust_group_acc, mobile_no)
PARTITION BY LIST(month_id)
(
PARTITION p200800 VALUES('200800'),
PARTITION p200801 VALUES('200801'),
PARTITION p200802 VALUES('200802'),
PARTITION p200803 VALUES('200803')
);
-- this is the case that worked before MPP-6861
-- start_ignore
-- Known_opt_diff: MPP-21316
-- end_ignore
explain select * from ds_4 where month_id = '200800';
QUERY PLAN
-------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=71)
-> Dynamic Seq Scan on ds_4 (cost=0.00..431.00 rows=1 width=71)
Number of partitions to scan: 1
Filter: ((month_id)::text = '200800'::text)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
-- now we can evaluate this function at planning/prune time
-- start_ignore
-- Known_opt_diff: MPP-21316
-- end_ignore
explain select * from ds_4 where month_id::int = 200800;
QUERY PLAN
-------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=71)
-> Dynamic Seq Scan on ds_4 (cost=0.00..431.00 rows=1 width=71)
Number of partitions to scan: 4
Filter: ((month_id)::integer = 200800)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
-- this will be satisfied by 200800
-- start_ignore
-- Known_opt_diff: MPP-21316
-- end_ignore
explain select * from ds_4 where month_id::int - 801 < 200000;
QUERY PLAN
-------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=71)
-> Dynamic Seq Scan on ds_4 (cost=0.00..431.00 rows=1 width=71)
Number of partitions to scan: 4
Filter: (((month_id)::integer - 801) < 200000)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
-- test OR case -- should NOT get pruning
explain select * from ds_4 where month_id::int - 801 < 200000 OR count_vas > 10;
QUERY PLAN
-------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=71)
-> Dynamic Seq Scan on ds_4 (cost=0.00..431.00 rows=1 width=71)
Number of partitions to scan: 4
Filter: ((((month_id)::integer - 801) < 200000) OR (count_vas > 10))
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
-- test AND case -- should still get pruning
-- start_ignore
-- Known_opt_diff: MPP-21316
-- end_ignore
explain select * from ds_4 where month_id::int - 801 < 200000 AND count_vas > 10;
QUERY PLAN
-------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=71)
-> Dynamic Seq Scan on ds_4 (cost=0.00..431.00 rows=1 width=71)
Number of partitions to scan: 4
Filter: ((((month_id)::integer - 801) < 200000) AND (count_vas > 10))
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
-- test expression case : should get pruning
-- start_ignore
-- Known_opt_diff: MPP-21316
-- end_ignore
explain select * from ds_4 where case when month_id = '200800' then 100 else 2 end = 100;
QUERY PLAN
-------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=71)
-> Dynamic Seq Scan on ds_4 (cost=0.00..431.00 rows=1 width=71)
Number of partitions to scan: 4
Filter: (CASE WHEN ((month_id)::text = '200800'::text) THEN 100 ELSE 2 END = 100)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
-- test expression case : should get pruning
-- start_ignore
-- Known_opt_diff: MPP-21316
-- end_ignore
explain select * from ds_4 where case when month_id = '200800' then NULL else 2 end IS NULL;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=71)
-> Dynamic Seq Scan on ds_4 (cost=0.00..431.00 rows=1 width=71)
Number of partitions to scan: 4
Filter: (CASE WHEN ((month_id)::text = '200800'::text) THEN NULL::integer ELSE 2 END IS NULL)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
-- should still get pruning here -- count_vas is only used in the path for month id = 200800
-- start_ignore
-- Known_opt_diff: MPP-21316
-- end_ignore
explain select * from ds_4 where case when month_id::int = 200800 then count_vas else 2 end IS NULL;
QUERY PLAN
----------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=71)
-> Dynamic Seq Scan on ds_4 (cost=0.00..431.00 rows=1 width=71)
Number of partitions to scan: 4
Filter: (CASE WHEN ((month_id)::integer = 200800) THEN count_vas ELSE 2 END IS NULL)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
-- do one that matches a couple partitions
-- start_ignore
-- Known_opt_diff: MPP-21316
-- end_ignore
explain select * from ds_4 where month_id::int in (200801, 1,55,6,6,6,6,66,565,65,65,200803);
QUERY PLAN
------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=71)
-> Dynamic Seq Scan on ds_4 (cost=0.00..431.00 rows=1 width=71)
Number of partitions to scan: 4
Filter: ((month_id)::integer = ANY ('{200801,1,55,6,6,6,6,66,565,65,65,200803}'::integer[]))
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
-- cleanup
drop table ds_4;
--
-- See MPP-18979
--
CREATE TABLE ds_2
(
month_id character varying(6),
cust_group_acc numeric(10),
mobile_no character varying(10),
source character varying(12),
vas_group numeric(10),
vas_type numeric(10),
count_vas integer,
amt_vas numeric(10,2),
network_type character varying(3),
execution_id integer
)
WITH (
OIDS=FALSE
)
DISTRIBUTED BY (cust_group_acc, mobile_no)
PARTITION BY LIST(month_id)
(
PARTITION p200800 VALUES('200800'),
PARTITION p200801 VALUES('200801'),
PARTITION p200802 VALUES('200802'),
PARTITION p200803 VALUES('200803'),
PARTITION p200804 VALUES('200804'),
PARTITION p200805 VALUES('200805'),
PARTITION p200806 VALUES('200806'),
PARTITION p200807 VALUES('200807'),
PARTITION p200808 VALUES('200808'),
PARTITION p200809 VALUES('200809')
);
insert into ds_2(month_id) values('200800');
insert into ds_2(month_id) values('200801');
insert into ds_2(month_id) values('200802');
insert into ds_2(month_id) values('200803');
insert into ds_2(month_id) values('200804');
insert into ds_2(month_id) values('200805');
insert into ds_2(month_id) values('200806');
insert into ds_2(month_id) values('200807');
insert into ds_2(month_id) values('200808');
insert into ds_2(month_id) values('200809');
-- queries without bitmap scan
-- start_ignore
-- Known_opt_diff: MPP-21316
-- end_ignore
set optimizer_segments=2;
explain select * from ds_2 where month_id::int in (200808, 1315) order by month_id;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=71)
Merge Key: month_id
-> Sort (cost=0.00..431.00 rows=1 width=71)
Sort Key: month_id
-> Dynamic Seq Scan on ds_2 (cost=0.00..431.00 rows=1 width=71)
Number of partitions to scan: 10
Filter: ((month_id)::integer = ANY ('{200808,1315}'::integer[]))
Optimizer: Pivotal Optimizer (GPORCA)
(8 rows)
-- start_ignore
-- Known_opt_diff: MPP-21316
-- end_ignore
explain select * from ds_2 where month_id::int in (200808, 200801, 2008010) order by month_id;
QUERY PLAN
------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=71)
Merge Key: month_id
-> Sort (cost=0.00..431.00 rows=1 width=71)
Sort Key: month_id
-> Dynamic Seq Scan on ds_2 (cost=0.00..431.00 rows=1 width=71)
Number of partitions to scan: 10
Filter: ((month_id)::integer = ANY ('{200808,200801,2008010}'::integer[]))
Optimizer: Pivotal Optimizer (GPORCA)
(8 rows)
reset optimizer_segments;
select * from ds_2 where month_id::int in (200907, 1315) order by month_id;
month_id | cust_group_acc | mobile_no | source | vas_group | vas_type | count_vas | amt_vas | network_type | execution_id
----------+----------------+-----------+--------+-----------+----------+-----------+---------+--------------+--------------
(0 rows)
select * from ds_2 where month_id::int in (200808, 1315) order by month_id;
month_id | cust_group_acc | mobile_no | source | vas_group | vas_type | count_vas | amt_vas | network_type | execution_id
----------+----------------+-----------+--------+-----------+----------+-----------+---------+--------------+--------------
200808 | | | | | | | | |
(1 row)
select * from ds_2 where month_id::int in (200808, 200801) order by month_id;
month_id | cust_group_acc | mobile_no | source | vas_group | vas_type | count_vas | amt_vas | network_type | execution_id
----------+----------------+-----------+--------+-----------+----------+-----------+---------+--------------+--------------
200801 | | | | | | | | |
200808 | | | | | | | | |
(2 rows)
select * from ds_2 where month_id::int in (200808, 200801, 2008010) order by month_id;
month_id | cust_group_acc | mobile_no | source | vas_group | vas_type | count_vas | amt_vas | network_type | execution_id
----------+----------------+-----------+--------+-----------+----------+-----------+---------+--------------+--------------
200801 | | | | | | | | |
200808 | | | | | | | | |
(2 rows)
-- cleanup
drop table ds_2;
drop table if exists dnsdata cascade;
NOTICE: table "dnsdata" does not exist, skipping
CREATE TABLE dnsdata(dnsname text) DISTRIBUTED RANDOMLY;
CREATE INDEX dnsdata_d1_idx ON dnsdata USING bitmap (split_part(reverse(dnsname),'.'::text,1));
CREATE INDEX dnsdata_d2_idx ON dnsdata USING bitmap (split_part(reverse(dnsname),'.'::text,2));
insert into dnsdata values('www.google.com');
insert into dnsdata values('www.google1.com');
insert into dnsdata values('1.google.com');
insert into dnsdata values('2.google.com');
insert into dnsdata select 'www.b.com' from generate_series(1, 100000) as x(a);
analyze dnsdata;
-- queries with bitmap scan enabled
set enable_bitmapscan=on;
set enable_indexscan=on;
set enable_seqscan=off;
Select dnsname from dnsdata
where (split_part(reverse('cache.google.com'),'.',1))=(split_part(reverse(dnsname),'.',1))
and (split_part(reverse('cache.google.com'),'.',2))=(split_part(reverse(dnsname),'.',2))
order by dnsname;
dnsname
----------------
1.google.com
2.google.com
www.google.com
(3 rows)
Select dnsname from dnsdata
where (split_part(reverse('cache.google.com'),'.',1))=(split_part(reverse(dnsname),'.',1))
and (split_part(reverse('cache.google.com'),'.',2))=(split_part(reverse(dnsname),'.',2))
and dnsname = 'cache.google.com'
order by dnsname;
dnsname
---------
(0 rows)
-- cleanup
drop table dnsdata cascade;
Create or replace function ZeroFunc(int) Returns int as $BODY$
BEGIN
RETURN 0;
END;
$BODY$ LANGUAGE plpgsql IMMUTABLE;
drop table if exists mytable cascade;
NOTICE: table "mytable" does not exist, skipping
create table mytable(i int, j int);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into mytable select x, x+1 from generate_series(1, 100000) as x;
analyze mytable;
CREATE INDEX mytable_idx1 ON mytable USING bitmap(ZeroFunc(i));
select * from mytable where ZeroFunc(i)=0 and i=100 order by i;
i | j
-----+-----
100 | 101
(1 row)
select * from mytable where ZeroFunc(i)=0 and i=-1 order by i;
i | j
---+---
(0 rows)
-- cleanup
drop function ZeroFunc(int) cascade;
NOTICE: drop cascades to index mytable_idx1
drop table mytable cascade;
-- @description Tests for static partition selection (MPP-24709, GPSQL-2879)
-- GPDB_12_MERGE_FIXME: this function extract the number such as [5,10] out of
-- the string "Partition selected: 5 out of 10". We have temporarily broken
-- that when we reimplemented dynamic seq scan. Currently only the number of
-- selected partitions is avalable, get the total number of partitions back
-- post merge.
create or replace function get_selected_parts(explain_query text) returns text as
$$
import re
rv = plpy.execute(explain_query)
search_text = 'Dynamic Seq Scan on '
result = []
result.append(0)
result.append(0)
selected = 0
out_of = 0
pattern = re.compile(r"\s+Number of partitions to scan: (?P<selected>\d+)")
for i in range(len(rv)):
cur_line = rv[i]['QUERY PLAN']
if search_text.lower() in cur_line.lower():
j = i+1
temp_line = rv[j]['QUERY PLAN']
while (not pattern.match(temp_line)):
j += 1
if j == len(rv) - 1:
break
temp_line = rv[j]['QUERY PLAN']
else:
selected += int(pattern.match(temp_line).group('selected'))
result[0] = selected
result[1] = out_of
return result
$$
language plpython3u;
drop table if exists partprune_foo;
create table partprune_foo(a int, b int, c int) partition by range (b) (start (1) end (101) every (10));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into partprune_foo select g % 5 + 1, g + 1, g % 10 + 1 from generate_series(0, 99) g;
analyze partprune_foo;
select get_selected_parts('explain select * from partprune_foo;');
get_selected_parts
--------------------
[10, 0]
(1 row)
select * from partprune_foo;
a | b | c
---+-----+----
1 | 1 | 1
3 | 3 | 3
5 | 5 | 5
1 | 6 | 6
3 | 8 | 8
5 | 10 | 10
1 | 11 | 1
3 | 13 | 3
5 | 15 | 5
1 | 16 | 6
3 | 18 | 8
5 | 20 | 10
1 | 21 | 1
3 | 23 | 3
5 | 25 | 5
1 | 26 | 6
3 | 28 | 8
5 | 30 | 10
1 | 31 | 1
3 | 33 | 3
5 | 35 | 5
1 | 36 | 6
3 | 38 | 8
5 | 40 | 10
1 | 41 | 1
3 | 43 | 3
5 | 45 | 5
1 | 46 | 6
3 | 48 | 8
5 | 50 | 10
1 | 51 | 1
3 | 53 | 3
5 | 55 | 5
1 | 56 | 6
3 | 58 | 8
5 | 60 | 10
1 | 61 | 1
3 | 63 | 3
5 | 65 | 5
1 | 66 | 6
3 | 68 | 8
5 | 70 | 10
1 | 71 | 1
3 | 73 | 3
5 | 75 | 5
1 | 76 | 6
3 | 78 | 8
5 | 80 | 10
1 | 81 | 1
3 | 83 | 3
5 | 85 | 5
1 | 86 | 6
3 | 88 | 8
5 | 90 | 10
1 | 91 | 1
3 | 93 | 3
5 | 95 | 5
1 | 96 | 6
3 | 98 | 8
5 | 100 | 10
2 | 2 | 2
4 | 4 | 4
2 | 7 | 7
4 | 9 | 9
2 | 12 | 2
4 | 14 | 4
2 | 17 | 7
4 | 19 | 9
2 | 22 | 2
4 | 24 | 4
2 | 27 | 7
4 | 29 | 9
2 | 32 | 2
4 | 34 | 4
2 | 37 | 7
4 | 39 | 9
2 | 42 | 2
4 | 44 | 4
2 | 47 | 7
4 | 49 | 9
2 | 52 | 2
4 | 54 | 4
2 | 57 | 7
4 | 59 | 9
2 | 62 | 2
4 | 64 | 4
2 | 67 | 7
4 | 69 | 9
2 | 72 | 2
4 | 74 | 4
2 | 77 | 7
4 | 79 | 9
2 | 82 | 2
4 | 84 | 4
2 | 87 | 7
4 | 89 | 9
2 | 92 | 2
4 | 94 | 4
2 | 97 | 7
4 | 99 | 9
(100 rows)
select get_selected_parts('explain select * from partprune_foo where b = 35;');
get_selected_parts
--------------------
[1, 0]
(1 row)
select * from partprune_foo where b = 35;
a | b | c
---+----+---
5 | 35 | 5
(1 row)
select get_selected_parts('explain select * from partprune_foo where b < 35;');
get_selected_parts
--------------------
[4, 0]
(1 row)
select * from partprune_foo where b < 35;
a | b | c
---+----+----
2 | 2 | 2
4 | 4 | 4
2 | 7 | 7
4 | 9 | 9
2 | 12 | 2
4 | 14 | 4
2 | 17 | 7
4 | 19 | 9
2 | 22 | 2
4 | 24 | 4
2 | 27 | 7
4 | 29 | 9
2 | 32 | 2
4 | 34 | 4
1 | 1 | 1
3 | 3 | 3
5 | 5 | 5
1 | 6 | 6
3 | 8 | 8
5 | 10 | 10
1 | 11 | 1
3 | 13 | 3
5 | 15 | 5
1 | 16 | 6
3 | 18 | 8
5 | 20 | 10
1 | 21 | 1
3 | 23 | 3
5 | 25 | 5
1 | 26 | 6
3 | 28 | 8
5 | 30 | 10
1 | 31 | 1
3 | 33 | 3
(34 rows)
select get_selected_parts('explain select * from partprune_foo where b in (5, 6, 14, 23);');
get_selected_parts
--------------------
[3, 0]
(1 row)
select * from partprune_foo where b in (5, 6, 14, 23);
a | b | c
---+----+---
5 | 5 | 5
1 | 6 | 6
3 | 23 | 3
4 | 14 | 4
(4 rows)
select get_selected_parts('explain select * from partprune_foo where b < 15 or b > 60;');
get_selected_parts
--------------------
[7, 0]
(1 row)
select * from partprune_foo where b < 15 or b > 60;
a | b | c
---+-----+----
2 | 2 | 2
4 | 4 | 4
2 | 7 | 7
4 | 9 | 9
2 | 12 | 2
4 | 14 | 4
2 | 62 | 2
4 | 64 | 4
2 | 67 | 7
4 | 69 | 9
2 | 72 | 2
4 | 74 | 4
2 | 77 | 7
4 | 79 | 9
2 | 82 | 2
4 | 84 | 4
2 | 87 | 7
4 | 89 | 9
2 | 92 | 2
4 | 94 | 4
2 | 97 | 7
4 | 99 | 9
1 | 1 | 1
3 | 3 | 3
5 | 5 | 5
1 | 6 | 6
3 | 8 | 8
5 | 10 | 10
1 | 11 | 1
3 | 13 | 3
1 | 61 | 1
3 | 63 | 3
5 | 65 | 5
1 | 66 | 6
3 | 68 | 8
5 | 70 | 10
1 | 71 | 1
3 | 73 | 3
5 | 75 | 5
1 | 76 | 6
3 | 78 | 8
5 | 80 | 10
1 | 81 | 1
3 | 83 | 3
5 | 85 | 5
1 | 86 | 6
3 | 88 | 8
5 | 90 | 10
1 | 91 | 1
3 | 93 | 3
5 | 95 | 5
1 | 96 | 6
3 | 98 | 8
5 | 100 | 10
(54 rows)
select get_selected_parts('explain select * from partprune_foo where b = 150;');
get_selected_parts
--------------------
[0, 0]
(1 row)
select * from partprune_foo where b = 150;
a | b | c
---+---+---
(0 rows)
select get_selected_parts('explain select * from partprune_foo where b = a*5;');
get_selected_parts
--------------------
[10, 0]
(1 row)
select * from partprune_foo where b = a*5;
a | b | c
---+----+---
5 | 25 | 5
(1 row)
-- Test with IN() lists
-- Number of elements > threshold, partition elimination is not performed
set optimizer_array_expansion_threshold = 3;
select get_selected_parts('explain select * from partprune_foo where b in (5, 6, 14, 23);');
get_selected_parts
--------------------
[3, 0]
(1 row)
select * from partprune_foo where b in (5, 6, 14, 23);
a | b | c
---+----+---
3 | 23 | 3
5 | 5 | 5
4 | 14 | 4
1 | 6 | 6
(4 rows)
reset optimizer_array_expansion_threshold;
-- Test "ANY (<array>)" syntax.
select get_selected_parts($$ explain select * from partprune_foo where b = ANY ('{5, 6, 14}') $$);
get_selected_parts
--------------------
[2, 0]
(1 row)
select * from partprune_foo where b = ANY ('{5, 6, 14}');
a | b | c
---+----+---
1 | 6 | 6
5 | 5 | 5
4 | 14 | 4
(3 rows)
select get_selected_parts($$ explain select * from partprune_foo where b < ANY ('{12, 14, 11}') $$);
get_selected_parts
--------------------
[2, 0]
(1 row)
select * from partprune_foo where b < ANY ('{12, 14, 11}');
a | b | c
---+----+----
1 | 1 | 1
2 | 2 | 2
1 | 6 | 6
2 | 7 | 7
1 | 11 | 1
2 | 12 | 2
3 | 3 | 3
4 | 4 | 4
5 | 5 | 5
3 | 8 | 8
4 | 9 | 9
5 | 10 | 10
3 | 13 | 3
(13 rows)
-- Check for all the different number of partition selections
DROP TABLE IF EXISTS DATE_PARTS;
NOTICE: table "date_parts" does not exist, skipping
CREATE TABLE DATE_PARTS (id int, year int, month int, day int, region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
SUBPARTITION BY LIST (month)
SUBPARTITION TEMPLATE (
SUBPARTITION Q1 VALUES (1, 2, 3),
SUBPARTITION Q2 VALUES (4 ,5 ,6),
SUBPARTITION Q3 VALUES (7, 8, 9),
SUBPARTITION Q4 VALUES (10, 11, 12),
DEFAULT SUBPARTITION other_months )
SUBPARTITION BY RANGE(day)
SUBPARTITION TEMPLATE (
START (1) END (31) EVERY (10),
DEFAULT SUBPARTITION other_days)
( START (2002) END (2012) EVERY (4),
DEFAULT PARTITION outlying_years );
insert into DATE_PARTS select i, extract(year from dt), extract(month from dt), extract(day from dt), NULL from (select i, '2002-01-01'::date + i * interval '1 day' day as dt from generate_series(1, 3650) as i) as t;
-- Expected total parts => 4 * 1 * 4 => 16:
-- TODO #141973839: we selected extra parts because of disjunction: 32 parts: 4 * 2 * 4
select get_selected_parts('explain analyze select * from DATE_PARTS where month between 1 and 3;');
get_selected_parts
--------------------
[0, 0]
(1 row)
-- Expected total parts => 4 * 2 * 4 => 32:
-- TODO #141973839: we selected extra parts because of disjunction: 48 parts: 4 * 3 * 4
select get_selected_parts('explain analyze select * from DATE_PARTS where month between 1 and 4;');
get_selected_parts
--------------------
[0, 0]
(1 row)
-- Expected total parts => 1 * 2 * 4 => 8:
-- TODO #141973839: we selected extra parts because of disjunction: 24 parts: 2 * 3 * 4
select get_selected_parts('explain analyze select * from DATE_PARTS where year = 2003 and month between 1 and 4;');
get_selected_parts
--------------------
[0, 0]
(1 row)
-- 1 :: 5 :: 4 => 20 // Only default for year
select get_selected_parts('explain analyze select * from DATE_PARTS where year = 1999;');
get_selected_parts
--------------------
[0, 0]
(1 row)
-- 4 :: 1 :: 4 => 16 // Only default for month
select get_selected_parts('explain analyze select * from DATE_PARTS where month = 13;');
get_selected_parts
--------------------
[0, 0]
(1 row)
-- 1 :: 1 :: 4 => 4 // Default for both year and month
select get_selected_parts('explain analyze select * from DATE_PARTS where year = 1999 and month = 13;');
get_selected_parts
--------------------
[0, 0]
(1 row)
-- 4 :: 5 :: 1 => 20 // Only default part for day
select get_selected_parts('explain analyze select * from DATE_PARTS where day = 40;');
get_selected_parts
--------------------
[0, 0]
(1 row)
-- General predicate
-- TODO #141973839. We expected 112 parts: (month = 1) => 4 * 1 * 4 => 16, month > 3 => 4 * 4 * 4 => 64, month in (0, 1, 2) => 4 * 1 * 4 => 16, month is NULL => 4 * 1 * 4 => 16.
-- However, we selected 128 parts: (month = 1) => 4 * 1 * 4 => 16, month > 3 => 4 * 4 * 4 => 64, month in (0, 1, 2) => 4 * 2 * 4 => 32, month is NULL => 4 * 1 * 4 => 16.
select get_selected_parts('explain analyze select * from DATE_PARTS where month = 1 union all select * from DATE_PARTS where month > 3 union all select * from DATE_PARTS where month in (0,1,2) union all select * from DATE_PARTS where month is null;');
get_selected_parts
--------------------
[0, 0]
(1 row)
-- Equality predicate
-- 16 partitions => 4 from year x 1 from month x 4 from days.
select get_selected_parts('explain analyze select * from DATE_PARTS where month = 3;'); -- Not working (it only produces general)
get_selected_parts
--------------------
[0, 0]
(1 row)
-- More Equality and General Predicates ---
create table foo(a int, b int)
partition by list (b)
(partition p1 values(1,3), partition p2 values(4,2), default partition other);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
-- General predicate
-- Total 6 parts. b = 1: 1 part, b > 3: 2 parts, b in (0, 1): 2 parts. b is null: 1 part
select get_selected_parts('explain analyze select * from foo where b = 1 union all select * from foo where b > 3 union all select * from foo where b in (0,1) union all select * from foo where b is null;');
get_selected_parts
--------------------
[6, 0]
(1 row)
drop table if exists pt;
NOTICE: table "pt" does not exist, skipping
CREATE TABLE pt (id int, gender varchar(2))
DISTRIBUTED BY (id)
PARTITION BY LIST (gender)
( PARTITION girls VALUES ('F', NULL),
PARTITION boys VALUES ('M'),
DEFAULT PARTITION other );
-- General filter
-- TODO #141916623. Expecting 6 parts, but optimizer plan selects 7 parts. The 6 parts breakdown is: gender = 'F': 1 part, gender < 'M': 2 parts (including default), gender in ('F', F'M'): 2 parts, gender is null => 1 part
select get_selected_parts('explain analyze select * from pt where gender = ''F'' union all select * from pt where gender < ''M'' union all select * from pt where gender in (''F'', ''FM'') union all select * from pt where gender is null;');
get_selected_parts
--------------------
[6, 0]
(1 row)
-- DML
-- Non-default part
insert into DATE_PARTS values (-1, 2004, 11, 30, NULL);
select * from date_parts_1_prt_2_2_prt_q4_3_prt_4 where id < 0;
id | year | month | day | region
----+------+-------+-----+--------
-1 | 2004 | 11 | 30 |
(1 row)
-- Default year
insert into DATE_PARTS values (-2, 1999, 11, 30, NULL);
select * from date_parts_1_prt_outlying_years_2_prt_q4_3_prt_4 where id < 0;
id | year | month | day | region
----+------+-------+-----+--------
-2 | 1999 | 11 | 30 |
(1 row)
-- Default month
insert into DATE_PARTS values (-3, 2004, 20, 30, NULL);
select * from date_parts_1_prt_2_2_prt_other_months where id < 0;
id | year | month | day | region
----+------+-------+-----+--------
-3 | 2004 | 20 | 30 |
(1 row)
-- Default day
insert into DATE_PARTS values (-4, 2004, 10, 50, NULL);
select * from date_parts_1_prt_2_2_prt_q4_3_prt_other_days where id < 0;
id | year | month | day | region
----+------+-------+-----+--------
-4 | 2004 | 10 | 50 |
(1 row)
-- Default everything
insert into DATE_PARTS values (-5, 1999, 20, 50, NULL);
select * from date_parts_1_prt_outlying_years_2_prt_other_mo_3_prt_other_days where id < 0;
id | year | month | day | region
----+------+-------+-----+--------
-5 | 1999 | 20 | 50 |
(1 row)
-- Default month + day but not year
insert into DATE_PARTS values (-6, 2002, 20, 50, NULL);
select * from date_parts_1_prt_2_2_prt_other_months_3_prt_other_days where id < 0;
id | year | month | day | region
----+------+-------+-----+--------
-6 | 2002 | 20 | 50 |
(1 row)
-- Dropped columns with exchange
drop table if exists sales;
NOTICE: table "sales" does not exist, skipping
CREATE TABLE sales (trans_id int, to_be_dropped1 int, date date, amount
decimal(9,2), to_be_dropped2 int, region text)
DISTRIBUTED BY (trans_id)
PARTITION BY RANGE (date)
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'),
SUBPARTITION asia VALUES ('asia'),
SUBPARTITION europe VALUES ('europe'),
DEFAULT SUBPARTITION other_regions)
(START (date '2011-01-01') INCLUSIVE
END (date '2012-01-01') EXCLUSIVE
EVERY (INTERVAL '3 month'),
DEFAULT PARTITION outlying_dates );
-- This will introduce different column numbers in subsequent part tables
alter table sales drop column to_be_dropped1;
alter table sales drop column to_be_dropped2;
-- Create the exchange candidate without dropped columns
drop table if exists sales_exchange_part;
NOTICE: table "sales_exchange_part" does not exist, skipping
create table sales_exchange_part (trans_id int, date date, amount
decimal(9,2), region text);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'trans_id' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
-- Insert some data
insert into sales_exchange_part values(1, '2011-01-01', 10.1, 'usa');
-- Exchange
ALTER TABLE sales
ALTER PARTITION FOR (date '2011-01-01')
EXCHANGE PARTITION FOR ('usa') WITH TABLE sales_exchange_part ;
ANALYZE sales;
-- TODO: #141973839. Expected 10 parts, currently selecting 15 parts. First level: 4 parts + 1 default. Second level 2 parts. Total 10 parts.
select get_selected_parts('explain analyze select * from sales where region = ''usa'' or region = ''asia'';');
get_selected_parts
--------------------
[0, 0]
(1 row)
select * from sales where region = 'usa' or region = 'asia';
trans_id | date | amount | region
----------+------------+--------+--------
1 | 01-01-2011 | 10.10 | usa
(1 row)
-- Test DynamicIndexScan with extra filter
create index idx_sales_date on sales(date);
explain select * from sales where date = '2011-01-01' and region = 'usa';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.12..8.16 rows=1 width=19)
-> Index Scan using sales_1_prt_2_2_prt_usa_date_idx on sales_1_prt_2_2_prt_usa sales (cost=0.12..8.14 rows=1 width=19)
Index Cond: (date = '01-01-2011'::date)
Filter: (region = 'usa'::text)
Optimizer: Postgres query optimizer
(5 rows)
select * from sales where date = '2011-01-01' and region = 'usa';
trans_id | date | amount | region
----------+------------+--------+--------
1 | 01-01-2011 | 10.10 | usa
(1 row)
-- Updating partition key
select * from sales_1_prt_2_2_prt_usa;
trans_id | date | amount | region
----------+------------+--------+--------
1 | 01-01-2011 | 10.10 | usa
(1 row)
select * from sales_1_prt_2_2_prt_europe;
trans_id | date | amount | region
----------+------+--------+--------
(0 rows)
update sales set region = 'europe' where trans_id = 1;
select * from sales_1_prt_2_2_prt_europe;
trans_id | date | amount | region
----------+------------+--------+--------
1 | 01-01-2011 | 10.10 | europe
(1 row)
select * from sales_1_prt_2_2_prt_usa;
trans_id | date | amount | region
----------+------+--------+--------
(0 rows)
select * from sales;
trans_id | date | amount | region
----------+------------+--------+--------
1 | 01-01-2011 | 10.10 | europe
(1 row)
-- Distinct From
drop table if exists bar;
NOTICE: table "bar" does not exist, skipping
CREATE TABLE bar (i INTEGER, j decimal)
partition by list (j)
subpartition by range (i) subpartition template (start(1) end(4) every(2))
(partition p1 values(0.2,2.8, NULL), partition p2 values(1.7,3.1),
partition p3 values(5.6), default partition other);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into bar values(1, 0.2); --p1
insert into bar values(1, 1.7); --p2
insert into bar values(1, 2.1); --default
insert into bar values(1, 5.6); --default
insert into bar values(1, NULL); --p1
-- In-equality
-- 8 parts: All 4 parts on first level and each will have 2 range parts
select get_selected_parts('explain analyze select * from bar where j>0.02;');
get_selected_parts
--------------------
[0, 0]
(1 row)
-- 6 parts: Excluding 1 list parts at first level. So, 3 at first level and each has 2 at second level.
select get_selected_parts('explain analyze select * from bar where j>2.8;');
get_selected_parts
--------------------
[0, 0]
(1 row)
-- Distinct From
-- 6 parts: Everything except 1 part that contains 5.6.
select get_selected_parts('explain analyze select * from bar where j is distinct from 5.6;');
get_selected_parts
--------------------
[0, 0]
(1 row)
-- 8 parts: NULL is shared with others on p1. So, all 8 parts.
select get_selected_parts('explain analyze select * from bar where j is distinct from NULL;');
get_selected_parts
--------------------
[0, 0]
(1 row)
-- Table partitioned by boolean column
CREATE TABLE pt_bool_tab
(
col1 int,
col2 bool
)
distributed by (col1)
partition by list(col2)
(
partition part1 values(true),
partition part2 values(false)
);
INSERT INTO pt_bool_tab SELECT i, true FROM generate_series(1,3)i;
INSERT INTO pt_bool_tab SELECT i, false FROM generate_series(1,2)i;
EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS true;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 1 (out of 2)
Filter: (col2 IS TRUE)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab WHERE col2 IS true;
col1 | col2
------+------
1 | t
2 | t
3 | t
(3 rows)
EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS false;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 1 (out of 2)
Filter: (col2 IS FALSE)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab WHERE col2 IS false;
col1 | col2
------+------
2 | f
1 | f
(2 rows)
EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NULL;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.00 rows=0 width=5)
One-Time Filter: false
Optimizer: Pivotal Optimizer (GPORCA)
(3 rows)
SELECT * FROM pt_bool_tab WHERE col2 IS NULL;
col1 | col2
------+------
(0 rows)
EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS unknown;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.00 rows=0 width=5)
One-Time Filter: false
Optimizer: GPORCA
(3 rows)
SELECT * FROM pt_bool_tab WHERE col2 IS unknown;
col1 | col2
------+------
(0 rows)
EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NOT true;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 1 (out of 2)
Filter: (col2 IS NOT TRUE)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab WHERE col2 IS NOT true;
col1 | col2
------+------
2 | f
1 | f
(2 rows)
EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NOT false;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 1 (out of 2)
Filter: (col2 IS NOT FALSE)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab WHERE col2 IS NOT false;
col1 | col2
------+------
2 | t
3 | t
1 | t
(3 rows)
EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NOT unknown;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 2 (out of 2)
Filter: (col2 IS NOT UNKNOWN)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab WHERE col2 IS NOT unknown;
col1 | col2
------+------
1 | f
1 | t
2 | f
2 | t
3 | t
(5 rows)
EXPLAIN SELECT * FROM pt_bool_tab WHERE col2 IS NOT NULL;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 2 (out of 2)
Filter: (NOT (col2 IS NULL))
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab WHERE col2 IS NOT NULL;
col1 | col2
------+------
1 | f
1 | t
2 | f
2 | t
3 | t
(5 rows)
EXPLAIN SELECT * FROM pt_bool_tab WHERE (not col2) IS true;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 1 (out of 2)
Filter: ((NOT col2) IS TRUE)
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab WHERE (not col2) IS true;
col1 | col2
------+------
2 | f
1 | f
(2 rows)
EXPLAIN SELECT * FROM pt_bool_tab WHERE (not col2) IS false;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 1 (out of 2)
Filter: ((NOT col2) IS FALSE)
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab WHERE (not col2) IS false;
col1 | col2
------+------
2 | t
3 | t
1 | t
(3 rows)
EXPLAIN SELECT * FROM pt_bool_tab WHERE (not col2) IS NULL;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 2 (out of 2)
Filter: ((NOT col2) IS NULL)
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab WHERE (not col2) IS NULL;
col1 | col2
------+------
(0 rows)
EXPLAIN SELECT * FROM pt_bool_tab WHERE (not col2) IS unknown;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 2 (out of 2)
Filter: ((NOT col2) IS UNKNOWN)
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab WHERE (not col2) IS unknown;
col1 | col2
------+------
(0 rows)
EXPLAIN SELECT * FROM pt_bool_tab WHERE (not col2) IS NOT true;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 1 (out of 2)
Filter: ((NOT col2) IS NOT TRUE)
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab WHERE (not col2) IS NOT true;
col1 | col2
------+------
2 | t
3 | t
1 | t
(3 rows)
EXPLAIN SELECT * FROM pt_bool_tab WHERE (not col2) IS NOT false;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 1 (out of 2)
Filter: ((NOT col2) IS NOT FALSE)
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab WHERE (not col2) IS NOT false;
col1 | col2
------+------
2 | f
1 | f
(2 rows)
EXPLAIN SELECT * FROM pt_bool_tab WHERE (not col2) IS NOT unknown;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 2 (out of 2)
Filter: ((NOT col2) IS NOT UNKNOWN)
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab WHERE (not col2) IS NOT unknown;
col1 | col2
------+------
1 | f
1 | t
2 | f
2 | t
3 | t
(5 rows)
EXPLAIN SELECT * FROM pt_bool_tab WHERE (not col2) IS NOT NULL;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 2 (out of 2)
Filter: (NOT ((NOT col2) IS NULL))
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab WHERE (not col2) IS NOT NULL;
col1 | col2
------+------
1 | f
1 | t
2 | f
2 | t
3 | t
(5 rows)
CREATE TABLE pt_bool_tab_df
(
col1 int,
col2 bool
)
distributed by (col1)
partition by list(col2)
(
partition part1 values(true),
partition part2 values(false),
default partition def
);
INSERT INTO pt_bool_tab_df SELECT i, true FROM generate_series(1,3)i;
INSERT INTO pt_bool_tab_df SELECT i, false FROM generate_series(1,2)i;
INSERT INTO pt_bool_tab_df SELECT i, NULL FROM generate_series(1,1)i;
EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS true;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 1 (out of 3)
Filter: (col2 IS TRUE)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab_df WHERE col2 IS true;
col1 | col2
------+------
1 | t
2 | t
3 | t
(3 rows)
EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS false;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 1 (out of 3)
Filter: (col2 IS FALSE)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab_df WHERE col2 IS false;
col1 | col2
------+------
2 | f
1 | f
(2 rows)
EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NULL;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 1 (out of 3)
Filter: (col2 IS NULL)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab_df WHERE col2 IS NULL;
col1 | col2
------+------
1 |
(1 row)
EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS unknown;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 1 (out of 3)
Filter: (col2 IS UNKNOWN)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab_df WHERE col2 IS unknown;
col1 | col2
------+------
1 |
(1 row)
EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT true;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 2 (out of 3)
Filter: (col2 IS NOT TRUE)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT true;
col1 | col2
------+------
2 | f
1 | f
1 |
(3 rows)
EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT false;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 2 (out of 3)
Filter: (col2 IS NOT FALSE)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT false;
col1 | col2
------+------
2 | t
3 | t
1 | t
1 |
(4 rows)
EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT unknown;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 2 (out of 3)
Filter: (col2 IS NOT UNKNOWN)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT unknown;
col1 | col2
------+------
2 | f
2 | t
3 | t
1 | f
1 | t
(5 rows)
EXPLAIN SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT NULL;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 2 (out of 3)
Filter: (NOT (col2 IS NULL))
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT NULL;
col1 | col2
------+------
2 | f
2 | t
3 | t
1 | f
1 | t
(5 rows)
EXPLAIN SELECT * FROM pt_bool_tab_df WHERE (not col2) IS true;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 1 (out of 3)
Filter: ((NOT col2) IS TRUE)
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab_df WHERE (not col2) IS true;
col1 | col2
------+------
2 | f
1 | f
(2 rows)
EXPLAIN SELECT * FROM pt_bool_tab_df WHERE (not col2) IS false;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 1 (out of 3)
Filter: ((NOT col2) IS FALSE)
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab_df WHERE (not col2) IS false;
col1 | col2
------+------
2 | t
3 | t
1 | t
(3 rows)
EXPLAIN SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NULL;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 3 (out of 3)
Filter: ((NOT col2) IS NULL)
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NULL;
col1 | col2
------+------
1 |
(1 row)
EXPLAIN SELECT * FROM pt_bool_tab_df WHERE (not col2) IS unknown;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 3 (out of 3)
Filter: ((NOT col2) IS UNKNOWN)
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab_df WHERE (not col2) IS unknown;
col1 | col2
------+------
1 |
(1 row)
EXPLAIN SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NOT true;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 2 (out of 3)
Filter: ((NOT col2) IS NOT TRUE)
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NOT true;
col1 | col2
------+------
2 | t
3 | t
1 | t
1 |
(4 rows)
EXPLAIN SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NOT false;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 2 (out of 3)
Filter: ((NOT col2) IS NOT FALSE)
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NOT false;
col1 | col2
------+------
2 | f
1 | f
1 |
(3 rows)
EXPLAIN SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NOT unknown;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 3 (out of 3)
Filter: ((NOT col2) IS NOT UNKNOWN)
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NOT unknown;
col1 | col2
------+------
2 | f
2 | t
3 | t
1 | f
1 | t
(5 rows)
EXPLAIN SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NOT NULL;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_df (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 3 (out of 3)
Filter: (NOT ((NOT col2) IS NULL))
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NOT NULL;
col1 | col2
------+------
2 | f
2 | t
3 | t
1 | f
1 | t
(5 rows)
CREATE TABLE pt_bool_tab_null
(
col1 int,
col2 bool
)
distributed by (col1)
partition by list(col2)
(
partition part1 values(true),
partition part2 values(false),
partition part3 values(null)
);
INSERT INTO pt_bool_tab_null SELECT i, true FROM generate_series(1,3)i;
INSERT INTO pt_bool_tab_null SELECT i, false FROM generate_series(1,2)i;
INSERT INTO pt_bool_tab_null SELECT i, NULL FROM generate_series(1,1)i;
EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS true;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 1 (out of 3)
Filter: (col2 IS TRUE)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab_null WHERE col2 IS true;
col1 | col2
------+------
1 | t
2 | t
3 | t
(3 rows)
EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS false;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 1 (out of 3)
Filter: (col2 IS FALSE)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab_null WHERE col2 IS false;
col1 | col2
------+------
1 | f
2 | f
(2 rows)
EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NULL;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 1 (out of 3)
Filter: (col2 IS NULL)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab_null WHERE col2 IS NULL;
col1 | col2
------+------
1 |
(1 row)
EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS unknown;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 1 (out of 3)
Filter: (col2 IS UNKNOWN)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab_null WHERE col2 IS unknown;
col1 | col2
------+------
1 |
(1 row)
EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT true;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 2 (out of 3)
Filter: (col2 IS NOT TRUE)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT true;
col1 | col2
------+------
1 | f
2 | f
1 |
(3 rows)
EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT false;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 2 (out of 3)
Filter: (col2 IS NOT FALSE)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT false;
col1 | col2
------+------
2 | t
3 | t
1 | t
1 |
(4 rows)
EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT unknown;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 2 (out of 3)
Filter: (col2 IS NOT UNKNOWN)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT unknown;
col1 | col2
------+------
2 | f
2 | t
3 | t
1 | f
1 | t
(5 rows)
EXPLAIN SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT NULL;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 2 (out of 3)
Filter: (NOT (col2 IS NULL))
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT NULL;
col1 | col2
------+------
2 | f
2 | t
3 | t
1 | f
1 | t
(5 rows)
EXPLAIN SELECT * FROM pt_bool_tab_null WHERE (not col2) IS true;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 1 (out of 3)
Filter: ((NOT col2) IS TRUE)
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab_null WHERE (not col2) IS true;
col1 | col2
------+------
1 | f
2 | f
(2 rows)
EXPLAIN SELECT * FROM pt_bool_tab_null WHERE (not col2) IS false;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 1 (out of 3)
Filter: ((NOT col2) IS FALSE)
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab_null WHERE (not col2) IS false;
col1 | col2
------+------
1 | t
2 | t
3 | t
(3 rows)
EXPLAIN SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NULL;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 3 (out of 3)
Filter: ((NOT col2) IS NULL)
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NULL;
col1 | col2
------+------
1 |
(1 row)
EXPLAIN SELECT * FROM pt_bool_tab_null WHERE (not col2) IS unknown;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 3 (out of 3)
Filter: ((NOT col2) IS UNKNOWN)
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab_null WHERE (not col2) IS unknown;
col1 | col2
------+------
1 |
(1 row)
EXPLAIN SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NOT true;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 2 (out of 3)
Filter: ((NOT col2) IS NOT TRUE)
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NOT true;
col1 | col2
------+------
1 | t
1 |
2 | t
3 | t
(4 rows)
EXPLAIN SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NOT false;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 2 (out of 3)
Filter: ((NOT col2) IS NOT FALSE)
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NOT false;
col1 | col2
------+------
1 | f
1 |
2 | f
(3 rows)
EXPLAIN SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NOT unknown;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 3 (out of 3)
Filter: ((NOT col2) IS NOT UNKNOWN)
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NOT unknown;
col1 | col2
------+------
1 | f
1 | t
2 | f
2 | t
3 | t
(5 rows)
EXPLAIN SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NOT NULL;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=5)
-> Dynamic Seq Scan on pt_bool_tab_null (cost=0.00..431.00 rows=1 width=5)
Number of partitions to scan: 3 (out of 3)
Filter: (NOT ((NOT col2) IS NULL))
Optimizer: GPORCA
(5 rows)
SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NOT NULL;
col1 | col2
------+------
1 | f
1 | t
2 | f
2 | t
3 | t
(5 rows)
RESET ALL;