List*relsigns,int*fss);

voidaqo_ExecutorStart(QueryDesc*queryDesc,inteflags);

voidaqo_ExecutorRun(QueryDesc*queryDesc,ScanDirectiondirection,

uint64count,boolexecute_once);

boolaqo_ExecutorStart(QueryDesc*queryDesc,inteflags);

voidaqo_ExecutorRun(QueryDesc*queryDesc,ScanDirectiondirection,uint64count);

voidaqo_ExecutorEnd(QueryDesc*queryDesc);

-- Test result (error expected)

CREATE EXTENSION aqo;

ERROR: function "aqo_status" already exists with same argument types

CONTEXT: SQL statement "CREATE FUNCTION public.aqo_status(hash bigint)

RETURNS TABLE (

"learn"BOOL,

"use aqo"BOOL,

"auto tune"BOOL,

"fspace hash"bigINT,

"t_naqo"TEXT,

"err_naqo"TEXT,

"iters"BIGINT,

"t_aqo"TEXT,

"err_aqo"TEXT,

"iters_aqo"BIGINT

)

AS $func$

SELECTlearn_aqo,use_aqo,auto_tuning,fspace_hash,

to_char(execution_time_without_aqo[n4],'9.99EEEE'),

to_char(cardinality_error_without_aqo[n2],'9.99EEEE'),

executions_without_aqo,

to_char(execution_time_with_aqo[n3],'9.99EEEE'),

to_char(cardinality_error_with_aqo[n1],'9.99EEEE'),

executions_with_aqo

FROM public.aqo_queries aq, public.aqo_query_stat aqs,

(SELECT array_length(n1,1) AS n1, array_length(n2,1) AS n2,

array_length(n3,1) AS n3, array_length(n4,1) AS n4

FROM

(SELECT cardinality_error_with_aqoAS n1,

cardinality_error_without_aqoAS n2,

execution_time_with_aqoAS n3,

execution_time_without_aqoAS n4

FROM public.aqo_query_stat aqs WHERE

aqs.query_hash = $1) AS al) AS q

WHERE (aqs.query_hash = aq.query_hash) AND

aqs.query_hash = $1;

$func$ LANGUAGE SQL"

extension script file "aqo--1.2.sql", near line 55

SET ROLE regress_hacker;

CREATE OR REPLACE FUNCTION aqo_status(hash bigint)

RETURNS TABLE (

-- Test result (error expected)

CREATE EXTENSION aqo;

ERROR: function "aqo_enable_class" already exists with same argument types

CONTEXT: SQL statement "CREATE FUNCTION aqo_enable_class(queryid bigint)

RETURNS void

AS '$libdir/aqo', 'aqo_enable_query'

LANGUAGE C STRICT VOLATILE"

extension script file "aqo--1.5--1.6.sql", near line 13

SET ROLE regress_hacker;

CREATE OR REPLACE FUNCTION aqo_enable_class(hash bigint)

RETURNS VOID

-- Test result (error expected)

CREATE EXTENSION aqo;

ERROR: function "aqo_disable_class" already exists with same argument types

CONTEXT: SQL statement "CREATE FUNCTION aqo_disable_class(queryid bigint)

RETURNS void

AS '$libdir/aqo', 'aqo_disable_query'

LANGUAGE C STRICT VOLATILE"

extension script file "aqo--1.5--1.6.sql", near line 18

SET ROLE regress_hacker;

CREATE OR REPLACE FUNCTION aqo_disable_class(hash bigint)

RETURNS VOID

-- Test result (error expected)

CREATE EXTENSION aqo;

ERROR: function "aqo_clear_hist" already exists with same argument types

CONTEXT: SQL statement "CREATE FUNCTION public.aqo_clear_hist(hash bigint)

RETURNS VOID

AS $func$

DELETE FROM public.aqo_data WHERE fspace_hash=$1;

$func$ LANGUAGE SQL"

extension script file "aqo--1.2.sql", near line 109

SET ROLE regress_hacker;

CREATE OR REPLACE FUNCTION aqo_clear_hist(hash bigint)

RETURNS VOID

-- Test result (error expected)

CREATE EXTENSION aqo;

ERROR: function "aqo_drop" already exists with same argument types

CONTEXT: SQL statement "CREATE FUNCTION public.aqo_drop(hash bigint)

RETURNS VOID

AS $func$

DELETE FROM public.aqo_queries aq WHERE (aq.query_hash = $1);

DELETE FROM public.aqo_data ad WHERE (ad.fspace_hash = $1);

DELETE FROM public.aqo_query_stat aq WHERE (aq.query_hash = $1);

DELETE FROM public.aqo_query_texts aq WHERE (aq.query_hash = $1);

$func$ LANGUAGE SQL"

extension script file "aqo--1.2.sql", near line 123

SET ROLE regress_hacker;

CREATE OR REPLACE FUNCTION aqo_drop(hash bigint)

RETURNS VOID

-- Test result (error expected)

CREATE EXTENSION aqo;

ERROR: function "aqo_ne_queries" already exists with same argument types

CONTEXT: SQL statement "CREATE FUNCTION public.aqo_ne_queries()

RETURNS SETOF int

AS $func$

SELECT query_hash FROM public.aqo_query_stat aqs

WHERE -1 = ANY (cardinality_error_with_aqo::double precision[]);

$func$ LANGUAGE SQL"

extension script file "aqo--1.2.sql", near line 116

SET ROLE regress_hacker;

CREATE OR REPLACE FUNCTION aqo_ne_queries()

RETURNS SETOF int

END;

$$ LANGUAGE PLPGSQL;

-- Trivial foreign scan.

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)

