errmsg("AQO module could be loaded only on startup."),

errdetail("Add 'aqo' into the shared_preload_libraries list.")));

EnableQueryId();

DefineCustomEnumVariable("aqo.mode",

"Mode of aqo usage.",

NULL,

(5 rows)

-- Push down base filters. Use verbose mode to see filters.

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

SELECT x FROM frgn WHERE x < 10;

ERROR: syntax error at or near ")"

LINE 1: ...LAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE))

^

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

SELECT x FROM frgn WHERE x < 10;

QUERY PLAN

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%';

str

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

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

AQO not used

JOINS: 0

(7 rows)

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%';

str

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

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

AQO: rows=1, error=0%

Output: x

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

Using aqo: true

AQO mode: LEARN

JOINS: 0

(7 rows)

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

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

QUERY PLAN

(6 rows)

-- TODO: Should learn on postgres_fdw nodes

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

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

QUERY PLAN

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%';

str

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

Foreign Scan (actual rows=1 loops=1)

AQO not used

JOINS: 0

(6 rows)

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

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

QUERY PLAN

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%';

str

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

Foreign Scan (actual rows=0 loops=1)

AQO not used

CREATE EXTENSION aqo;

-- Utility tool. Allow to filter system-dependent strings from an explain output.

CREATE OR REPLACE FUNCTION expln(query_string text) RETURNS SETOF text AS $$

BEGIN

RETURN QUERY

EXECUTE format('%s', query_string);

RETURN;

END;

$$ LANGUAGE PLPGSQL;

SET aqo.join_threshold = 0;

SET aqo.mode = 'learn';

SET aqo.show_details = true;

(1 row)

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

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

SELECT x FROM t;

QUERY PLAN

SELECT str FROM expln('

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

SELECT x FROM t;

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

str

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

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

AQO not used

JOINS: 0

(6 rows)

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

SELECT x FROM t;

QUERY PLAN

SELECT str FROM expln('

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

SELECT x FROM t;

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

str

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

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

AQO: rows=100, error=0%

CREATE EXTENSION aqo;

-- Utility tool. Allow to filter system-dependent strings from an explain output.

CREATE OR REPLACE FUNCTION expln(query_string text) RETURNS SETOF text AS $$

BEGIN

RETURN QUERY

EXECUTE format('%s', query_string);

RETURN;

END;

$$ LANGUAGE PLPGSQL;

SET aqo.join_threshold = 0;

SET aqo.mode = 'learn';

SET aqo.show_details = 'on';

(11 rows)

SELECT str FROM expln('

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

SELECT * FROM t GROUP BY (x) HAVING x > 3;

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

SELECT * FROM t GROUP BY (x) HAVING x > 3;

') AS str WHERE str NOT LIKE '%Memory Usage%';

str

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

-- any prediction on number of fetched tuples.

-- So, if selectivity was wrong we could make bad choice of Scan operation.

-- For example, we could choose suboptimal index.

--

-- Returns string-by-string explain of a query. Made for removing some strings

-- from the explain output.

--

CREATE OR REPLACE FUNCTION expln(query_string text) RETURNS SETOF text AS $$

BEGIN

RETURN QUERY

EXECUTE format('EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF) %s', query_string);

RETURN;

END;

$$ LANGUAGE PLPGSQL;

-- Turn off statistics gathering for simple demonstration of filtering problem.

ALTER TABLE t SET (autovacuum_enabled = 'false');

CREATE INDEX ind1 ON t(x);

50

(1 row)

SELECT str AS result

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

WHERE str NOT LIKE '%Heap Blocks%';

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%';

str

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

Aggregate (actual rows=1 loops=1)

AQO not used

| SELECT count(*) FROM t WHERE +

| x = (SELECT avg(x) FROM t t0 WHERE t0.x = t.x + 21) OR +

| x IN (SELECT avg(x) FROM t t0 WHERE t0.x = t.x + 21);

0.106 | +

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

| SELECT * FROM t GROUP BY (x) HAVING x > 3; +

|

0.454 | SELECT count(*) FROM (SELECT x, y FROM t1 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1;

0.000 | SELECT count(*) FROM ( +

| SELECT count(*) AS x FROM ( +

0.000 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +

| SELECT count(*) FROM t WHERE (SELECT avg(x) FROM t t0 WHERE t0.x = t.x) = +

| (SELECT avg(x) FROM t t0 WHERE t0.x = t.x);

0.106 | +

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

| SELECT * FROM t GROUP BY (x) HAVING x > 3; +

|

0.000 | SELECT count(*) FROM +

| (SELECT * FROM t WHERE x % 3 < (SELECT avg(x) FROM t t0 WHERE t0.x = t.x)) AS q1 +

| JOIN +

staticList**get_clause_args_ptr(Expr*clause);

staticboolclause_is_eq_clause(Expr*clause);

get_query_hash(Query*parse,constchar*query_text)

{

char*str_repr;

uint64hash;

str_repr=remove_locations(remove_consts(nodeToString(parse)));

hash=DatumGetUInt64(hash_any_extended((void*)str_repr,strlen(str_repr),0));

returnhash;

}

externuint64get_query_hash(Query*parse,constchar*query_text);

externboollist_member_uint64(constList*list,uint64datum);

externList*list_copy_uint64(List*list);

externList*lappend_uint64(List*list,uint64datum);

MemoryContextSwitchTo(oldctx);

oldctx=MemoryContextSwitchTo(AQOUtilityMemCtx);

query_context.query_hash=get_query_hash(parse,query_string);

if (parse->queryId==UINT64CONST(0))

JumbleQuery(parse,query_string);

Assert(parse->utilityStmt==NULL);

Assert(parse->queryId!=UINT64CONST(0));

query_context.query_hash=parse->queryId;

MemoryContextSwitchTo(oldctx);

MemoryContextReset(AQOUtilityMemCtx);

CREATE EXTENSION aqo;

CREATE EXTENSION postgres_fdw;

SETaqo.mode='learn';

SETaqo.show_details='true';-- show AQO info for each node and entire query.

SETaqo.show_hash='false';-- a hash value is system-depended. Ignore it.

SELECT xFROM frgn;

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

SELECT xFROM frgnWHERE x<10;

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

SELECT xFROM frgnWHERE x<10;

SELECT strFROM expln('

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

SELECT strFROM expln('

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

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

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

')AS strWHERE str NOTLIKE'%Sort Method%';

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

SELECT*FROM frgnAS a, frgnAS bWHEREa.x=b.x;

SELECT strFROM expln('

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

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

SELECT*FROM frgnAS a, frgnAS bWHEREa.x<b.x;

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

SELECT*FROM frgnAS a, frgnAS bWHEREa.x<b.x;

SELECT strFROM expln('

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

DROP EXTENSION aqo CASCADE;

DROP EXTENSION postgres_fdw CASCADE;

CREATE EXTENSION aqo;

SETaqo.join_threshold=0;

CREATE OR REPLACEFUNCTIONexpln(query_stringtext) RETURNS SETOFtextAS $$

RETURN QUERY

EXECUTE format('%s', query_string);

RETURN;

END;

$$ LANGUAGE PLPGSQL;

SETaqo.join_threshold=0;

SETaqo.mode='learn';

SETaqo.show_details= true;

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

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

SELECT xFROM t;

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

SELECT xFROM t;

SELECT strFROM expln('

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

SELECT strFROM expln('

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

SETaqo.mode='disabled';