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

Commit8879624

Browse files
committed
Another attempt to resolve contradictory between oid-based and relname-based
approaches to organize ML storage base.In this patch we store list of oids of persistent tables for each record in theaqo_data table to have a possibility of cleaning records which depends onremoved tables.On the other hand, we use relnames (tupDesc hash for TEMP tables) to form akind of signature of a table. This signature is used for a feature subspacegeneration.
1 parentde286fe commit8879624

28 files changed

+967
-376
lines changed

‎Makefile‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
# contrib/aqo/Makefile
22

33
EXTENSION = aqo
4-
EXTVERSION = 1.4
4+
EXTVERSION = 1.5
55
PGFILEDESC = "AQO - Adaptive Query Optimization"
66
MODULE_big = aqo
77
OBJS = aqo.o auto_tuning.o cardinality_estimation.o cardinality_hooks.o\
@@ -24,6 +24,7 @@ REGRESS =aqo_disabled \
2424
clean_aqo_data\
2525
plancache\
2626
statement_timeout\
27+
temp_tables\
2728
top_queries
2829

2930
fdw_srcdir =$(top_srcdir)/contrib/postgres_fdw
@@ -33,7 +34,7 @@ EXTRA_REGRESS_OPTS=--temp-config=$(top_srcdir)/$(subdir)/conf.add
3334
EXTRA_INSTALL = contrib/postgres_fdw contrib/pg_stat_statements
3435

3536
DATA = aqo--1.0.sql aqo--1.0--1.1.sql aqo--1.1--1.2.sql aqo--1.2.sql\
36-
aqo--1.2--1.3.sql aqo--1.3--1.4.sql
37+
aqo--1.2--1.3.sql aqo--1.3--1.4.sql aqo--1.4--1.5.sql
3738

3839
ifdefUSE_PGXS
3940
PG_CONFIG ?= pg_config

‎aqo--1.4--1.5.sql‎

