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

Commite4cef06

Browse files
committed
Replace aqo_queries table with a file storage.
1 parent0eee1f8 commite4cef06

31 files changed

+390
-526
lines changed

‎README.md‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -212,7 +212,7 @@ execution of such query type. Disabling of AQO usage is reasonable for that
212212
cases in which query execution time increases after applying AQO. It happens
213213
sometimes because of cost models incompleteness.
214214

215-
`Fspace_hash` setting is for extra advanced AQO tuning. It may be changed manually
215+
`fs` setting is for extra advanced AQO tuning. It may be changed manually
216216
to optimize a number of queries using the same model. It may decrease the
217217
amount of memory for models and even the query execution time, but also it
218218
may cause the bad AQO's behavior, so please use it only if you know exactly
@@ -230,7 +230,7 @@ ignored.
230230

231231
If`aqo.mode` is`'learn'`, then the normalized query hash appends to aqo_queries
232232
with the default settings`learn_aqo=true`,`use_aqo=true`,`auto_tuning=false`, and
233-
`fspace_hash =query_hash` which means that AQO uses separate machine learning
233+
`fs =queryid` which means that AQO uses separate machine learning
234234
model for this query type optimization. After that the query is processed as if
235235
it already was in aqo_queries.
236236

‎aqo--1.4--1.5.sql‎

Lines changed: 21 additions & 45 deletions
Original file line numberDiff line numberDiff line change
@@ -21,11 +21,11 @@ DROP TABLE public.aqo_query_texts CASCADE;
2121
DROPTABLEpublic.aqo_query_stat CASCADE;
2222

2323
CREATEFUNCTIONaqo_queries (
24-
OUT queryidbigint,
25-
OUTfspace_hashbigint,
26-
OUT learn_aqoboolean,
27-
OUT use_aqoboolean,
28-
OUT auto_tuningboolean
24+
OUT queryidbigint,
25+
OUTfsbigint,
26+
OUT learn_aqoboolean,
27+
OUT use_aqoboolean,
28+
OUT auto_tuningboolean
2929
)
3030
RETURNS SETOF record
3131
AS'MODULE_PATHNAME','aqo_queries'
@@ -91,13 +91,6 @@ CREATE FUNCTION aqo_stat_remove(fs bigint) RETURNS bool
9191
AS'MODULE_PATHNAME'
9292
LANGUAGE C STRICT PARALLEL SAFE;
9393

