Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit404e503

Browse files
danolivoAlena Rybakina
authored and
Alena Rybakina
committed
Try to search in surrounding feature spaces for the fss data.
Side effect: ML knowledge base become smaller because we make right decisionmore quickly.TODO: unexpectedly found out that we don't learn on postgres_fdw plan nodes.
1 parent38e9c13 commit404e503

20 files changed

+216
-150
lines changed

‎Makefile‎

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -4,9 +4,10 @@ EXTENSION = aqo
44
EXTVERSION = 1.5
55
PGFILEDESC = "AQO - Adaptive Query Optimization"
66
MODULE_big = aqo
7-
OBJS = aqo.o auto_tuning.o cardinality_estimation.o cardinality_hooks.o\
8-
hash.o machine_learning.o path_utils.o postprocessing.o preprocessing.o\
9-
selectivity_cache.o storage.o utils.o learn_cache.o aqo_shared.o$(WIN32RES)
7+
OBJS =$(WIN32RES)\
8+
aqo.o auto_tuning.o cardinality_estimation.o cardinality_hooks.o\
9+
hash.o machine_learning.o path_utils.o postprocessing.o preprocessing.o\
10+
selectivity_cache.o storage.o utils.o learn_cache.o aqo_shared.o
1011

1112
TAP_TESTS = 1
1213

‎aqo--1.0.sql‎

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -50,5 +50,3 @@ CREATE FUNCTION invalidate_deactivated_queries_cache() RETURNS trigger
5050
CREATETRIGGERaqo_queries_invalidate AFTERUPDATEORDELETEOR TRUNCATE
5151
ONpublic.aqo_queries FOR EACH STATEMENT
5252
EXECUTE PROCEDURE invalidate_deactivated_queries_cache();
53-
CREATEINDEXaqo_fss_idx
54-
onpublic.aqo_data (fsspace_hash);

