autovacuum = off

shared_preload_libraries = 'postgres_fdw, aqo'

max_parallel_maintenance_workers = 1 # switch off parallel workers because of unsteadiness

aqo.wide_search = 'on'

aqo.wide_search = 'on'

compute_query_id = 'regress'

SELECT str FROM expln('

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

SELECT x FROM frgn WHERE x < 10;

') AS str WHERE str NOT LIKE '%Query Identifier%';

') AS str;

str

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

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

SELECT str FROM expln('

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

SELECT x FROM frgn WHERE x < 10;

') AS str WHERE str NOT LIKE '%Query Identifier%';

') AS str;

str

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

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

SELECT str FROM expln('

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

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

') AS str WHERE str NOT LIKE '%Query Identifier%';

') AS str;

str

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

Foreign Scan (actual rows=1 loops=1)

SELECT str FROM expln('

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

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

') AS str WHERE str NOT LIKE '%Query Identifier%';

') AS str;

str

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

Foreign Scan (actual rows=0 loops=1)

SET aqo.join_threshold = 0;

SET aqo.mode = 'learn';

SET aqo.show_details = true;

SET compute_query_id = 'auto';

CREATE TABLE t(x int);

INSERT INTO t (x) (SELECT * FROM generate_series(1, 100) AS gs);

ANALYZE t;

(1 row)

-- Check AQO addons to explain (the only stable data)

SELECT str FROM expln('

SELECT regexp_replace(

str,'Query Identifier: -?\m\d+\M','Query Identifier: N','g') as str FROM expln('

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

SELECT x FROM t;

') AS str WHERE str NOT LIKE '%Query Identifier%';

') AS str;

str

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

Seq Scan on public.t (actual rows=100 loops=1)

AQO not used

Output: x

Query Identifier: N

Using aqo: true

AQO mode: LEARN

JOINS: 0

(6 rows)

(7 rows)

SELECT str FROM expln('

SELECT regexp_replace(

str,'Query Identifier: -?\m\d+\M','Query Identifier: N','g') as str FROM expln('

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

SELECT x FROM t;

') AS str WHERE str NOT LIKE '%Query Identifier%';

') AS str;

str

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

Seq Scan on public.t (actual rows=100 loops=1)

AQO: rows=100, error=0%

Output: x

Query Identifier: N

Using aqo: true

AQO mode: LEARN

JOINS: 0

(6 rows)

(7 rows)

SET aqo.mode = 'disabled';

-- Check existence of the interface functions.

-- in the next queries with the same fss_hash

SELECT str AS result

FROM expln('

SELECT x FROM A where x = 5;') AS str

WHERE str NOT LIKE 'Query Identifier%';

SELECT x FROM A where x = 5;') AS str;

result

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

Seq Scan on public.a (actual rows=100 loops=1)

SELECT str AS result

FROM expln('

SELECT x FROM A,B WHERE x = 5 AND A.x = B.y;') AS str

WHERE str NOT LIKE 'Query Identifier%'

; -- Find cardinality for SCAN A(x=5) from a neighbour class, created by the

result

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

SELECT str AS result

FROM expln('

SELECT x, sum(x) FROM A,B WHERE y = 5 AND A.x = B.y group by(x);') AS str

WHERE str NOT LIKE 'Query Identifier%'

; -- Find the JOIN cardinality from a neighbour class.

result

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

-- cardinality 100 in the first Seq Scan on a

SELECT str AS result

FROM expln('

SELECT x, sum(x) FROM A WHERE x = 5 group by(x);') AS str

WHERE str NOT LIKE 'Query Identifier%';

SELECT x, sum(x) FROM A WHERE x = 5 group by(x);') AS str;

result

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

GroupAggregate (actual rows=1 loops=1)

SELECT str AS result

FROM expln('

SELECT x FROM A where x < 10 group by(x);') AS str

WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%';

WHERE str NOT LIKE '%Memory%';

result

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

HashAggregate (actual rows=10 loops=1)

SELECT str AS result

FROM expln('

SELECT x,y FROM A,B WHERE x < 10 AND A.x = B.y;') AS str

WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%';

WHERE str NOT LIKE '%Memory%';

result

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

Merge Join (actual rows=100000 loops=1)

SELECT str AS result

FROM expln('

SELECT x FROM A,B where x < 10 and y > 10 group by(x);') AS str

WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%';

WHERE str NOT LIKE '%Memory%';

result

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

HashAggregate (actual rows=0 loops=1)

SELECT str AS result

FROM expln('

SELECT x,y FROM A,B WHERE x < 10 and y > 10 AND A.x = B.y;') AS str

WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%'

WHERE str NOT LIKE '%Memory%'

;

result

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

SELECT str FROM expln('

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

SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1') AS str

WHERE str NOT LIKE '%Heap Blocks%' AND str NOT LIKE '%Query Identifier%';

WHERE str NOT LIKE '%Heap Blocks%';

str

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

Aggregate (actual rows=1 loops=1)

SELECT strFROM expln('

')AS strWHERE str NOTLIKE'%Query Identifier%';

')AS str;

SELECT strFROM expln('

')AS strWHERE str NOTLIKE'%Query Identifier%';

')AS str;

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

SELECT xFROM frgnWHERE x<-10;-- AQO ignores constants

SELECT strFROM expln('

')AS strWHERE str NOTLIKE'%Query Identifier%';

')AS str;

CREATETABLElocal_a(aidintprimary key, avaltext);

CREATETABLElocal_b(bidintprimary key, aidintreferences local_a(aid), bvaltext);

SELECT strFROM expln('

')AS strWHERE str NOTLIKE'%Query Identifier%';

')AS str;

DROP EXTENSION aqo CASCADE;

DROP EXTENSION postgres_fdw CASCADE;

SETaqo.join_threshold=0;

SETaqo.mode='learn';

SETaqo.show_details= true;

SET compute_query_id='auto';

CREATETABLEt(xint);

INSERT INTO t (x) (SELECT*FROM generate_series(1,100)AS gs);

ANALYZE t;

SELECT trueFROM aqo_reset();-- Remember! DROP EXTENSION doesn't remove any AQO data gathered.

SELECT strFROM expln('

SELECT regexp_replace(

str,'Query Identifier: -?\m\d+\M','Query Identifier: N','g')as strFROM expln('

')AS strWHERE str NOTLIKE'%Query Identifier%';

SELECT strFROM expln('

')AS str;

SELECT regexp_replace(

str,'Query Identifier: -?\m\d+\M','Query Identifier: N','g')as strFROM expln('

')AS strWHERE str NOTLIKE'%Query Identifier%';

')AS str;

SETaqo.mode='disabled';

SELECT strAS result

FROM expln('

SELECT x FROM A where x = 5;')AS str

WHERE str NOTLIKE'Query Identifier%';

SELECT x FROM A where x = 5;')AS str;

SELECT strAS result

FROM expln('

SELECT x FROM A,B WHERE x = 5 AND A.x = B.y;')AS str

WHERE str NOTLIKE'Query Identifier%'

;-- Find cardinality for SCAN A(x=5) from a neighbour class, created by the

SELECT strAS result

FROM expln('

SELECT x, sum(x) FROM A,B WHERE y = 5 AND A.x = B.y group by(x);')AS str

WHERE str NOTLIKE'Query Identifier%'

;-- Find the JOIN cardinality from a neighbour class.

SELECT strAS result

FROM expln('

SELECT x, sum(x) FROM A WHERE x = 5 group by(x);')AS str

WHERE str NOTLIKE'Query Identifier%';

SELECT x, sum(x) FROM A WHERE x = 5 group by(x);')AS str;

SELECT strAS result

FROM expln('

SELECT x FROM A where x < 10 group by(x);')AS str

WHERE str NOTLIKE'Query Identifier%'and str NOTLIKE'%Memory%';

WHERE str NOTLIKE'%Memory%';

SELECT strAS result

FROM expln('

SELECT x,y FROM A,B WHERE x < 10 AND A.x = B.y;')AS str

WHERE str NOTLIKE'Query Identifier%'and str NOTLIKE'%Memory%';

WHERE str NOTLIKE'%Memory%';

SELECT strAS result

FROM expln('

SELECT x FROM A,B where x < 10 and y > 10 group by(x);')AS str

WHERE str NOTLIKE'Query Identifier%'and str NOTLIKE'%Memory%';

WHERE str NOTLIKE'%Memory%';

SELECT strAS result

FROM expln('

SELECT x,y FROM A,B WHERE x < 10 and y > 10 AND A.x = B.y;')AS str

WHERE str NOTLIKE'Query Identifier%'and str NOTLIKE'%Memory%'

WHERE str NOTLIKE'%Memory%'

;

RESET enable_material;

SELECT strFROM expln('

SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1')AS str

WHERE str NOTLIKE'%Heap Blocks%'AND str NOTLIKE'%Query Identifier%';

WHERE str NOTLIKE'%Heap Blocks%';

ANALYZE t;