1-- =================================================================== 2-- test recursive planning functionality with different executors 3-- =================================================================== 4CREATE SCHEMA subquery_executor; 5SET search_path TO subquery_executor, public; 6CREATE TABLE users_table_local AS SELECT * FROM users_table; 7SET client_min_messages TO DEBUG1; 8-- subquery with router planner 9SELECT 10 count(*) 11FROM 12( 13 SELECT value_2 FROM users_table WHERE user_id = 15 OFFSET 0 14) as foo, 15( 16 SELECT user_id FROM users_table 17) as bar 18WHERE foo.value_2 = bar.user_id; 19DEBUG: generating subplan XXX_1 for subquery SELECT value_2 FROM public.users_table WHERE (user_id OPERATOR(pg_catalog.=) 15) OFFSET 0 20DEBUG: Plan XXX query after replacing subqueries and CTEs: SELECT count(*) AS count FROM (SELECT intermediate_result.value_2 FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(value_2 integer)) foo, (SELECT users_table.user_id FROM public.users_table) bar WHERE (foo.value_2 OPERATOR(pg_catalog.=) bar.user_id) 21 count 22--------------------------------------------------------------------- 23 0 24(1 row) 25 26-- subquery with router but not logical plannable 27-- bar is recursively planned 28SELECT 29 count(*) 30FROM 31( 32 SELECT user_id, sum(value_2) over (partition by user_id) AS counter FROM users_table WHERE user_id = 15 33) as foo, 34( 35 SELECT user_id FROM users_table 36) as bar 37WHERE foo.counter = bar.user_id; 38DEBUG: generating subplan XXX_1 for subquery SELECT user_id FROM public.users_table 39DEBUG: Plan XXX query after replacing subqueries and CTEs: SELECT count(*) AS count FROM (SELECT users_table.user_id, sum(users_table.value_2) OVER (PARTITION BY users_table.user_id) AS counter FROM public.users_table WHERE (users_table.user_id OPERATOR(pg_catalog.=) 15)) foo, (SELECT intermediate_result.user_id FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(user_id integer)) bar WHERE (foo.counter OPERATOR(pg_catalog.=) bar.user_id) 40 count 41--------------------------------------------------------------------- 42 0 43(1 row) 44 45-- subquery with real-time query 46SELECT 47 count(*) 48FROM 49( 50 SELECT value_2 FROM users_table WHERE user_id != 15 OFFSET 0 51) as foo, 52( 53 SELECT user_id FROM users_table 54) as bar 55WHERE foo.value_2 = bar.user_id; 56DEBUG: generating subplan XXX_1 for subquery SELECT value_2 FROM public.users_table WHERE (user_id OPERATOR(pg_catalog.<>) 15) OFFSET 0 57DEBUG: Plan XXX query after replacing subqueries and CTEs: SELECT count(*) AS count FROM (SELECT intermediate_result.value_2 FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(value_2 integer)) foo, (SELECT users_table.user_id FROM public.users_table) bar WHERE (foo.value_2 OPERATOR(pg_catalog.=) bar.user_id) 58 count 59--------------------------------------------------------------------- 60 1612 61(1 row) 62 63-- subquery with repartition query 64SET citus.enable_repartition_joins to ON; 65SELECT 66 count(*) 67FROM 68( 69 SELECT DISTINCT users_table.value_2 FROM users_table, events_table WHERE users_table.user_id = events_table.value_2 AND users_table.user_id < 2 70) as foo, 71( 72 SELECT user_id FROM users_table 73) as bar 74WHERE foo.value_2 = bar.user_id; 75DEBUG: generating subplan XXX_1 for subquery SELECT DISTINCT users_table.value_2 FROM public.users_table, public.events_table WHERE ((users_table.user_id OPERATOR(pg_catalog.=) events_table.value_2) AND (users_table.user_id OPERATOR(pg_catalog.<) 2)) 76DEBUG: Plan XXX query after replacing subqueries and CTEs: SELECT count(*) AS count FROM (SELECT intermediate_result.value_2 FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(value_2 integer)) foo, (SELECT users_table.user_id FROM public.users_table) bar WHERE (foo.value_2 OPERATOR(pg_catalog.=) bar.user_id) 77 count 78--------------------------------------------------------------------- 79 58 80(1 row) 81 82-- mixed of all executors (including local execution) 83SELECT 84 count(*) 85FROM 86( 87 SELECT value_2 FROM users_table WHERE user_id = 15 OFFSET 0 88) as foo, 89( 90 SELECT user_id FROM users_table OFFSET 0 91) as bar, 92( 93 SELECT DISTINCT users_table.value_2 FROM users_table, events_table WHERE users_table.user_id = events_table.value_2 AND users_table.user_id < 2 94) baz, 95( 96 SELECT user_id FROM users_table_local WHERE user_id = 2 97) baw 98WHERE foo.value_2 = bar.user_id AND baz.value_2 = bar.user_id AND bar.user_id = baw.user_id; 99DEBUG: generating subplan XXX_1 for subquery SELECT value_2 FROM public.users_table WHERE (user_id OPERATOR(pg_catalog.=) 15) OFFSET 0 100DEBUG: generating subplan XXX_2 for subquery SELECT user_id FROM public.users_table OFFSET 0 101DEBUG: generating subplan XXX_3 for subquery SELECT DISTINCT users_table.value_2 FROM public.users_table, public.events_table WHERE ((users_table.user_id OPERATOR(pg_catalog.=) events_table.value_2) AND (users_table.user_id OPERATOR(pg_catalog.<) 2)) 102DEBUG: generating subplan XXX_4 for subquery SELECT user_id FROM subquery_executor.users_table_local WHERE (user_id OPERATOR(pg_catalog.=) 2) 103DEBUG: Plan XXX query after replacing subqueries and CTEs: SELECT count(*) AS count FROM (SELECT intermediate_result.value_2 FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(value_2 integer)) foo, (SELECT intermediate_result.user_id FROM read_intermediate_result('XXX_2'::text, 'binary'::citus_copy_format) intermediate_result(user_id integer)) bar, (SELECT intermediate_result.value_2 FROM read_intermediate_result('XXX_3'::text, 'binary'::citus_copy_format) intermediate_result(value_2 integer)) baz, (SELECT intermediate_result.user_id FROM read_intermediate_result('XXX_4'::text, 'binary'::citus_copy_format) intermediate_result(user_id integer)) baw WHERE ((foo.value_2 OPERATOR(pg_catalog.=) bar.user_id) AND (baz.value_2 OPERATOR(pg_catalog.=) bar.user_id) AND (bar.user_id OPERATOR(pg_catalog.=) baw.user_id)) 104 count 105--------------------------------------------------------------------- 106 0 107(1 row) 108 109SET citus.enable_repartition_joins to OFF; 110-- final query is router 111SELECT 112 count(*) 113FROM 114( 115 SELECT value_2 FROM users_table WHERE user_id = 1 OFFSET 0 116) as foo, 117( 118 SELECT user_id FROM users_table WHERE user_id = 2 OFFSET 0 119) as bar 120WHERE foo.value_2 = bar.user_id; 121DEBUG: generating subplan XXX_1 for subquery SELECT value_2 FROM public.users_table WHERE (user_id OPERATOR(pg_catalog.=) 1) OFFSET 0 122DEBUG: generating subplan XXX_2 for subquery SELECT user_id FROM public.users_table WHERE (user_id OPERATOR(pg_catalog.=) 2) OFFSET 0 123DEBUG: Plan XXX query after replacing subqueries and CTEs: SELECT count(*) AS count FROM (SELECT intermediate_result.value_2 FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(value_2 integer)) foo, (SELECT intermediate_result.user_id FROM read_intermediate_result('XXX_2'::text, 'binary'::citus_copy_format) intermediate_result(user_id integer)) bar WHERE (foo.value_2 OPERATOR(pg_catalog.=) bar.user_id) 124 count 125--------------------------------------------------------------------- 126 18 127(1 row) 128 129-- final query is real-time 130SELECT 131 count(*) 132FROM 133( 134 SELECT value_2 FROM users_table WHERE user_id = 1 OFFSET 0 135) as foo, 136( 137 SELECT user_id FROM users_table WHERE user_id != 2 138) as bar 139WHERE foo.value_2 = bar.user_id; 140DEBUG: generating subplan XXX_1 for subquery SELECT value_2 FROM public.users_table WHERE (user_id OPERATOR(pg_catalog.=) 1) OFFSET 0 141DEBUG: Plan XXX query after replacing subqueries and CTEs: SELECT count(*) AS count FROM (SELECT intermediate_result.value_2 FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(value_2 integer)) foo, (SELECT users_table.user_id FROM public.users_table WHERE (users_table.user_id OPERATOR(pg_catalog.<>) 2)) bar WHERE (foo.value_2 OPERATOR(pg_catalog.=) bar.user_id) 142 count 143--------------------------------------------------------------------- 144 103 145(1 row) 146 147SET client_min_messages TO DEFAULT; 148DROP SCHEMA subquery_executor CASCADE; 149NOTICE: drop cascades to table users_table_local 150SET search_path TO public; 151