shared_preload_libraries = 'postgres_fdw, aqo'

max_parallel_maintenance_workers = 1 # switch off parallel workers because of unsteadiness

aqo.wide_search = 'on'

if (es->format == EXPLAIN_FORMAT_TEXT)

appendStringInfoChar(es->str, '\n');

index682b28ed72..3a5c615deb 100644

index692b6c1559..580d04d784 100644

@@ -132,6 +132,7 @@ CopyPlanFields(const Plan *from, Plan *newnode)

/*

index7237b52e96..5e2ee2732a 100644

index21ececf0c2..a0e7a7ebca 100644

@@ -342,6 +342,7 @@ _outPlanInfo(StringInfo str, const Plan *node)

/*

index62c945b6c5..a39046ca56 100644

index7976b369ba..604314e0b3 100644

@@ -1580,6 +1580,11 @@ ReadCommonPlan(Plan *local_node)

/*

index60e7fda6a9..5732c7a685 100644

index27c665ac12..f599fba755 100644

@@ -145,7 +145,8 @@ static List *extract_rollup_sets(List *groupingSets);

grouping_sets_data *gd,

List *target_list);

static RelOptInfo *create_grouping_paths(PlannerInfo *root,

@@ -3682,7 +3683,8 @@ standard_qp_callback(PlannerInfo *root, void *extra)

@@ -3686,7 +3687,8 @@ standard_qp_callback(PlannerInfo *root, void *extra)

*/

static double

get_number_of_groups(PlannerInfo *root,

grouping_sets_data *gd,

List *target_list)

{

@@ -3719,7 +3721,7 @@ get_number_of_groups(PlannerInfo *root,

@@ -3723,7 +3725,7 @@ get_number_of_groups(PlannerInfo *root,

GroupingSetData *gs = lfirst_node(GroupingSetData, lc2);

doublenumGroups = estimate_num_groups(root,

groupExprs,

&gset);

gs->numGroups = numGroups;

@@ -3744,7 +3746,7 @@ get_number_of_groups(PlannerInfo *root,

@@ -3748,7 +3750,7 @@ get_number_of_groups(PlannerInfo *root,

GroupingSetData *gs = lfirst_node(GroupingSetData, lc2);

doublenumGroups = estimate_num_groups(root,

groupExprs,

&gset);

gs->numGroups = numGroups;

@@ -3760,8 +3762,8 @@ get_number_of_groups(PlannerInfo *root,

@@ -3764,8 +3766,8 @@ get_number_of_groups(PlannerInfo *root,

groupExprs = get_sortgrouplist_exprs(parse->groupClause,

target_list);

}

}

else if (parse->groupingSets)

@@ -4147,7 +4149,8 @@ create_ordinary_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel,

@@ -4151,7 +4153,8 @@ create_ordinary_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel,

* Estimate number of groups.

*/

dNumGroups = get_number_of_groups(root,

gd,

extra->targetList);

@@ -6931,13 +6934,15 @@ create_partial_grouping_paths(PlannerInfo *root,

@@ -6935,13 +6938,15 @@ create_partial_grouping_paths(PlannerInfo *root,

if (cheapest_total_path != NULL)

dNumPartialGroups =

get_number_of_groups(root,

return ppi;

index821844ada3..85b2482114 100644

index37458da096..248a1875a1 100644

index 90f02ce6fd..f3e2138ee2 100644

index 90f02ce6fd..88c332164d 100644

@@ -159,6 +159,12 @@ typedef struct Plan

@@ -159,6 +159,9 @@ typedef struct Plan

*/

Bitmapset *extParam;

Bitmapset *allParam;

} Plan;

(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;

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;

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

JOINS: 0

(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

-- Should learn on postgres_fdw nodes

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;

str

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

Foreign Scan (actual rows=1 loops=1)

AQO: rows=1, error=0%

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;

str

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

Foreign Scan (actual rows=0 loops=1)

AQO: rows=1, error=100%

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

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

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

-- 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

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

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

Aggregate (actual rows=1 loops=1)

AQO not used

List*cur_classes=NIL;

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 str;

SELECT strFROM expln('

')AS str;

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 str;

CREATETABLElocal_a(aidintprimary key, avaltext);

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

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 str;

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 regexp_replace(

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

')AS str;

SELECT regexp_replace(

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

')AS str;

SETaqo.mode='disabled';