94-
-- INSERT INTO aqo_queries VALUES (0, false, false, 0, false);
95-
-- a virtual query for COMMON feature space
96-
97-
--CREATE TRIGGER aqo_queries_invalidate AFTER UPDATE OR DELETE OR TRUNCATE
98-
--ON aqo_queries FOR EACH STATEMENT
99-
--EXECUTE PROCEDURE invalidate_deactivated_queries_cache();
100-
10194
--
10295
-- Show execution time of queries, for which AQO has statistics.
10396
-- controlled - show stat on executions where AQO was used for cardinality
@@ -118,7 +111,7 @@ IF (controlled) THEN
118111
FROM (
119112
SELECT
120113
aq.queryidAS queryid,
121-
aq.fspace_hashAS fs_hash,
114+
aq.fsAS fs_hash,
122115
execution_time_with_aqo[array_length(execution_time_with_aqo,1)]AS exectime,
123116
executions_with_aqoAS execs
124117
FROM aqo_queries aqJOIN aqo_query_stat aqs
@@ -137,7 +130,7 @@ ELSE
137130
FROM (
138131
SELECT
139132
aq.queryidAS queryid,
140-
aq.fspace_hashAS fs_hash,
133+
aq.fsAS fs_hash,
141134
(SELECTAVG(t)FROM unnest(execution_time_without_aqo) t)AS exectime,
142135
executions_without_aqoAS execs
143136
FROM aqo_queries aqJOIN aqo_query_stat aqs
@@ -165,7 +158,7 @@ BEGIN
165158
raise EXCEPTION'[AQO] Cannot remove basic class %.', queryid_rm;
166159
END IF;
167160

168-
SELECTfspace_hashFROM aqo_queriesWHERE (queryid= queryid_rm) INTO lfs;
161+
SELECTfsFROM aqo_queriesWHERE (queryid= queryid_rm) INTO lfs;
169162

170163
IF (lfs ISNULL) THEN
171164
raise WARNING'[AQO] Nothing to remove for the class %.', queryid_rm;
@@ -258,7 +251,7 @@ IF (controlled) THEN
258251
FROM (
259252
SELECT
260253
aq.queryidAS query_id,
261-
aq.fspace_hashAS fs_hash,
254+
aq.fsAS fs_hash,
262255
cardinality_error_with_aqo[array_length(cardinality_error_with_aqo,1)]AS cerror,
263256
executions_with_aqoAS execs
264257
FROM aqo_queries aqJOIN aqo_query_stat aqs
@@ -274,7 +267,7 @@ ELSE
274267
FROM (
275268
SELECT
276269
aq.queryidAS query_id,
277-
aq.fspace_hashAS fs_hash,
270+
aq.fsAS fs_hash,
278271
(SELECTAVG(t)FROM unnest(cardinality_error_without_aqo) t)AS cerror,
279272
executions_without_aqoAS execs
280273
FROM aqo_queries aqJOIN aqo_query_stat aqs
@@ -300,15 +293,15 @@ CREATE OR REPLACE FUNCTION aqo_reset_query(queryid_res bigint)
300293
RETURNSintegerAS $$
301294
DECLARE
302295
numinteger;
303-
fsbigint;
296+
lfsbigint;
304297
BEGIN
305298
IF (queryid_res=0) THEN
306299
raise WARNING'[AQO] Reset common feature space.'
307300
END IF;
308301

309-
SELECTfspace_hashFROM aqo_queriesWHERE queryid= queryid_res INTOfs;
310-
SELECTcount(*)FROM aqo_dataWHEREfspace_hash=fs INTO num;
311-
DELETEFROM aqo_dataWHEREfspace_hash=fs;
302+
SELECTfsFROM aqo_queriesWHERE queryid= queryid_res INTOlfs;
303+
SELECTcount(*)FROM aqo_dataWHEREfs=lfs INTO num;
304+
DELETEFROM aqo_dataWHEREfs=lfs;
312305
RETURN num;
313306
END;
314307
$$ LANGUAGE plpgsql;
@@ -329,7 +322,7 @@ RETURNS TABLE (
329322
"err_aqo"TEXT,
330323
"iters_aqo"BIGINT
331324
)AS $$
332-
SELECTlearn_aqo,use_aqo,auto_tuning,fspace_hash,
325+
SELECTlearn_aqo,use_aqo,auto_tuning,fs,
333326
to_char(execution_time_without_aqo[n4],'9.99EEEE'),
334327
to_char(cardinality_error_without_aqo[n2],'9.99EEEE'),
335328
executions_without_aqo,
@@ -350,35 +343,18 @@ WHERE (aqs.queryid = aq.queryid) AND
350343
aqs.queryid= $1;
351344
$$ LANGUAGE SQL;
352345

353-
/* CREATE FUNCTION aqo_enable_query(hash bigint)
354-
RETURNS VOID AS $$
355-
UPDATE aqo_queries SET
356-
learn_aqo = 'true',
357-
use_aqo = 'true'
358-
WHERE queryid = $1;
359-
$$ LANGUAGE SQL;
360-
361-
CREATE FUNCTION aqo_disable_query(hash bigint)
362-
RETURNS VOID AS $$
363-
UPDATE aqo_queries SET
364-
learn_aqo = 'false',
365-
use_aqo = 'false',
366-
auto_tuning = 'false'
367-
WHERE queryid = $1;
368-
$$ LANGUAGE SQL;
369-
*/
370-
371-
CREATEFUNCTIONaqo_enable_query(hashbigint)
346+
CREATEFUNCTIONaqo_enable_query(queryidbigint)
372347
RETURNS void
373348
AS'MODULE_PATHNAME','aqo_enable_query'
374349
LANGUAGE C STRICT VOLATILE;
375350

376-
CREATEFUNCTIONaqo_disable_query(hashbigint)
351+
CREATEFUNCTIONaqo_disable_query(queryidbigint)
377352
RETURNS void
378353
AS'MODULE_PATHNAME','aqo_enable_query'
379354
LANGUAGE C STRICT VOLATILE;
380355

381-
CREATEFUNCTIONaqo_queries_update(learn_aqoint, use_aqoint, auto_tuningint)
382-
RETURNS void
356+
CREATEFUNCTIONaqo_queries_update(queryidbigint, fsbigint, learn_aqo bool,
357+
use_aqo bool, auto_tuning bool)
358+
RETURNS bool
383359
AS'MODULE_PATHNAME','aqo_queries_update'
384-
LANGUAGE CSTRICTVOLATILE;
360+
LANGUAGE C VOLATILE;

‎aqo.c‎

Lines changed: 12 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -272,20 +272,6 @@ _PG_init(void)
272272
RequestAddinShmemSpace(aqo_memsize());
273273
}
274274

275-
PG_FUNCTION_INFO_V1(invalidate_deactivated_queries_cache);
276-
277-
/*
278-
* Clears the cache of deactivated queries if the user changed aqo_queries
279-
* manually.
280-
*/
281-
Datum
282-
invalidate_deactivated_queries_cache(PG_FUNCTION_ARGS)
283-
{
284-
fini_deactivated_queries_storage();
285-
init_deactivated_queries_storage();
286-
PG_RETURN_POINTER(NULL);
287-
}
288-
289275
/*
290276
* Return AQO schema's Oid or InvalidOid if that's not possible.
291277
*/
@@ -363,3 +349,15 @@ IsQueryDisabled(void)
363349

364350
return false;
365351
}
352+
353+
PG_FUNCTION_INFO_V1(invalidate_deactivated_queries_cache);
354+
355+
/*
356+
* Clears the cache of deactivated queries if the user changed aqo_queries
357+
* manually.
358+
*/
359+
Datum
360+
invalidate_deactivated_queries_cache(PG_FUNCTION_ARGS)
361+
{
362+
PG_RETURN_POINTER(NULL);
363+
}

‎aqo.h‎

Lines changed: 4 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -144,7 +144,7 @@
144144
#include"utils/snapmgr.h"
145145

146146
#include"machine_learning.h"
147-
#include"storage.h"
147+
//#include "storage.h"
148148

149149
/* Check PostgreSQL version (9.6.0 contains important changes in planner) */
150150
#ifPG_VERSION_NUM<90600
@@ -200,6 +200,8 @@ typedef struct QueryContextData
200200
doubleplanning_time;
201201
}QueryContextData;
202202

203+
structStatEntry;
204+
203205
externdoublepredicted_ppi_rows;
204206
externdoublefss_ppi_hash;
205207

@@ -251,18 +253,10 @@ int get_clause_hash(Expr *clause, int nargs, int *args_hash, int *eclass_hash);
251253

252254

253255
/* Storage interaction */
254-
externboolfile_find_query(uint64queryid);
255256
externboolload_fss_ext(uint64fs,intfss,OkNNrdata*data,List**reloids,
256257
boolisSafe);
257258
externboolupdate_fss_ext(uint64fs,intfss,OkNNrdata*data,
258259
List*reloids,boolisTimedOut);
259-
externboolmy_index_insert(RelationindexRelation,Datum*values,bool*isnull,
260-
ItemPointerheap_t_ctid,RelationheapRelation,
261-
IndexUniqueCheckcheckUnique);
262-
voidinit_deactivated_queries_storage(void);
263-
voidfini_deactivated_queries_storage(void);
264-
externboolquery_is_deactivated(uint64query_hash);
265-
externvoidadd_deactivated_query(uint64query_hash);
266260

267261
/* Query preprocessing hooks */
268262
externvoidprint_into_explain(PlannedStmt*plannedstmt,IntoClause*into,
@@ -283,7 +277,7 @@ void aqo_ExecutorRun(QueryDesc *queryDesc, ScanDirection direction,
283277
voidaqo_ExecutorEnd(QueryDesc*queryDesc);
284278

285279
/* Automatic query tuning */
286-
externvoidautomatical_query_tuning(uint64query_hash,StatEntry*stat);
280+
externvoidautomatical_query_tuning(uint64query_hash,structStatEntry*stat);
287281

288282
/* Utilities */
289283
externintint64_compare(constvoid*a,constvoid*b);

‎aqo_shared.c‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -200,6 +200,7 @@ aqo_init_shmem(void)
200200
aqo_state->data_dsa_handler=DSM_HANDLE_INVALID;
201201
aqo_state->data_trancheid=LWLockNewTrancheId();
202202
aqo_state->data_changed= false;
203+
aqo_state->queries_changed= false;
203204

204205
LWLockInitialize(&aqo_state->stat_lock,LWLockNewTrancheId());
205206
LWLockInitialize(&aqo_state->qtexts_lock,LWLockNewTrancheId());

‎aqo_shared.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -42,6 +42,7 @@ typedef struct AQOSharedState
4242
booldata_changed;
4343

4444
LWLockqueries_lock;/* lock for access to queries storage */
45+
boolqueries_changed;
4546
}AQOSharedState;
4647

4748

‎auto_tuning.c‎

Lines changed: 12 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -145,13 +145,13 @@ is_in_infinite_loop_cq(double *elems, int nelems)
145145
* this query to false.
146146
*/
147147
void
148-
automatical_query_tuning(uint64qhash,StatEntry*stat)
148+
automatical_query_tuning(uint64queryid,StatEntry*stat)
149149
{
150-
doubleunstability=auto_tuning_exploration;
151-
doublet_aqo,
152-
t_not_aqo;
153-
doublep_use=-1;
154-
int64num_iterations;
150+
doubleunstability=auto_tuning_exploration;
151+
doublet_aqo,
152+
t_not_aqo;
153+
doublep_use=-1;
154+
int64num_iterations;
155155

156156
num_iterations=stat->execs_with_aqo+stat->execs_without_aqo;
157157
query_context.learn_aqo= true;
@@ -195,16 +195,15 @@ automatical_query_tuning(uint64 qhash, StatEntry *stat)
195195
* If our decision is using AQO for this query class, then learn on new
196196
* queries of this type. Otherwise, turn off.
197197
*/
198-
query_context.use_aqo= (random() / ((double)MAX_RANDOM_VALUE+1))<p_use;
198+
query_context.use_aqo=
199+
(random() / ((double)MAX_RANDOM_VALUE+1))<p_use;
199200
query_context.learn_aqo=query_context.use_aqo;
200201
}
201202

202203
if (num_iterations <=auto_tuning_max_iterations||p_use>0.5)
203-
aqo_queries_store(qhash,
204-
query_context.fspace_hash,
205-
query_context.learn_aqo,
206-
query_context.use_aqo,
207-
true);
204+
aqo_queries_store(queryid,query_context.fspace_hash,
205+
query_context.learn_aqo,query_context.use_aqo, true);
208206
else
209-
aqo_queries_store(qhash,query_context.fspace_hash, false, false, false);
207+
aqo_queries_store(queryid,
208+
query_context.fspace_hash, false, false, false);
210209
}

‎expected/aqo_controlled.out‎

Lines changed: 18 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -107,10 +107,13 @@ SELECT count(*) FROM tmp1;
107107

108108
DROP TABLE tmp1;
109109
SET aqo.mode = 'controlled';
110-
SELECT aqo_queries_update(1, 0, 0);
111-
aqo_queries_update
112-
--------------------
113-
110+
SELECT count(*) FROM
111+
(SELECT queryid AS id FROM aqo_queries) AS q1,
112+
LATERAL aqo_queries_update(q1.id, NULL, true, false, false)
113+
; -- learn = true, use = false, tuning = false
114+
count
115+
-------
116+
12
114117
(1 row)
115118

116119
EXPLAIN (COSTS FALSE)
@@ -194,10 +197,13 @@ WHERE t1.a = t2.b AND t2.a = t3.b;
194197
-> Seq Scan on aqo_test1 t3
195198
(9 rows)
196199

197-
SELECT aqo_queries_update(2, 1, 2);
198-
aqo_queries_update
199-
--------------------
200-
200+
SELECT count(*) FROM
201+
(SELECT queryid AS id FROM aqo_queries) AS q1,
202+
LATERAL aqo_queries_update(q1.id, NULL, NULL, true, NULL)
203+
; -- set use = true
204+
count
205+
-------
206+
12
201207
(1 row)
202208

203209
EXPLAIN (COSTS FALSE)
@@ -306,10 +312,10 @@ DROP TABLE aqo_test1;
306312
DROP INDEX aqo_test2_idx_a;
307313
DROP TABLE aqo_test2;
308314
-- XXX: extension dropping doesn't clear file storage. Do it manually.
309-
SELECT aqo_reset();
310-
aqo_reset
311-
-----------
312-
61
315+
SELECT1 FROMaqo_reset();
316+
?column?
317+
----------
318+
1
313319
(1 row)
314320

315321
DROP EXTENSION aqo;

0 commit comments

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