Lines changed: 75 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,75 @@
1+
/* contrib/aqo/aqo--1.4--1.5.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
4+
\echo Use"ALTER EXTENSION aqo UPDATE TO '1.5'" to load this file. \quit
5+
6+
--
7+
-- Re-create the aqo_data table. Do so to keep the columns order.
8+
--
9+
DROPTABLEpublic.aqo_data CASCADE;
10+
CREATETABLEpublic.aqo_data (
11+
fspace_hashbigintNOT NULLREFERENCESpublic.aqo_queriesON DELETE CASCADE,
12+
fsspace_hashintNOT NULL,
13+
nfeaturesintNOT NULL,
14+
featuresdouble precision[][],
15+
targetsdouble precision[],
16+
oidsoid [] DEFAULTNULL,
17+
reliabilitydouble precision []
18+
);
19+
CREATEUNIQUE INDEXaqo_fss_access_idxONpublic.aqo_data (fspace_hash, fsspace_hash);
20+
21+
22+
--
23+
-- Remove rows from the AQO ML knowledge base, related to previously dropped
24+
-- tables of the database.
25+
--
26+
CREATE OR REPLACEFUNCTIONpublic.clean_aqo_data() RETURNS voidAS $$
27+
DECLARE
28+
aqo_data_row aqo_data%ROWTYPE;
29+
aqo_queries_row aqo_queries%ROWTYPE;
30+
aqo_query_texts_row aqo_query_texts%ROWTYPE;
31+
aqo_query_stat_row aqo_query_stat%ROWTYPE;
32+
oid_varoid;
33+
fspace_hash_varbigint;
34+
delete_rowboolean DEFAULT false;
35+
BEGIN
36+
FOR aqo_data_rowIN (SELECT*FROM aqo_data)
37+
LOOP
38+
delete_row= false;
39+
SELECTaqo_data_row.fspace_hash INTO fspace_hash_varFROM aqo_data;
40+
41+
IF (aqo_data_row.oidsIS NOT NULL) THEN
42+
FOREACH oid_varIN ARRAYaqo_data_row.oids
43+
LOOP
44+
IF NOT EXISTS (SELECT relnameFROM pg_classWHEREoid= oid_var) THEN
45+
delete_row= true;
46+
END IF;
47+
END LOOP;
48+
END IF;
49+
50+
FOR aqo_queries_rowIN (SELECT*FROMpublic.aqo_queries)
51+
LOOP
52+
IF (delete_row= trueAND fspace_hash_var<>0AND
53+
fspace_hash_var=aqo_queries_row.fspace_hashAND
54+
aqo_queries_row.fspace_hash=aqo_queries_row.query_hash) THEN
55+
DELETEFROM aqo_dataWHERE aqo_data= aqo_data_row;
56+
DELETEFROM aqo_queriesWHERE aqo_queries= aqo_queries_row;
57+
58+
FOR aqo_query_texts_rowIN (SELECT*FROM aqo_query_texts)
59+
LOOP
60+
DELETEFROM aqo_query_texts
61+
WHEREaqo_query_texts_row.query_hash= fspace_hash_varAND
62+
aqo_query_texts= aqo_query_texts_row;
63+
END LOOP;
64+
65+
FOR aqo_query_stat_rowIN (SELECT*FROM aqo_query_stat)
66+
LOOP
67+
DELETEFROM aqo_query_stat
68+
WHEREaqo_query_stat_row.query_hash= fspace_hash_varAND
69+
aqo_query_stat= aqo_query_stat_row;
70+
END LOOP;
71+
END IF;
72+
END LOOP;
73+
END LOOP;
74+
END;
75+
$$ LANGUAGE plpgsql;

‎aqo.control‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# AQO extension
22
comment = 'machine learning for cardinality estimation in optimizer'
3-
default_version = '1.4'
3+
default_version = '1.5'
44
module_pathname = '$libdir/aqo'
55
relocatable = false

‎aqo.h‎

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -280,12 +280,12 @@ extern bool find_query(uint64 qhash, QueryContextData *ctx);
280280
externboolupdate_query(uint64qhash,uint64fhash,
281281
boollearn_aqo,booluse_aqo,boolauto_tuning);
282282
externbooladd_query_text(uint64query_hash,constchar*query_string);
283-
externboolload_fss_ext(uint64fs,intfss,OkNNrdata*data,List**relnames,
283+
externboolload_fss_ext(uint64fs,intfss,OkNNrdata*data,List**reloids,
284284
boolisSafe);
285-
externboolload_fss(uint64fs,intfss,OkNNrdata*data,List**relnames);
285+
externboolload_fss(uint64fs,intfss,OkNNrdata*data,List**reloids);
286286
externboolupdate_fss_ext(uint64fs,intfss,OkNNrdata*data,
287-
List*relnames,boolisTimedOut);
288-
externboolupdate_fss(uint64fs,intfss,OkNNrdata*data,List*relnames);
287+
List*reloids,boolisTimedOut);
288+
externboolupdate_fss(uint64fs,intfss,OkNNrdata*data,List*reloids);
289289
QueryStat*get_aqo_stat(uint64query_hash);
290290
voidupdate_aqo_stat(uint64query_hash,QueryStat*stat);
291291
externboolmy_index_insert(RelationindexRelation,Datum*values,bool*isnull,
@@ -305,8 +305,8 @@ extern void print_into_explain(PlannedStmt *plannedstmt, IntoClause *into,
305305
externvoidprint_node_explain(ExplainState*es,PlanState*ps,Plan*plan);
306306

307307
/* Cardinality estimation */
308-
doublepredict_for_relation(List*restrict_clauses,List*selectivities,
309-
List*relnames,int*fss);
308+
externdoublepredict_for_relation(List*restrict_clauses,List*selectivities,
309+
List*relsigns,int*fss);
310310

311311
/* Query execution statistics collecting hooks */
312312
voidaqo_ExecutorStart(QueryDesc*queryDesc,inteflags);

