fdw_srcdir =$(top_srcdir)/contrib/postgres_fdw

stat_srcdir =$(top_srcdir)/contrib/pg_stat_statements

PG_CPPFLAGS += -I$(libpq_srcdir) -I$(fdw_srcdir) -I$(stat_srcdir)

EXTRA_REGRESS_OPTS=--temp-config=$(top_srcdir)/$(subdir)/conf.add

EXTRA_REGRESS_OPTS=--temp-config=$(top_srcdir)/$(subdir)/aqo.conf

EXTRA_INSTALL = contrib/postgres_fdw contrib/pg_stat_statements

DATA = aqo--1.0.sql aqo--1.0--1.1.sql aqo--1.1--1.2.sql aqo--1.2.sql\

*`stable9_6`.

*`stable11` - for PG v10 and v11.

*`stable12` - for PG v12.

* the`master` branch of the AQO repository correctly works with PGv13 and the PostgreSQL`master` branch.

*`stable13` - for PG v13.

*`stable14` - for PG v14.

*`stable15` - for PG v15.

* the`master` branch of the AQO repository correctly works with PGv15 and the PostgreSQL`master` branch.

The module contains a patch and an extension. Patch has to be applied to the

sources of PostgresSQL. Patch affects header files, that is why PostgreSQL

DROPTABLEpublic.aqo_queries CASCADE;

DROPTABLEpublic.aqo_query_texts CASCADE;

DROPTABLEpublic.aqo_query_stat CASCADE;

DROPFUNCTION invalidate_deactivated_queries_cache;

CREATE OR REPLACEFUNCTIONaqo_cleanup(OUT nfsinteger, OUT nfssinteger)

RETURNS SETOF record

AS'MODULE_PATHNAME','aqo_cleanup'

LANGUAGE C STRICT VOLATILE;

AQOLearnMemCtx=AllocSetContextCreate(AQOTopMemCtx,

"AQOLearnMemoryContext",

ALLOCSET_DEFAULT_SIZES);

ALLOCSET_DEFAULT_SIZES);

RegisterResourceReleaseCallback(aqo_free_callback,NULL);

RegisterAQOPlanNodeMethods();

autovacuum = off

shared_preload_libraries = 'postgres_fdw, aqo'

max_parallel_maintenance_workers = 1 # switch off parallel workers because of unsteadiness

aqo.wide_search = 'on'

List of functions

Schema | Name | Result data type | Argument data types | Type

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

public | aqo_cleanup |record | OUT nfs integer, OUT nfss integer | func

public | aqo_cleanup |SETOF record | OUT nfs integer, OUT nfss integer | func

(1 row)

\df aqo_reset

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

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

FROM generate_series(1,1000) AS gs;

ANALYZE t, t1;

--

-- Do not support HAVINGclause for now.

-- Do not support HAVINGclauses for now.

--

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

count

JOINS: 0

(11 rows)

SELECT str FROM expln('

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

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

HashAggregate (actual rows=17 loops=1)

AQO not used

Group Key: x

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

AQO: rows=801, error=0%

Filter: (x > 3)

Rows Removed by Filter: 199

Using aqo: true

AQO mode: LEARN

JOINS: 0

(10 rows)

--

-- Doesn't estimates GROUP BY clause

--

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 +

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

| ON q1.x = q2.x+1;

(12 rows)

(13 rows)

DROP TABLE t,t1 CASCADE; -- delete all tables used in the test

SELECT count(*) FROM aqo_data; -- Just to detect some changes in the logic. May some false positives really bother us here?

count

-------

(1 row)

SELECT * FROM aqo_cleanup();

nfs | nfss

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

12 |42

13 |44

(1 row)

SELECT count(*) FROM aqo_data; -- No one row should be returned

Assert(LWLockHeldByMeInMode(&aqo_state->lock,LW_EXCLUSIVE)||

LWLockHeldByMeInMode(&aqo_state->lock,LW_SHARED));

Assert(hdr->magic==AQO_SHARED_MAGIC);

Assert(hdr&&ptr);

Assert(hdr&&ptr&&hdr->rows>0);

data->rows=hdr->rows;

data->cols=hdr->cols;

}

}

ptr= (char*)hdr+sizeof(dsm_block_hdr)+ (sizeof(double)*data->cols*aqo_K);

memcpy(data->targets,ptr,sizeof(double)*hdr->rows);

ptr+=sizeof(double)*aqo_K;

memcpy(data->rfactors,ptr,sizeof(double)*hdr->rows);

returncalculate_size(hdr->cols,*reloids);

}

return0;

}

aqo_data_store(hdr->key.fs,hdr->key.fss,&data,reloids);

if (!hash_search(fss_htab, (void*)&hdr->key,HASH_REMOVE,NULL))

elog(ERROR,"[AQO] Flush: local ML cache is corrupted.");

elog(PANIC,"[AQO] Flush: local ML cache is corrupted.");

}

reset_dsm_cache();

while ((entry= (htab_entry*)hash_seq_search(&status))!=NULL)

{

if (!hash_search(fss_htab, (void*)&entry->key,HASH_REMOVE,NULL))

elog(ERROR,"[AQO] The local ML cache is corrupted.");

elog(PANIC,"[AQO] The local ML cache is corrupted.");

}

LWLockRelease(&aqo_state->lock);

}

{

TimestampTzfin_time;

if (IsParallelWorker())

return false;

if (!get_timeout_active(STATEMENT_TIMEOUT)|| !aqo_learn_statement_timeout)

return false;

test: forced_stat_collection

test: unsupported

test: clean_aqo_data

test: parallel_workers

test: plancache

# Performance-dependent test. Can be ignored if executes in containers or on slow machines

ignore: statement_timeout

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

ANALYZE t, t1;

SELECTcount(*)FROM (SELECT*FROM tGROUP BY (x)HAVING x>3)AS q1;

EXPLAIN (COSTS OFF)

SELECTcount(*)FROM (SELECT*FROM tGROUP BY (x)HAVING x>3)AS q1;

SELECT strFROM expln('

')AS strWHERE str NOTLIKE'%Memory Usage%';