‎aqo.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -283,7 +283,8 @@ extern bool update_query(uint64 qhash, uint64 fhash,
283283
externbooladd_query_text(uint64query_hash,constchar*query_string);
284284
externboolload_fss_ext(uint64fs,intfss,OkNNrdata*data,List**reloids,
285285
boolisSafe);
286-
externboolload_fss(uint64fs,intfss,OkNNrdata*data,List**reloids,booluse_idx_fss);
286+
externboolload_fss(uint64fs,intfss,OkNNrdata*data,List**reloids,
287+
boolwideSearch);
287288
externboolupdate_fss_ext(uint64fs,intfss,OkNNrdata*data,
288289
List*reloids,boolisTimedOut);
289290
externboolupdate_fss(uint64fs,intfss,OkNNrdata*data,List*reloids);

‎cardinality_estimation.c‎

Lines changed: 10 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -87,14 +87,21 @@ predict_for_relation(List *clauses, List *selectivities, List *relsigns,
8787
{
8888
/*
8989
* Due to planning optimizer tries to build many alternate paths. Many
90-
* ofthese not used in final query execution path. Consequently, only
91-
* small part of paths was used for AQO learning andfetch into the AQO
92-
* knowledge base.
90+
* ofthem aren't used in final query execution path. Consequently, only
91+
* small part of paths was used for AQO learning andstored into
92+
*the AQOknowledge base.
9393
*/
94+
95+
/* Try to search in surrounding feature spaces for the same node */
9496
if (!load_fss(query_context.fspace_hash,*fss,&data,NULL, false))
9597
result=-1;
9698
else
99+
{
100+
elog(DEBUG5,"[AQO] Make prediction for fss %d by a neighbour "
101+
"includes %d feature(s) and %d fact(s).",
102+
*fss,data.cols,data.rows);
97103
result=OkNNr_predict(&data,features);
104+
}
98105
}
99106
#ifdefAQO_DEBUG_PRINT
100107
predict_debug_output(clauses,selectivities,relsigns,*fss,result);

‎expected/aqo_fdw.out‎

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -85,8 +85,8 @@ SELECT x FROM frgn WHERE x < -10; -- AQO ignores constants
8585

8686
-- Trivial JOIN push-down.
8787
SELECT str FROM expln('
88-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
89-
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
88+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
89+
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
9090
') AS str WHERE str NOT LIKE '%Sort Method%';
9191
str
9292
-------------------------------------------
@@ -98,8 +98,9 @@ SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
9898
JOINS: 0
9999
(6 rows)
100100

101+
-- TODO: Should learn on postgres_fdw nodes
101102
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
102-
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
103+
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
103104
QUERY PLAN
104105
--------------------------------------------------------------------------------------------------------
105106
Foreign Scan (actual rows=1 loops=1)

‎expected/aqo_learn.out‎

Lines changed: 21 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -265,48 +265,48 @@ ORDER BY (md5(query_text))
265265
| | WHERE t1.a < 1 AND t2.b < 1 AND t2.c < 1 AND t2.d < 1 AND t1.a = t2.a;
266266
{1,1,1} | 4 | EXPLAIN SELECT * FROM aqo_test0 +
267267
| | WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
268-
{1,1,1,1,1} | 0 | SELECT count(*) FROM tmp1;
269-
{1,1,1,1,1} | 0 | SELECT count(*) FROM tmp1;
270-
{1} |2 | EXPlAIN SELECT t1.a, t2.b, t3.c +
268+
{1} | 0 | SELECT count(*) FROM tmp1;
269+
{1} | 0 | SELECT count(*) FROM tmp1;
270+
{1} |6 | EXPlAIN SELECT t1.a, t2.b, t3.c +
271271
| | FROM aqo_test1 AS t1, aqo_test0 AS t2, aqo_test0 AS t3 +
272272
| | WHERE t1.a < 1 AND t3.b < 1 AND t2.c < 1 AND t3.d < 0 AND t1.a = t2.a AND t1.b = t3.b;
273-
{1} |2 | EXPlAIN SELECT t1.a, t2.b, t3.c +
273+
{1} |1 | EXPlAIN SELECT t1.a, t2.b, t3.c +
274274
| | FROM aqo_test1 AS t1, aqo_test0 AS t2, aqo_test0 AS t3 +
275275
| | WHERE t1.a < 1 AND t3.b < 1 AND t2.c < 1 AND t3.d < 0 AND t1.a = t2.a AND t1.b = t3.b;
276-
{1} |3 | EXPlAIN SELECT t1.a, t2.b, t3.c +
276+
{1} |2 | EXPlAIN SELECT t1.a, t2.b, t3.c +
277277
| | FROM aqo_test1 AS t1, aqo_test0 AS t2, aqo_test0 AS t3 +
278278
| | WHERE t1.a < 1 AND t3.b < 1 AND t2.c < 1 AND t3.d < 0 AND t1.a = t2.a AND t1.b = t3.b;
279-
{1} |6 | EXPlAIN SELECT t1.a, t2.b, t3.c +
279+
{1} |3 | EXPlAIN SELECT t1.a, t2.b, t3.c +
280280
| | FROM aqo_test1 AS t1, aqo_test0 AS t2, aqo_test0 AS t3 +
281281
| | WHERE t1.a < 1 AND t3.b < 1 AND t2.c < 1 AND t3.d < 0 AND t1.a = t2.a AND t1.b = t3.b;
282-
{1,1} |1 | EXPlAIN SELECT t1.a, t2.b, t3.c +
282+
{1}|2 | EXPlAIN SELECT t1.a, t2.b, t3.c +
283283
| | FROM aqo_test1 AS t1, aqo_test0 AS t2, aqo_test0 AS t3 +
284284
| | WHERE t1.a < 1 AND t3.b < 1 AND t2.c < 1 AND t3.d < 0 AND t1.a = t2.a AND t1.b = t3.b;
285-
{1} | 4 | EXPLAIN SELECT t1.a, t2.b FROM aqo_test0 AS t1, aqo_test0 AS t2 +
286-
| | WHERE t1.a < 1 AND t1.b < 1 AND t2.c < 1 AND t2.d < 1;
287285
{1} | 2 | EXPLAIN SELECT t1.a, t2.b FROM aqo_test0 AS t1, aqo_test0 AS t2 +
288286
| | WHERE t1.a < 1 AND t1.b < 1 AND t2.c < 1 AND t2.d < 1;
289287
{1} | 2 | EXPLAIN SELECT t1.a, t2.b FROM aqo_test0 AS t1, aqo_test0 AS t2 +
290288
| | WHERE t1.a < 1 AND t1.b < 1 AND t2.c < 1 AND t2.d < 1;
291-
{1} | 1 | EXPLAIN SELECT t1.a AS a, t2.a AS b, t3.a AS c +
289+
{1} | 4 | EXPLAIN SELECT t1.a, t2.b FROM aqo_test0 AS t1, aqo_test0 AS t2 +
290+
| | WHERE t1.a < 1 AND t1.b < 1 AND t2.c < 1 AND t2.d < 1;
291+
{1} | 2 | EXPLAIN SELECT t1.a AS a, t2.a AS b, t3.a AS c +
292292
| | FROM aqo_test1 AS t1, aqo_test1 AS t2, aqo_test1 AS t3 +
293293
| | WHERE t1.a = t2.b AND t2.a = t3.b;
294-
{1} |2 | EXPLAIN SELECT t1.a AS a, t2.a AS b, t3.a AS c +
294+
{1} |1 | EXPLAIN SELECT t1.a AS a, t2.a AS b, t3.a AS c +
295295
| | FROM aqo_test1 AS t1, aqo_test1 AS t2, aqo_test1 AS t3 +
296296
| | WHERE t1.a = t2.b AND t2.a = t3.b;
297297
{1} | 0 | EXPLAIN SELECT t1.a AS a, t2.a AS b, t3.a AS c +
298298
| | FROM aqo_test1 AS t1, aqo_test1 AS t2, aqo_test1 AS t3 +
299299
| | WHERE t1.a = t2.b AND t2.a = t3.b;
300-
{1} |2 | EXPLAIN SELECT t1.a AS a, t2.a AS b, t3.a AS c, t4.a AS d +
300+
{1} |3 | EXPLAIN SELECT t1.a AS a, t2.a AS b, t3.a AS c, t4.a AS d +
301301
| | FROM aqo_test1 AS t1, aqo_test1 AS t2, aqo_test1 AS t3, aqo_test1 AS t4 +
302302
| | WHERE t1.a = t2.b AND t2.a = t3.b AND t3.a = t4.b;
303-
{1} |3 | EXPLAIN SELECT t1.a AS a, t2.a AS b, t3.a AS c, t4.a AS d +
303+
{1} |0 | EXPLAIN SELECT t1.a AS a, t2.a AS b, t3.a AS c, t4.a AS d +
304304
| | FROM aqo_test1 AS t1, aqo_test1 AS t2, aqo_test1 AS t3, aqo_test1 AS t4 +
305305
| | WHERE t1.a = t2.b AND t2.a = t3.b AND t3.a = t4.b;
306306
{1} | 1 | EXPLAIN SELECT t1.a AS a, t2.a AS b, t3.a AS c, t4.a AS d +
307307
| | FROM aqo_test1 AS t1, aqo_test1 AS t2, aqo_test1 AS t3, aqo_test1 AS t4 +
308308
| | WHERE t1.a = t2.b AND t2.a = t3.b AND t3.a = t4.b;
309-
{1} |0 | EXPLAIN SELECT t1.a AS a, t2.a AS b, t3.a AS c, t4.a AS d +
309+
{1} |2 | EXPLAIN SELECT t1.a AS a, t2.a AS b, t3.a AS c, t4.a AS d +
310310
| | FROM aqo_test1 AS t1, aqo_test1 AS t2, aqo_test1 AS t3, aqo_test1 AS t4 +
311311
| | WHERE t1.a = t2.b AND t2.a = t3.b AND t3.a = t4.b;
312312
(21 rows)
@@ -537,7 +537,8 @@ SELECT * FROM check_estimated_rows('SELECT * FROM aqo_test1;');
537537
20 | 20
538538
(1 row)
539539

540-
SELECT * FROM check_estimated_rows('SELECT * FROM aqo_test1 AS t1, aqo_test1 AS t2 WHERE t1.a = t2.b');
540+
SELECT * FROM check_estimated_rows('
541+
SELECT * FROM aqo_test1 AS t1, aqo_test1 AS t2 WHERE t1.a = t2.b');
541542
estimated | actual
542543
-----------+--------
543544
20 | 19
@@ -553,13 +554,13 @@ SELECT * FROM check_estimated_rows('
553554
SELECT *
554555
FROM aqo_test1 AS t1, aqo_test1 AS t2, aqo_test1 AS t3, aqo_test1 AS t4
555556
WHERE t1.a = t2.b AND t2.a = t3.b AND t3.a = t4.b;
556-
');
557+
'); -- Learn on the query
557558
estimated | actual
558559
-----------+--------
559560
20 | 17
560561
(1 row)
561562

562-
SELECT count(*) FROM -- Learn on the query
563+
SELECT count(*) FROM
563564
(SELECT fspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1
564565
;
565566
count
@@ -584,15 +585,16 @@ SELECT * FROM check_estimated_rows('SELECT * FROM aqo_test1;');
584585
20 | 20
585586
(1 row)
586587

587-
SELECT * FROM check_estimated_rows('SELECT * FROM aqo_test1 AS t1, aqo_test1 AS t2 WHERE t1.a = t2.b');
588+
SELECT * FROM check_estimated_rows(
589+
'SELECT * FROM aqo_test1 AS t1, aqo_test1 AS t2 WHERE t1.a = t2.b');
588590
estimated | actual
589591
-----------+--------
590592
19 | 19
591593
(1 row)
592594

593595
SELECT count(*) FROM
594596
(SELECT fspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1
595-
; -- Learn on a query with one join
597+
; -- Learn on anewquery with one join (cardinality of this join AQO extracted from previous 3-join query)
596598
count
597599
-------
598600
2

‎expected/clean_aqo_data.out‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -141,7 +141,7 @@ SELECT 'b'::regclass::oid AS b_oid \gset
141141
SELECT count(*) FROM aqo_data WHERE :a_oid=ANY(oids);
142142
count
143143
-------
144-
3
144+
2
145145
(1 row)
146146

147147
SELECT count(*) FROM aqo_queries WHERE
@@ -200,7 +200,7 @@ DROP TABLE a;
200200
SELECT aqo_cleanup();
201201
aqo_cleanup
202202
-------------
203-
(2,4)
203+
(2,3)
204204
(1 row)
205205

206206
/*

‎expected/look_a_like.out‎

Lines changed: 2 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,4 @@
1-
CREATE IF NOT EXISTS EXTENSION aqo;
2-
ERROR: syntax error at or near "IF"
3-
LINE 1: CREATE IF NOT EXISTS EXTENSION aqo;
4-
^
1+
CREATE EXTENSION aqo;
52
SET aqo.join_threshold = 0;
63
SET aqo.mode = 'learn';
74
SET aqo.show_details = 'on';
@@ -238,3 +235,4 @@ WHERE str NOT LIKE 'Query Identifier%';
238235
JOINS: 0
239236
(20 rows)
240237

238+
DROP EXTENSION aqo CASCADE;

‎expected/relocatable.out‎

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,3 @@
1-
DROP EXTENSION aqo CASCADE;
21
CREATE EXTENSION aqo;
32
SET aqo.join_threshold = 0;
43
SET aqo.mode = 'learn'; -- use this mode for unconditional learning

‎expected/temp_tables.out‎

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -126,18 +126,18 @@ SELECT * FROM check_estimated_rows('
126126

127127
SELECT * FROM check_estimated_rows('
128128
SELECT pt.x, avg(pt.y) FROM pt,ttd WHERE pt.x = ttd.x GROUP BY (pt.x);
129-
'); --TODO:Should use AQO estimation with another temp table of the same structure
129+
'); -- Should use AQO estimation with another temp table of the same structure
130130
estimated | actual
131131
-----------+--------
132-
100 | 0
132+
1 | 0
133133
(1 row)
134134

135135
SET aqo.mode = 'forced'; -- Now we use all fss records for each query
136136
DROP TABLE pt;
137-
SELECT aqo_cleanup();
138-
aqo_cleanup
139-
-------------
140-
(2,6)
137+
SELECT* FROMaqo_cleanup();
138+
nfs | nfss
139+
-----+------
140+
2 | 5
141141
(1 row)
142142

143143
CREATE TABLE pt AS SELECT x AS x, (x % 10) AS y FROM generate_series(1,100) AS x;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp