AQO not used

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

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

AQO not used

Sort Key: a.x

-> Seq Scan on a (actual rows=10 loops=1)

AQO not used

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

AQO not used

Sort Key: b.x

-> Seq Scan on b (actual rows=100 loops=1)

AQO not used

Using aqo: true

AQO mode: LEARN

JOINS: 0

(16 rows)

(14 rows)

-- TODO: Using method of other classes neighbours we get a bad estimation.

SELECT str AS result

-> Seq Scan on b (actual rows=100 loops=1)

AQO: rows=100, error=0%

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

AQO not used

-> Seq Scan on a (actual rows=10 loops=1)

AQO: rows=10, error=0%

Using aqo: true

AQO mode: LEARN

JOINS: 0

(12 rows)

(11 rows)

-- Look into the reason: two JOINs from different classes have the same FSS.

SELECT to_char(d1.targets[1], 'FM999.00') AS target FROM aqo_data d1

Output: a.x, b.y

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

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

AQO not used

Output: a.x

Sort Key: a.x

-> Seq Scan on public.a (actual rows=1000 loops=1)

AQO: rows=1000, error=0%

Output: a.x

Filter: (a.x < 10)

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

AQO not used

Output: b.y

Sort Key: b.y

-> Seq Scan on public.b (actual rows=1000 loops=1)

Using aqo: true

AQO mode: LEARN

JOINS: 0

(22 rows)

(20 rows)

-- cardinality 100 in Seq Scan on a and Seq Scan on b

SELECT str AS result

Output: a.x

Filter: (a.x < 10)

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

AQO not used

Output: b.y

-> Seq Scan on public.b (actual rows=0 loops=1)

AQO: rows=1, error=100%

Using aqo: true

AQO mode: LEARN

JOINS: 0

(19 rows)

(18 rows)

RESET enable_material;

DROP TABLE a,b CASCADE;

-- Specifically test AQO machinery for queries uses partial paths and executed

-- with parallel workers.

CREATE EXTENSION aqo;

-- Utility tool. Allow to filter system-dependent strings from 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;

-- Be generous with a number parallel workers to test the machinery

SET max_parallel_workers = 64;

SET max_parallel_workers_per_gather = 64;

-- Enforce usage of parallel workers

SET parallel_setup_cost = 0.1;

SET parallel_tuple_cost = 0.0001;

CREATE TABLE t AS (

SELECT x AS id, repeat('a', 512) AS payload FROM generate_series(1, 1E5) AS x

);

ANALYZE t;

-- Simple test. Check serialization machinery mostly.

SELECT count(*) FROM t WHERE id % 100 = 0; -- Learning stage

count

-------

1000

(1 row)

