| -- Test the optimizer_enable_dml_constraints GUC, which forces GPORCA to fall back when there |
| -- are NULL or CHECK constraints on a table. |
| set optimizer_trace_fallback = on; |
| DROP TABLE IF EXISTS constr_tab; |
| NOTICE: table "constr_tab" does not exist, skipping |
| CREATE TABLE constr_tab ( a int check (a>0) , b int, c int, d int, CHECK (a+b>5)) DISTRIBUTED BY (a); |
| set optimizer_enable_dml_constraints = off; |
| explain insert into constr_tab values (1,2,3); |
| QUERY PLAN |
| ------------------------------------------------ |
| Insert on constr_tab (cost=0.00..0.01 rows=1 width=0) |
| -> Result (cost=0.00..0.01 rows=1 width=0) |
| Optimizer status: Postgres query optimizer |
| (3 rows) |
| |
| set optimizer_enable_dml_constraints=on; |
| explain insert into constr_tab values (1,2,3); |
| QUERY PLAN |
| ------------------------------------------------ |
| Insert on constr_tab (cost=0.00..0.01 rows=1 width=0) |
| -> Result (cost=0.00..0.01 rows=1 width=0) |
| Optimizer status: Postgres query optimizer |
| (3 rows) |
| |
| -- The remaining tests require a row in the table. |
| INSERT INTO constr_tab VALUES(1,5,3,4); |
| set optimizer_enable_dml_constraints=off; |
| explain update constr_tab set a = 10; |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------------------- |
| Update on constr_tab (cost=0.00..1219.00 rows=0 width=0) |
| -> Explicit Redistribute Motion 3:3 (slice1; segments: 3) (cost=0.00..1219.00 rows=47400 width=26) |
| -> Split Update (cost=0.00..271.00 rows=47400 width=26) |
| -> Seq Scan on constr_tab (cost=0.00..271.00 rows=23700 width=26) |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| explain update constr_tab set b = 10; |
| QUERY PLAN |
| ----------------------------------------------------------------- |
| Update on constr_tab (cost=0.00..1.01 rows=1 width=22) |
| -> Seq Scan on constr_tab (cost=0.00..1.01 rows=1 width=22) |
| Optimizer status: Postgres query optimizer |
| (3 rows) |
| |
| set optimizer_enable_dml_constraints=on; |
| explain update constr_tab set b = 10; |
| QUERY PLAN |
| ----------------------------------------------------------------- |
| Update on constr_tab (cost=0.00..1.01 rows=1 width=22) |
| -> Seq Scan on constr_tab (cost=0.00..1.01 rows=1 width=22) |
| Optimizer status: Postgres query optimizer |
| (3 rows) |
| |
| -- Same, with NOT NULL constraint. |
| DROP TABLE IF EXISTS constr_tab; |
| CREATE TABLE constr_tab ( a int NOT NULL, b int, c int, d int, CHECK (a+b>5)) DISTRIBUTED BY (a); |
| INSERT INTO constr_tab VALUES(1,5,3,4); |
| set optimizer_enable_dml_constraints=off; |
| explain update constr_tab set a = 10; |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------------------- |
| Update on constr_tab (cost=0.00..1219.00 rows=0 width=0) |
| -> Explicit Redistribute Motion 3:3 (slice1; segments: 3) (cost=0.00..1219.00 rows=47400 width=26) |
| -> Split Update (cost=0.00..271.00 rows=47400 width=26) |
| -> Seq Scan on constr_tab (cost=0.00..271.00 rows=23700 width=26) |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| DROP TABLE IF EXISTS constr_tab; |
| CREATE TABLE constr_tab ( a int NOT NULL, b int NOT NULL, c int NOT NULL, d int NOT NULL) DISTRIBUTED BY (a,b); |
| INSERT INTO constr_tab VALUES(1,5,3,4); |
| INSERT INTO constr_tab VALUES(1,5,3,4); |
| set optimizer_enable_dml_constraints=off; |
| explain update constr_tab set b = 10; |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------------------- |
| Update on constr_tab (cost=0.00..1219.00 rows=0 width=0) |
| -> Explicit Redistribute Motion 3:3 (slice1; segments: 3) (cost=0.00..1219.00 rows=47400 width=26) |
| -> Split Update (cost=0.00..271.00 rows=47400 width=26) |
| -> Seq Scan on constr_tab (cost=0.00..271.00 rows=23700 width=26) |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| DROP TABLE IF EXISTS constr_tab; |
| CREATE TABLE constr_tab ( a int, b int, c int, d int) DISTRIBUTED BY (a); |
| INSERT INTO constr_tab VALUES(1,5,3,4); |
| INSERT INTO constr_tab VALUES(1,5,3,4); |
| set optimizer_enable_dml_constraints=off; |
| explain update constr_tab set a = 10; |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------------------- |
| Update on constr_tab (cost=0.00..1219.00 rows=0 width=0) |
| -> Explicit Redistribute Motion 3:3 (slice1; segments: 3) (cost=0.00..1219.00 rows=47400 width=26) |
| -> Split Update (cost=0.00..271.00 rows=47400 width=26) |
| -> Seq Scan on constr_tab (cost=0.00..271.00 rows=23700 width=26) |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| -- Test ORCA fallback on "FROM ONLY" |
| CREATE TABLE homer (a int, b int, c int) |
| DISTRIBUTED BY (a) |
| PARTITION BY range(b) |
| SUBPARTITION BY range(c) |
| SUBPARTITION TEMPLATE ( |
| START(40) END(46) EVERY(3) |
| ) |
| (START(0) END(4) EVERY(2)); |
| INSERT INTO homer VALUES (1,0,40),(2,1,43),(3,2,41),(4,3,44); |
| SELECT * FROM ONLY homer; |
| a | b | c |
| ---+---+--- |
| (0 rows) |
| |
| SELECT * FROM ONLY homer_1_prt_1; |
| a | b | c |
| ---+---+--- |
| (0 rows) |
| |
| UPDATE ONLY homer SET c = c + 1; |
| SELECT * FROM homer; |
| a | b | c |
| ---+---+---- |
| 1 | 0 | 40 |
| 2 | 1 | 43 |
| 3 | 2 | 41 |
| 4 | 3 | 44 |
| (4 rows) |
| |
| DELETE FROM ONLY homer WHERE a = 3; |
| SELECT * FROM homer; |
| a | b | c |
| ---+---+---- |
| 1 | 0 | 40 |
| 2 | 1 | 43 |
| 3 | 2 | 41 |
| 4 | 3 | 44 |
| (4 rows) |
| |
| -- ORCA should not fallback just because external tables are in FROM clause |
| -- start_ignore |
| CREATE TABLE heap_t1 (a int, b int) DISTRIBUTED BY (b); |
| CREATE EXTERNAL TABLE ext_table_no_fallback (a int, b int) LOCATION ('gpfdist://myhost:8080/test.csv') FORMAT 'CSV'; |
| -- end_ignore |
| EXPLAIN SELECT * FROM ext_table_no_fallback; |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..31000.00 rows=1000000 width=8) |
| -> Foreign Scan on ext_table_no_fallback (cost=0.00..11000.00 rows=333334 width=8) |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| |
| EXPLAIN SELECT * FROM ONLY ext_table_no_fallback; |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..31000.00 rows=1000000 width=8) |
| -> Foreign Scan on ext_table_no_fallback (cost=0.00..11000.00 rows=333334 width=8) |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| |
| EXPLAIN INSERT INTO heap_t1 SELECT * FROM ONLY ext_table_no_fallback; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------- |
| Insert on heap_t1 (cost=0.00..31000.00 rows=333334 width=8) |
| -> Redistribute Motion 3:3 (slice1; segments: 3) (cost=0.00..31000.00 rows=333334 width=8) |
| Hash Key: ext_table_no_fallback.b |
| -> Foreign Scan on ext_table_no_fallback (cost=0.00..11000.00 rows=333334 width=8) |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| set optimizer_enable_dml=off; |
| EXPLAIN INSERT INTO homer VALUES (1,0,40),(2,1,43),(3,2,41),(4,3,44); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------- |
| Insert on homer (cost=0.00..0.05 rows=2 width=12) |
| -> Redistribute Motion 1:3 (slice1; segments: 1) (cost=0.00..0.05 rows=4 width=12) |
| Hash Key: "*VALUES*".column1 |
| -> Values Scan on "*VALUES*" (cost=0.00..0.05 rows=2 width=12) |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| EXPLAIN UPDATE ONLY homer SET c = c + 1; |
| QUERY PLAN |
| --------------------------------------------------- |
| Update on homer (cost=0.00..0.00 rows=0 width=0) |
| -> Result (cost=0.00..0.00 rows=0 width=22) |
| One-Time Filter: false |
| Optimizer: Postgres query optimizer |
| (4 rows) |
| |
| EXPLAIN DELETE FROM ONLY homer WHERE a = 3; |
| QUERY PLAN |
| --------------------------------------------------- |
| Delete on homer (cost=0.00..0.00 rows=0 width=0) |
| -> Result (cost=0.00..0.00 rows=0 width=0) |
| One-Time Filter: false |
| Optimizer: Postgres query optimizer |
| (4 rows) |
| |
| set optimizer_enable_dml=on; |
| create table foo(a int, b int); |
| 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 foo select i%100, i%100 from generate_series(1,10000)i; |
| analyze foo; |
| set optimizer_enable_hashagg = on; |
| set optimizer_enable_groupagg = on; |
| explain select count(*) from foo group by a; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=163.00..164.00 rows=100 width=12) |
| -> HashAggregate (cost=163.00..164.00 rows=34 width=12) |
| Group Key: a |
| -> Seq Scan on foo (cost=0.00..113.00 rows=3334 width=4) |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| set optimizer_enable_hashagg = off; |
| set optimizer_enable_groupagg = on; |
| explain select count(*) from foo group by a; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=163.00..164.00 rows=100 width=12) |
| -> HashAggregate (cost=163.00..164.00 rows=34 width=12) |
| Group Key: a |
| -> Seq Scan on foo (cost=0.00..113.00 rows=3334 width=4) |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| set optimizer_enable_hashagg = off; |
| set optimizer_enable_groupagg = off; |
| explain select count(*) from foo group by a; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=163.00..164.00 rows=100 width=12) |
| -> HashAggregate (cost=163.00..164.00 rows=34 width=12) |
| Group Key: a |
| -> Seq Scan on foo (cost=0.00..113.00 rows=3334 width=4) |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| -- Orca should fallback for RTE_TABLEFUNC RTE type |
| explain SELECT * FROM xmltable('/root' passing '' COLUMNS element text); |
| QUERY PLAN |
| ------------------------------------------------------------------------ |
| Table Function Scan on "xmltable" (cost=0.00..1.00 rows=100 width=32) |
| Optimizer: Postgres query optimizer |
| (2 rows) |
| |
| create table ext_part(a int) partition by list(a); |
| 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. |
| create table p1(a int); |
| 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. |
| create external web table p2_ext (like p1) EXECUTE 'cat something.txt' FORMAT 'TEXT'; |
| alter table ext_part attach partition p1 for values in (1); |
| alter table ext_part attach partition p2_ext for values in (2); |
| NOTICE: partition constraints are not validated when attaching a readable external table |
| explain insert into ext_part values (1); |
| QUERY PLAN |
| ------------------------------------------------------ |
| Insert on ext_part (cost=0.00..0.03 rows=1 width=4) |
| -> Result (cost=0.00..0.01 rows=1 width=4) |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| |
| explain delete from ext_part where a=1; |
| QUERY PLAN |
| ----------------------------------------------------------------------- |
| Delete on ext_part (cost=0.00..435.25 rows=0 width=0) |
| Delete on p1 ext_part_1 |
| -> Seq Scan on p1 ext_part_1 (cost=0.00..435.25 rows=32 width=14) |
| Filter: (a = 1) |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| explain update ext_part set a=1; |
| ERROR: cannot update foreign table "p2_ext" |
| set optimizer_enable_orderedagg=off; |
| select array_agg(a order by b) |
| from (values (1,4),(2,3),(3,1),(4,2)) v(a,b); |
| array_agg |
| ----------- |
| {3,4,2,1} |
| (1 row) |
| |
| -- Orca should fallback if a function in 'from' clause uses 'WITH ORDINALITY' |
| SELECT * FROM jsonb_array_elements('["b", "a"]'::jsonb) WITH ORDINALITY; |
| value | ordinality |
| -------+------------ |
| "b" | 1 |
| "a" | 2 |
| (2 rows) |
| |
| -- start_ignore |
| -- FIXME: gpcheckcat fails due to mismatching distribution policy if this table isn't dropped |
| -- Keep this table around once this is fixed |
| reset optimizer_enable_orderedagg; |
| drop table ext_part; |
| -- end_ignore |