‎cardinality_estimation.c‎

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -26,7 +26,7 @@
2626
#ifdefAQO_DEBUG_PRINT
2727
staticvoid
2828
predict_debug_output(List*clauses,List*selectivities,
29-
List*relnames,intfss,doubleresult)
29+
List*reloids,intfss,doubleresult)
3030
{
3131
StringInfoDatadebug_str;
3232
ListCell*lc;
@@ -42,8 +42,8 @@ predict_debug_output(List *clauses, List *selectivities,
4242
appendStringInfo(&debug_str,"%lf ",*s);
4343
}
4444

45-
appendStringInfoString(&debug_str,"},relnames: { ");
46-
foreach(lc,relnames)
45+
appendStringInfoString(&debug_str,"},reloids: { ");
46+
foreach(lc,reloids)
4747
{
4848
Value*relname=lfirst_node(String,lc);
4949
appendStringInfo(&debug_str,"%s ",valStr(relname));
@@ -59,22 +59,22 @@ predict_debug_output(List *clauses, List *selectivities,
5959
* General method for prediction the cardinality of given relation.
6060
*/
6161
double
62-
predict_for_relation(List*clauses,List*selectivities,
63-
List*relnames,int*fss)
62+
predict_for_relation(List*clauses,List*selectivities,List*relsigns,
63+
int*fss)
6464
{
6565
double*features;
6666
doubleresult;
6767
inti;
6868
OkNNrdatadata;
6969

70-
if (relnames==NIL)
70+
if (relsigns==NIL)
7171
/*
7272
* Don't make prediction for query plans without any underlying plane
7373
* tables. Use return value -4 for debug purposes.
7474
*/
7575
return-4.;
7676

77-
*fss=get_fss_for_object(relnames,clauses,selectivities,
77+
*fss=get_fss_for_object(relsigns,clauses,selectivities,
7878
&data.cols,&features);
7979

8080
if (data.cols>0)
@@ -94,7 +94,7 @@ predict_for_relation(List *clauses, List *selectivities,
9494
result=-1;
9595
}
9696
#ifdefAQO_DEBUG_PRINT
97-
predict_debug_output(clauses,selectivities,relnames,*fss,result);
97+
predict_debug_output(clauses,selectivities,relsigns,*fss,result);
9898
#endif
9999
pfree(features);
100100
if (data.cols>0)

‎cardinality_hooks.c‎

Lines changed: 37 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -138,12 +138,12 @@ default_estimate_num_groups(PlannerInfo *root, List *groupExprs,
138138
void
139139
aqo_set_baserel_rows_estimate(PlannerInfo*root,RelOptInfo*rel)
140140
{
141-
doublepredicted;
142-
RangeTblEntry*rte;
143-
List*relnames=NIL;
144-
List*selectivities=NULL;
145-
List*clauses;
146-
intfss=0;
141+
doublepredicted;
142+
RangeTblEntry*rte;
143+
RelSortOutrels= {NIL,NIL};
144+
List*selectivities=NULL;
145+
List*clauses;
146+
intfss=0;
147147

148148
if (IsQueryDisabled())
149149
/* Fast path. */
@@ -166,16 +166,18 @@ aqo_set_baserel_rows_estimate(PlannerInfo *root, RelOptInfo *rel)
166166
{
167167
/* Predict for a plane table. */
168168
Assert(rte->eref&&rte->eref->aliasname);
169-
relnames=list_make1(makeString(pstrdup(rte->eref->aliasname)));
169+
get_list_of_relids(root,rel->relids,&rels);
170170
}
171171

172172
clauses=aqo_get_clauses(root,rel->baserestrictinfo);
173-
predicted=predict_for_relation(clauses,selectivities,relnames,&fss);
173+
predicted=predict_for_relation(clauses,selectivities,rels.signatures,
174+
&fss);
174175
rel->fss_hash=fss;
175176

177+
list_free(rels.hrels);
178+
list_free(rels.signatures);
176179
list_free_deep(selectivities);
177180
list_free(clauses);
178-
list_free(relnames);
179181

180182
if (predicted >=0)
181183
{
@@ -212,7 +214,7 @@ aqo_get_parameterized_baserel_size(PlannerInfo *root,
212214
{
213215
doublepredicted;
214216
RangeTblEntry*rte=NULL;
215-
List*relnames=NIL;
217+
RelSortOutrels= {NIL,NIL};
216218
List*allclauses=NULL;
217219
List*selectivities=NULL;
218220
ListCell*l;
@@ -268,10 +270,12 @@ aqo_get_parameterized_baserel_size(PlannerInfo *root,
268270
{
269271
/* Predict for a plane table. */
270272
Assert(rte->eref&&rte->eref->aliasname);
271-
relnames=list_make1(makeString(pstrdup(rte->eref->aliasname)));
273+
get_list_of_relids(root,rel->relids,&rels);
272274
}
273275

274-
predicted=predict_for_relation(allclauses,selectivities,relnames,&fss);
276+
predicted=predict_for_relation(allclauses,selectivities,rels.signatures,&fss);
277+
list_free(rels.hrels);
278+
list_free(rels.signatures);
275279

276280
predicted_ppi_rows=predicted;
277281
fss_ppi_hash=fss;
@@ -296,7 +300,7 @@ aqo_set_joinrel_size_estimates(PlannerInfo *root, RelOptInfo *rel,
296300
List*restrictlist)
297301
{
298302
doublepredicted;
299-
List*relnames;
303+
RelSortOutrels= {NIL,NIL};
300304
List*outer_clauses;
301305
List*inner_clauses;
302306
List*allclauses;
@@ -322,7 +326,7 @@ aqo_set_joinrel_size_estimates(PlannerInfo *root, RelOptInfo *rel,
322326
gotodefault_estimator;
323327
}
324328

325-
relnames=get_relnames(root,rel->relids);
329+
get_list_of_relids(root,rel->relids,&rels);
326330
outer_clauses=get_path_clauses(outer_rel->cheapest_total_path,root,
327331
&outer_selectivities);
328332
inner_clauses=get_path_clauses(inner_rel->cheapest_total_path,root,
@@ -333,7 +337,11 @@ aqo_set_joinrel_size_estimates(PlannerInfo *root, RelOptInfo *rel,
333337
list_concat(outer_selectivities,
334338
inner_selectivities));
335339

336-
predicted=predict_for_relation(allclauses,selectivities,relnames,&fss);
340+
predicted=predict_for_relation(allclauses,selectivities,rels.signatures,
341+
&fss);
342+
list_free(rels.hrels);
343+
list_free(rels.signatures);
344+
337345
rel->fss_hash=fss;
338346

339347
if (predicted >=0)
@@ -364,7 +372,7 @@ aqo_get_parameterized_joinrel_size(PlannerInfo *root,
364372
List*clauses)
365373
{
366374
doublepredicted;
367-
List*relnames;
375+
RelSortOutrels= {NIL,NIL};
368376
List*outer_clauses;
369377
List*inner_clauses;
370378
List*allclauses;
@@ -390,7 +398,7 @@ aqo_get_parameterized_joinrel_size(PlannerInfo *root,
390398
gotodefault_estimator;
391399
}
392400

393-
relnames=get_relnames(root,rel->relids);
401+
get_list_of_relids(root,rel->relids,&rels);
394402
outer_clauses=get_path_clauses(outer_path,root,&outer_selectivities);
395403
inner_clauses=get_path_clauses(inner_path,root,&inner_selectivities);
396404
allclauses=list_concat(aqo_get_clauses(root,clauses),
@@ -399,7 +407,10 @@ aqo_get_parameterized_joinrel_size(PlannerInfo *root,
399407
list_concat(outer_selectivities,
400408
inner_selectivities));
401409

402-
predicted=predict_for_relation(allclauses,selectivities,relnames,&fss);
410+
predicted=predict_for_relation(allclauses,selectivities,rels.signatures,
411+
&fss);
412+
list_free(rels.hrels);
413+
list_free(rels.signatures);
403414

404415
predicted_ppi_rows=predicted;
405416
fss_ppi_hash=fss;
@@ -426,13 +437,16 @@ predict_num_groups(PlannerInfo *root, Path *subpath, List *group_exprs,
426437
child_fss=subpath->parent->fss_hash;
427438
else
428439
{
429-
List*relnames;
430-
List*clauses;
431-
List*selectivities=NIL;
440+
RelSortOutrels= {NIL,NIL};
441+
List*clauses;
442+
List*selectivities=NIL;
432443

433-
relnames=get_relnames(root,subpath->parent->relids);
444+
get_list_of_relids(root,subpath->parent->relids,&rels);
434445
clauses=get_path_clauses(subpath,root,&selectivities);
435-
(void)predict_for_relation(clauses,selectivities,relnames,&child_fss);
446+
(void)predict_for_relation(clauses,selectivities,rels.signatures,
447+
&child_fss);
448+
list_free(rels.hrels);
449+
list_free(rels.signatures);
436450
}
437451

438452
*fss=get_grouped_exprs_hash(child_fss,group_exprs);

0 commit comments

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