SELECT str FROM expln('

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

SELECT count(*) FROM t WHERE id % 100 = 0;') AS str

WHERE str NOT LIKE '%Worker%';

str

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

Finalize Aggregate (actual rows=1 loops=1)

AQO not used

-> Gather (actual rows=3 loops=1)

AQO not used

-> Partial Aggregate (actual rows=1 loops=3)

AQO not used

-> Parallel Seq Scan on t (actual rows=333 loops=3)

AQO: rows=1000, error=0%

Filter: ((id % '100'::numeric) = '0'::numeric)

Rows Removed by Filter: 33000

Using aqo: true

AQO mode: LEARN

JOINS: 0

(13 rows)

-- More complex query just to provoke errors

SELECT count(*) FROM

(SELECT id FROM t WHERE id % 100 = 0 GROUP BY (id)) AS q1,

(SELECT max(id) AS id, payload FROM t

WHERE id % 101 = 0 GROUP BY (payload)) AS q2

WHERE q1.id = q2.id; -- Learning stage

count

-------

0

(1 row)

-- XXX: Why grouping prediction isn't working here?

SELECT str FROM expln('

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

SELECT count(*) FROM

(SELECT id FROM t WHERE id % 100 = 0 GROUP BY (id)) AS q1,

(SELECT max(id) AS id, payload FROM t

WHERE id % 101 = 0 GROUP BY (payload)) AS q2

WHERE q1.id = q2.id;') AS str

WHERE str NOT LIKE '%Workers%' AND str NOT LIKE '%Sort Method%';

str

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

Aggregate (actual rows=1 loops=1)

AQO not used

-> Merge Join (actual rows=0 loops=1)

AQO not used

Merge Cond: (q2.id = t_1.id)

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

Sort Key: q2.id

-> Subquery Scan on q2 (actual rows=1 loops=1)

AQO not used

-> Finalize GroupAggregate (actual rows=1 loops=1)

AQO not used

Group Key: t.payload

-> Gather Merge (actual rows=3 loops=1)

AQO not used

-> Partial GroupAggregate (actual rows=1 loops=3)

AQO not used

Group Key: t.payload

-> Sort (actual rows=330 loops=3)

AQO not used

Sort Key: t.payload

-> Parallel Seq Scan on t (actual rows=330 loops=3)

AQO: rows=991, error=0%

Filter: ((id % '101'::numeric) = '0'::numeric)

Rows Removed by Filter: 33003

-> Group (actual rows=1000 loops=1)

AQO not used

Group Key: t_1.id

-> Gather Merge (actual rows=1000 loops=1)

AQO not used

-> Group (actual rows=333 loops=3)

AQO not used

Group Key: t_1.id

-> Sort (actual rows=333 loops=3)

AQO not used

Sort Key: t_1.id

-> Parallel Seq Scan on t t_1 (actual rows=333 loops=3)

AQO: rows=991, error=-1%

Filter: ((id % '100'::numeric) = '0'::numeric)

Rows Removed by Filter: 33000

Using aqo: true

AQO mode: LEARN

JOINS: 1

(42 rows)

RESET parallel_tuple_cost;

RESET parallel_setup_cost;

RESET max_parallel_workers;

RESET max_parallel_workers_per_gather;

DROP TABLE t;

DROP FUNCTION expln;

DROP EXTENSION aqo;

Filter: (x <> t_1.x)

Rows Removed by Filter: 50

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

AQO not used

-> Seq Scan on t (actual rows=851 loops=1)

AQO: rows=851, error=0%

Filter: (((x % 3))::numeric < (SubPlan 1))

Using aqo: true

AQO mode: LEARN

JOINS: 1

(31 rows)

(30 rows)

-- Two identical subplans in a clause

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

Filter: (t.x < 3)

Rows Removed by Filter: 300

-> Bitmap Index Scan on ind2 (actual rows=350 loops=1)

AQO not used

Index Cond: (mod(t.x, 3) = 1)

Using aqo: true

AQO mode: LEARN

JOINS: 0

(14 rows)

(13 rows)

-- Best choice is ...

ANALYZE t;

-------+------------------------------------------------------------------------------------------------

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

0.070 | SELECT count(*) FROM (SELECT * FROM t GROUP BY (x) HAVING x > 3) AS q1;

1.416 | SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1;

0.000 | SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1;

0.000 | SELECT * FROM +

| (SELECT * FROM t WHERE x < 0) AS t0 +

| JOIN +

get_aqo_plan_node(Plan*plan,boolcreate)

if (node==NULL)

{

if (!create)

return&DefaultAQOPlanNode;

returnNULL;

node=create_aqo_plan_node();

plan->ext_nodes=lappend(plan->ext_nodes,node);

}

aqo_create_plan_hook(PlannerInfo*root,Path*src,Plan**dest)

if (prev_create_plan_hook)

prev_create_plan_hook(root,src,dest);

if (!query_context.use_aqo&& !query_context.learn_aqo)

if (!query_context.use_aqo&& !query_context.learn_aqo&&

!query_context.collect_stat)

return;

is_join_path= (src->type==T_NestPath||src->type==T_MergePath||

}

{

node->prediction=src->parent->predicted_cardinality;

node->fss=src->parent->fss_hash;

}

{

AQOPlanNode*node= (AQOPlanNode*)enode;

node->had_path= false;

node->jointype=0;

node->parallel_divisor=1.0;

node->was_parametrized= false;

WRITE_INT_FIELD(fss);

WRITE_FLOAT_FIELD(prediction,"%.0f");

constchar*token;

intlength;

READ_BOOL_FIELD(had_path);

READ_ENUM_FIELD(jointype,JoinType);

READ_FLOAT_FIELD(parallel_divisor);

READ_BOOL_FIELD(was_parametrized);

local_node->had_path= false;

local_node->jointype=0;

local_node->parallel_divisor=1.0;

local_node->was_parametrized= false;

local_node->rels=palloc0(sizeof(RelSortOut));

local_node->clauses=NIL;