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

(0 rows)

SELECT learn_aqo,use_aqo,auto_tuning,cardinality_error_without_aqo ce,executions_without_aqo nex

CREATE OR REPLACE FUNCTION round_array (double precision[])

RETURNS double precision[]

LANGUAGE SQL

AS $$

SELECT array_agg(round(elem::numeric, 3))

FROM unnest($1) as arr(elem);

$$;

SELECT learn_aqo,use_aqo,auto_tuning,round_array(cardinality_error_without_aqo) ce,executions_without_aqo nex

FROM aqo_queries AS aq JOIN aqo_query_stat AS aqs

ON aq.queryid = aqs.queryid

ORDER BY (cardinality_error_without_aqo);

learn_aqo | use_aqo | auto_tuning | ce | nex

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

f | f | f | {0.8637762840285226} | 1

f | f | f | {2.9634630129852053} | 1

learn_aqo | use_aqo | auto_tuning |ce | nex

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

f | f | f | {0.864} | 1

f | f | f | {2.963} | 1

(2 rows)

SELECT query_text FROM aqo_query_texts ORDER BY (md5(query_text));

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

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

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

QUERY PLAN

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

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

Aggregate (actual rows=1 loops=1)

AQO not used

-> Bitmap Heap Scan on t (actual rows=50 loops=1)

Output: count(*)

-> Bitmap Heap Scan on public.t (actual rows=50 loops=1)

AQO: rows=50, error=0%

Recheck Cond: (mod(x, 3) = 1)

Filter: (x < 3)

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

Filter: (t.x < 3)

Rows Removed by Filter: 300

Heap Blocks: exact=5

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

AQO not used

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

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

Using aqo: true

AQO mode: LEARN

JOINS: 0

SELECTcount(*)FROM personWHERE age<18AND passportIS NOT NULL;

SELECT*FROM aqo_data;

SELECT learn_aqo,use_aqo,auto_tuning,cardinality_error_without_aqo ce,executions_without_aqo nex

CREATE OR REPLACEFUNCTIONround_array (double precision[])

RETURNSdouble precision[]

LANGUAGE SQL

AS $$

SELECT array_agg(round(elem::numeric,3))

FROM unnest($1)as arr(elem);

$$;

SELECT learn_aqo,use_aqo,auto_tuning,round_array(cardinality_error_without_aqo) ce,executions_without_aqo nex

FROM aqo_queriesAS aqJOIN aqo_query_statAS aqs

ONaq.queryid=aqs.queryid

ORDER BY (cardinality_error_without_aqo);

CREATE OR REPLACEFUNCTIONexpln(query_stringtext) RETURNS SETOFtextAS $$

RETURN QUERY

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

RETURN;

END;

$$ LANGUAGE PLPGSQL;

ALTERTABLE tSET (autovacuum_enabled='false');

CREATEINDEXind1ON t(x);

CREATEINDEXind2ON t(mod(x,3));

SELECTcount(*)FROM tWHERE x<3AND mod(x,3)=1;

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

SELECTcount(*)FROM tWHERE x<3AND mod(x,3)=1;

SELECT strAS result

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

WHERE str NOTLIKE'%Heap Blocks%';

ANALYZE t;