1-- test geqo 2CREATE SCHEMA geqo_schema; 3SET search_path TO geqo_schema; 4CREATE TABLE dist (a int, b int); 5SELECT create_distributed_table('dist', 'a'); 6 create_distributed_table 7--------------------------------------------------------------------- 8 9(1 row) 10 11INSERT INTO dist VALUES (1, 1), (2, 2), (3, 3); 12CREATE TABLE dist2 (a int, b int); 13SELECT create_distributed_table('dist2', 'a'); 14 create_distributed_table 15--------------------------------------------------------------------- 16 17(1 row) 18 19INSERT INTO dist2 VALUES (1, 1), (2, 2), (3, 3); 20SET geqo_threshold TO 2; 21SET geqo_pool_size TO 1000; 22SET geqo_generations TO 1000; 23SET citus.enable_repartition_joins to ON; 24SELECT count(*) FROM dist d1 LEFT JOIN dist2 d2 ON d1.a = d2.a LEFT JOIN dist d3 ON d3.a = d2.a; 25 count 26--------------------------------------------------------------------- 27 3 28(1 row) 29 30-- JOINs with CTEs: 31WITH cte_1 AS (SELECT * FROM dist OFFSET 0) 32 SELECT count(*) FROM dist d1 LEFT JOIN dist2 d2 ON d1.a = d2.a LEFT JOIN dist d3 ON d3.a = d2.a LEFT JOIN cte_1 ON true; 33 count 34--------------------------------------------------------------------- 35 9 36(1 row) 37 38WITH cte_1 AS (SELECT * FROM dist OFFSET 0), 39 cte_2 AS (SELECT * FROM dist2 OFFSET 0), 40 cte_3 AS (SELECT * FROM dist OFFSET 0) 41 SELECT count(*) FROM cte_1 d1 LEFT JOIN cte_2 d2 ON d1.a = d2.a LEFT JOIN cte_3 d3 ON d3.a = d2.a LEFT JOIN cte_1 ON true; 42 count 43--------------------------------------------------------------------- 44 9 45(1 row) 46 47-- Inner JOIN: 48SELECT count(*) FROM dist d1 JOIN dist2 d2 ON d1.a = d2.a JOIN dist d3 ON d3.a = d2.a; 49 count 50--------------------------------------------------------------------- 51 3 52(1 row) 53 54-- subquery join 55SELECT count(*) FROM (SELECT *, random() FROM dist) as d1 JOIN (SELECT *, random() FROM dist2) d2 ON d1.a = d2.a JOIN (SELECT *, random() FROM dist) as d3 ON d3.a = d2.a; 56 count 57--------------------------------------------------------------------- 58 3 59(1 row) 60 61SELECT count(*) FROM (SELECT *, random() FROM dist) as d1 LEFT JOIN (SELECT *, random() FROM dist2) d2 ON d1.a = d2.a LEFT JOIN (SELECT *, random() FROM dist) as d3 ON d3.a = d2.a; 62 count 63--------------------------------------------------------------------- 64 3 65(1 row) 66 67-- router query 68SELECT count(*) FROM dist d1 LEFT JOIN dist2 d2 ON d1.a = d2.a LEFT JOIN dist d3 ON d3.a = d2.a WHERE d1.a = 1 AND d2.a = 1 AND d3.a = 1; 69 count 70--------------------------------------------------------------------- 71 1 72(1 row) 73 74-- fast path router query 75SELECT count(*) FROM dist WHERE a = 1; 76 count 77--------------------------------------------------------------------- 78 1 79(1 row) 80 81-- simple INSERT 82INSERT INTO dist (a) VALUES (1); 83-- repartition join, probably not relevant, but still be defensive 84SELECT count(*) FROM dist d1 JOIN dist2 d2 ON d1.b = d2.b JOIN dist d3 ON d3.b = d2.b; 85 count 86--------------------------------------------------------------------- 87 3 88(1 row) 89 90-- update query with join 91UPDATE dist SET b = foo.a FROM (SELECT d1.a FROM dist d1 JOIN dist2 d2 USING(a)) foo WHERE foo.a = dist.a RETURNING *; 92 a | b | a 93--------------------------------------------------------------------- 94 1 | 1 | 1 95 1 | 1 | 1 96 2 | 2 | 2 97 3 | 3 | 3 98(4 rows) 99 100-- insert select via repartitioning 101INSERT INTO dist (a) SELECT max(d1.b) FROM dist d1 JOIN dist2 d2 ON d1.a = d2.a JOIN dist d3 ON d3.a = d2.a GROUP BY d1.a; 102SELECT count(*) FROM dist; 103 count 104--------------------------------------------------------------------- 105 7 106(1 row) 107 108-- insert select pushdown 109INSERT INTO dist SELECT d1.* FROM dist d1 JOIN dist2 d2 ON d1.a = d2.a JOIN dist d3 ON d3.a = d2.a WHERE d1.b < 2 AND d2.b < 2; 110SELECT count(*) FROM dist; 111 count 112--------------------------------------------------------------------- 113 13 114(1 row) 115 116-- insert select via coordinator 117INSERT INTO dist SELECT d1.* FROM dist d1 JOIN dist2 d2 ON d1.a = d2.a JOIN dist d3 ON d3.a = d2.a WHERE d1.b < 2 AND d2.b < 2 OFFSET 0; 118SELECT count(*) FROM dist; 119 count 120--------------------------------------------------------------------- 121 85 122(1 row) 123 124DROP SCHEMA geqo_schema CASCADE; 125NOTICE: drop cascades to 2 other objects 126DETAIL: drop cascades to table dist 127drop cascades to table dist2 128