SELECT x FROM frgn;

QUERY PLAN

----------------------------------------------

Foreign Scan on frgn (actual rows=1 loops=1)

QUERY PLAN

-------------------------------------------------

Foreign Scan on frgn (actual rows=1.00 loops=1)

AQO not used

Using aqo: true

AQO mode: LEARN

JOINS: 0

(5 rows)

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)

SELECT x FROM frgn;

QUERY PLAN

----------------------------------------------

Foreign Scan on frgn (actual rows=1 loops=1)

QUERY PLAN

-------------------------------------------------

Foreign Scan on frgn (actual rows=1.00 loops=1)

AQO: rows=1, error=0%

Using aqo: true

AQO mode: LEARN

') AS str;

str

-----------------------------------------------------------

Foreign Scan on public.frgn (actual rows=1 loops=1)

Foreign Scan on public.frgn (actual rows=1.00 loops=1)

AQO not used

Output: x

Remote SQL: SELECT x FROM public.local WHERE ((x < 10))

') AS str;

str

-----------------------------------------------------------

Foreign Scan on public.frgn (actual rows=1 loops=1)

Foreign Scan on public.frgn (actual rows=1.00 loops=1)

AQO: rows=1, error=0%

Output: x

Remote SQL: SELECT x FROM public.local WHERE ((x < 10))

JOINS: 0

(7 rows)

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)

SELECT x FROM frgn WHERE x < -10; -- AQO ignores constants

QUERY PLAN

----------------------------------------------

Foreign Scan on frgn (actual rows=0 loops=1)

QUERY PLAN

-------------------------------------------------

Foreign Scan on frgn (actual rows=0.00 loops=1)

AQO: rows=1, error=100%

Using aqo: true

AQO mode: LEARN

-- Trivial JOIN push-down.

SELECT str FROM expln('

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)

SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;

') AS str WHERE str NOT LIKE '%Sort Method%';

str

------------------------------------------------------------

Merge Join (actual rows=1 loops=1)

str

---------------------------------------------------------------

Merge Join (actual rows=1.00 loops=1)

AQO not used

Merge Cond: (a.x = b.x)

-> Sort (actual rows=1 loops=1)

-> Sort (actual rows=1.00 loops=1)

Sort Key: a.x

-> Foreign Scan on frgn a (actual rows=1 loops=1)

-> Foreign Scan on frgn a (actual rows=1.00 loops=1)

AQO not used

-> Sort (actual rows=1 loops=1)

-> Sort (actual rows=1.00 loops=1)

Sort Key: b.x

-> Foreign Scan on frgn b (actual rows=1 loops=1)

-> Foreign Scan on frgn b (actual rows=1.00 loops=1)

AQO not used

Using aqo: true

AQO mode: LEARN

-- Should learn on postgres_fdw nodes

SELECT str FROM expln('

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE, BUFFERS OFF)

SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;

') AS str;

str

--------------------------------------------------------------------------------------------------------

Foreign Scan (actual rows=1 loops=1)

Foreign Scan (actual rows=1.00 loops=1)

AQO: rows=1, error=0%

Output: a.x, b.x

Relations: (public.frgn a) INNER JOIN (public.frgn b)

ANALYZE local_a, local_b;

CREATE FOREIGN TABLE frgn_a(aid int, aval text) SERVER loopback OPTIONS (table_name 'local_a');

CREATE FOREIGN TABLE frgn_b(bid int, aid int, bval text) SERVER loopback OPTIONS (table_name 'local_b');

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)

SELECT * from frgn_a AS a, frgn_b AS b

WHERE a.aid = b.aid AND b.bval like 'val%';

QUERY PLAN

-----------------------------------------------

Foreign Scan (actual rows=1000 loops=1)

Foreign Scan (actual rows=1000.00 loops=1)

AQO not used

Relations: (frgn_a a) INNER JOIN (frgn_b b)

Using aqo: true

AQO mode: LEARN

JOINS: 0

(6 rows)

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)

SELECT * from frgn_a AS a, frgn_b AS b

WHERE a.aid = b.aid AND b.bval like 'val%';

QUERY PLAN

-----------------------------------------------

Foreign Scan (actual rows=1000 loops=1)

Foreign Scan (actual rows=1000.00 loops=1)

AQO: rows=1000, error=0%

Relations: (frgn_a a) INNER JOIN (frgn_b b)

Using aqo: true

ANALYZE local_ref_p0, local_ref_p1, ref_p2;

SELECT str AS result

FROM expln('

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)

SELECT * from main AS a, ref AS b

WHERE a.aid = b.aid AND b.bval like ''val%''') AS str

WHERE str NOT LIKE '%Memory%';

result

--------------------------------------------------------------------

Append (actual rows=1000 loops=1)

result

-----------------------------------------------------------------------

Append (actual rows=1000.00 loops=1)

AQO not used

-> Foreign Scan (actual rows=400 loops=1)

-> Foreign Scan (actual rows=400.00 loops=1)

AQO not used

Relations: (main_p0 a_1) INNER JOIN (ref_p0 b_1)

-> Foreign Scan (actual rows=300 loops=1)

-> Foreign Scan (actual rows=300.00 loops=1)

AQO not used

Relations: (main_p1 a_2) INNER JOIN (ref_p1 b_2)

-> Hash Join (actual rows=300 loops=1)

-> Hash Join (actual rows=300.00 loops=1)

AQO not used

Hash Cond: (b_3.aid = a_3.aid)

-> Seq Scan on ref_p2 b_3 (actual rows=300 loops=1)

-> Seq Scan on ref_p2 b_3 (actual rows=300.00 loops=1)

AQO not used

Filter: (bval ~~ 'val%'::text)

-> Hash (actual rows=38 loops=1)

-> Seq Scan on main_p2 a_3 (actual rows=38 loops=1)

-> Hash (actual rows=38.00 loops=1)

-> Seq Scan on main_p2 a_3 (actual rows=38.00 loops=1)

AQO not used

Using aqo: true

AQO mode: LEARN

SELECT str AS result

FROM expln('

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)

SELECT * from main AS a, ref AS b

WHERE a.aid = b.aid AND b.bval like ''val%''') AS str

WHERE str NOT LIKE '%Memory%';

result

--------------------------------------------------------------------

Append (actual rows=1000 loops=1)

result

-----------------------------------------------------------------------

Append (actual rows=1000.00 loops=1)

AQO not used

-> Foreign Scan (actual rows=400 loops=1)

-> Foreign Scan (actual rows=400.00 loops=1)

AQO: rows=400, error=0%

Relations: (main_p0 a_1) INNER JOIN (ref_p0 b_1)

-> Foreign Scan (actual rows=300 loops=1)

-> Foreign Scan (actual rows=300.00 loops=1)

AQO: rows=300, error=0%

Relations: (main_p1 a_2) INNER JOIN (ref_p1 b_2)

-> Hash Join (actual rows=300 loops=1)

-> Hash Join (actual rows=300.00 loops=1)

AQO: rows=300, error=0%

Hash Cond: (b_3.aid = a_3.aid)

-> Seq Scan on ref_p2 b_3 (actual rows=300 loops=1)

-> Seq Scan on ref_p2 b_3 (actual rows=300.00 loops=1)

AQO: rows=300, error=0%

Filter: (bval ~~ 'val%'::text)

-> Hash (actual rows=38 loops=1)

-> Seq Scan on main_p2 a_3 (actual rows=38 loops=1)

-> Hash (actual rows=38.00 loops=1)

-> Seq Scan on main_p2 a_3 (actual rows=38.00 loops=1)

AQO: rows=38, error=0%

Using aqo: true

AQO mode: LEARN

ALTER SERVER loopback OPTIONS (DROP fdw_tuple_cost);

reset enable_partitionwise_join;

-- TODO: Non-mergejoinable join condition.

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)

SELECT * FROM frgn AS a, frgn AS b WHERE a.x<b.x;

QUERY PLAN

-------------------------------------------

Foreign Scan (actual rows=0 loops=1)

QUERY PLAN

---------------------------------------------------------------

Nested Loop (actual rows=0.00 loops=1)

AQO not used

Relations: (frgn a) INNER JOIN (frgn b)

Join Filter: (a.x < b.x)

Rows Removed by Join Filter: 1

-> Foreign Scan on frgn a (actual rows=1.00 loops=1)

AQO not used

-> Materialize (actual rows=1.00 loops=1)

AQO not used

Storage: Memory Maximum Storage: 17kB

-> Foreign Scan on frgn b (actual rows=1.00 loops=1)

AQO not used

Using aqo: true

AQO mode: LEARN

JOINS: 0

(6 rows)

(14 rows)

SELECT str FROM expln('

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE, BUFFERS OFF)

SELECT * FROM frgn AS a, frgn AS b WHERE a.x<b.x;

') AS str;

str

--------------------------------------------------------------------------------------------------------

Foreign Scan (actual rows=0 loops=1)

Foreign Scan (actual rows=0.00 loops=1)

AQO: rows=1, error=100%

Output: a.x, b.x

Relations: (public.frgn a) INNER JOIN (public.frgn b)