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

Commitf80695d

Browse files
committed
Rewrite aqo_execution_time in C.
Now, I guess, the extension is truly relocatable. We should check it by sometests on extension moving.
1 parentbe06f13 commitf80695d

File tree

6 files changed

+158
-122
lines changed

6 files changed

+158
-122
lines changed

‎aqo--1.4--1.5.sql‎

Lines changed: 39 additions & 112 deletions
Original file line numberDiff line numberDiff line change
@@ -75,6 +75,43 @@ CREATE VIEW aqo_queries AS SELECT * FROM aqo_queries();
7575

7676
/* UI functions*/
7777

78+
79+
CREATEFUNCTIONaqo_enable_query(queryidbigint)
80+
RETURNS void
81+
AS'MODULE_PATHNAME','aqo_enable_query'
82+
LANGUAGE C STRICT VOLATILE;
83+
84+
CREATEFUNCTIONaqo_disable_query(queryidbigint)
85+
RETURNS void
86+
AS'MODULE_PATHNAME','aqo_enable_query'
87+
LANGUAGE C STRICT VOLATILE;
88+
89+
CREATEFUNCTIONaqo_queries_update(
90+
queryidbigint, fsbigint, learn_aqo bool, use_aqo bool, auto_tuning bool)
91+
RETURNS bool
92+
AS'MODULE_PATHNAME','aqo_queries_update'
93+
LANGUAGE C VOLATILE;
94+
95+
--
96+
-- Get cardinality error of queries the last time they were executed.
97+
-- IN:
98+
-- controlled - show queries executed under a control of AQO (true);
99+
-- executed without an AQO control, but AQO has a stat on the query (false).
100+
--
101+
-- OUT:
102+
-- num - sequental number. Smaller number corresponds to higher error.
103+
-- id - ID of a query.
104+
-- fshash - feature space. Usually equal to zero or ID.
105+
-- error - AQO error that calculated on plan nodes of the query.
106+
-- nexecs - number of executions of queries associated with this ID.
107+
--
108+
CREATE OR REPLACEFUNCTIONaqo_cardinality_error(controlledboolean)
109+
RETURNS TABLE(numbigint, idbigint, fshashbigint, error float, nexecsbigint)
110+
AS'MODULE_PATHNAME','aqo_cardinality_error'
111+
LANGUAGE C STRICT VOLATILE;
112+
COMMENT ON FUNCTION aqo_cardinality_error(boolean) IS
113+
'Get cardinality error of queries the last time they were executed. Order queries according to an error value.';
114+
78115
--
79116
-- Show execution time of queries, for which AQO has statistics.
80117
-- controlled - show stat on executions where AQO was used for cardinality
@@ -83,48 +120,8 @@ CREATE VIEW aqo_queries AS SELECT * FROM aqo_queries();
83120
--
84121
CREATE OR REPLACEFUNCTIONaqo_execution_time(controlledboolean)
85122
RETURNS TABLE(numbigint, idbigint, fshashbigint, exec_time float, nexecsbigint)
86-
AS $$
87-
BEGIN
88-
IF (controlled) THEN
89-
-- Show a query execution time made with AQO support for the planner
90-
-- cardinality estimations. Here we return result of last execution.
91-
RETURN QUERY
92-
SELECT
93-
row_number() OVER (ORDER BY (exectime, queryid, fs_hash)DESC)AS nn,
94-
queryid, fs_hash, exectime, execs
95-
FROM (
96-
SELECT
97-
aq.queryidAS queryid,
98-
aq.fsAS fs_hash,
99-
execution_time_with_aqo[array_length(execution_time_with_aqo,1)]AS exectime,
100-
executions_with_aqoAS execs
101-
FROM aqo_queries aqJOIN aqo_query_stat aqs
102-
ONaq.queryid=aqs.queryid
103-
WHERE TRUE= ANY (SELECT unnest(execution_time_with_aqo)IS NOT NULL)
104-
)AS q1
105-
ORDER BY nnASC;
106-
107-
ELSE
108-
-- Show a query execution time made without any AQO advise.
109-
-- Return an average value across all executions.
110-
RETURN QUERY
111-
SELECT
112-
row_number() OVER (ORDER BY (exectime, queryid, fs_hash)DESC)AS nn,
113-
queryid, fs_hash, exectime, execs
114-
FROM (
115-
SELECT
116-
aq.queryidAS queryid,
117-
aq.fsAS fs_hash,
118-
(SELECTAVG(t)FROM unnest(execution_time_without_aqo) t)AS exectime,
119-
executions_without_aqoAS execs
120-
FROM aqo_queries aqJOIN aqo_query_stat aqs
121-
ONaq.queryid=aqs.queryid
122-
WHERE TRUE= ANY (SELECT unnest(execution_time_without_aqo)IS NOT NULL)
123-
)AS q1
124-
ORDER BY (nn)ASC;
125-
END IF;
126-
END;
127-
$$ LANGUAGE plpgsql;
123+
AS'MODULE_PATHNAME','aqo_execution_time'
124+
LANGUAGE C STRICT VOLATILE;
128125
COMMENT ON FUNCTION aqo_execution_time(boolean) IS
129126
'Get execution time of queries. If controlled = true (AQO could advise cardinality estimations), show time of last execution attempt. Another case (AQO not used), return an average value of execution time across all known executions.';
130127

@@ -152,76 +149,6 @@ LANGUAGE C STRICT VOLATILE;
152149
COMMENT ON FUNCTION aqo_cleanup() IS
153150
'Remove unneeded rows from the AQO ML storage';
154151

155-
--
156-
-- Get cardinality error of queries the last time they were executed.
157-
-- IN:
158-
-- controlled - show queries executed under a control of AQO (true);
159-
-- executed without an AQO control, but AQO has a stat on the query (false).
160-
--
161-
-- OUT:
162-
-- num - sequental number. Smaller number corresponds to higher error.
163-
-- id - ID of a query.
164-
-- fshash - feature space. Usually equal to zero or ID.
165-
-- error - AQO error that calculated on plan nodes of the query.
166-
-- nexecs - number of executions of queries associated with this ID.
167-
--
168-
CREATE OR REPLACEFUNCTIONaqo_cardinality_error(controlledboolean)
169-
RETURNS TABLE(numbigint, idbigint, fshashbigint, error float, nexecsbigint)
170-
AS'MODULE_PATHNAME','aqo_cardinality_error'
171-
LANGUAGE C STRICT VOLATILE;
172-
COMMENT ON FUNCTION aqo_cardinality_error(boolean) IS
173-
'Get cardinality error of queries the last time they were executed. Order queries according to an error value.';
174-
175-
CREATEFUNCTIONaqo_status(hashbigint)
176-
RETURNS TABLE (
177-
"learn"BOOL,
178-
"use aqo"BOOL,
179-
"auto tune"BOOL,
180-
"fspace hash"bigINT,
181-
"t_naqo"TEXT,
182-
"err_naqo"TEXT,
183-
"iters"BIGINT,
184-
"t_aqo"TEXT,
185-
"err_aqo"TEXT,
186-
"iters_aqo"BIGINT
187-
)AS $$
188-
SELECTlearn_aqo,use_aqo,auto_tuning,fs,
189-
to_char(execution_time_without_aqo[n4],'9.99EEEE'),
190-
to_char(cardinality_error_without_aqo[n2],'9.99EEEE'),
191-
executions_without_aqo,
192-
to_char(execution_time_with_aqo[n3],'9.99EEEE'),
193-
to_char(cardinality_error_with_aqo[n1],'9.99EEEE'),
194-
executions_with_aqo
195-
FROM aqo_queries aq, aqo_query_stat aqs,
196-
(SELECT array_length(n1,1)AS n1, array_length(n2,1)AS n2,
197-
array_length(n3,1)AS n3, array_length(n4,1)AS n4
198-
FROM
199-
(SELECT cardinality_error_with_aqoAS n1,
200-
cardinality_error_without_aqoAS n2,
201-
execution_time_with_aqoAS n3,
202-
execution_time_without_aqoAS n4
203-
FROM aqo_query_stat aqsWHERE
204-
aqs.queryid= $1)AS al)AS q
205-
WHERE (aqs.queryid=aq.queryid)AND
206-
aqs.queryid= $1;
207-
$$ LANGUAGE SQL;
208-
209-
CREATEFUNCTIONaqo_enable_query(queryidbigint)
210-
RETURNS void
211-
AS'MODULE_PATHNAME','aqo_enable_query'
212-
LANGUAGE C STRICT VOLATILE;
213-
214-
CREATEFUNCTIONaqo_disable_query(queryidbigint)
215-
RETURNS void
216-
AS'MODULE_PATHNAME','aqo_enable_query'
217-
LANGUAGE C STRICT VOLATILE;
218-
219-
CREATEFUNCTIONaqo_queries_update(queryidbigint, fsbigint, learn_aqo bool,
220-
use_aqo bool, auto_tuning bool)
221-
RETURNS bool
222-
AS'MODULE_PATHNAME','aqo_queries_update'
223-
LANGUAGE C VOLATILE;
224-
225152
--
226153
-- Remove all records in the AQO storage.
227154
-- Return number of rows removed.

‎expected/schema.out‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -25,14 +25,16 @@ SELECT * FROM test;
2525
-- Check AQO service relations state after some manipulations
2626
-- Exclude fields with hash values from the queries. Hash is depend on
2727
-- nodefuncs code which is highly PostgreSQL version specific.
28-
SELECT query_text FROM aqo_query_texts;
28+
SELECT query_text FROM aqo_query_texts
29+
ORDER BY (md5(query_text)) DESC;
2930
query_text
3031
---------------------------------------
3132
COMMON feature space (do not delete!)
3233
SELECT * FROM test;
3334
(2 rows)
3435

35-
SELECT learn_aqo, use_aqo, auto_tuning FROM aqo_queries;
36+
SELECT learn_aqo, use_aqo, auto_tuning FROM aqo_queries
37+
ORDER BY (learn_aqo, use_aqo, auto_tuning);
3638
learn_aqo | use_aqo | auto_tuning
3739
-----------+---------+-------------
3840
f | f | f

‎expected/top_queries.out‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -63,17 +63,17 @@ SELECT count(*) FROM (SELECT x, y FROM t2 GROUP BY GROUPING SETS ((x,y), (x), (y
6363
31
6464
(1 row)
6565

66-
SELECTnum,to_char(error, '9.99EEEE') FROM aqo_cardinality_error(false) AS te
66+
SELECT to_char(error, '9.99EEEE') FROM aqo_cardinality_error(false) AS te
6767
WHERE te.fshash = (
6868
SELECT fs FROM aqo_queries
6969
WHERE aqo_queries.queryid = (
7070
SELECT aqo_query_texts.queryid FROM aqo_query_texts
7171
WHERE query_text = 'SELECT count(*) FROM (SELECT x, y FROM t2 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1;'
7272
)
7373
);
74-
num | to_char
75-
-----+-----------
76-
1 |1.94e+00
74+
to_char
75+
-----------
76+
1.94e+00
7777
(1 row)
7878

7979
-- Should return zero

‎sql/schema.sql‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,8 @@ SELECT * FROM test;
2121
-- Check AQO service relations state after some manipulations
2222
-- Exclude fields with hash values from the queries. Hash is depend on
2323
-- nodefuncs code which is highly PostgreSQL version specific.
24-
SELECT query_textFROM aqo_query_texts;
25-
SELECT learn_aqo, use_aqo, auto_tuningFROM aqo_queries;
24+
SELECT query_textFROM aqo_query_texts
25+
ORDER BY (md5(query_text))DESC;
26+
SELECT learn_aqo, use_aqo, auto_tuningFROM aqo_queries
27+
ORDER BY (learn_aqo, use_aqo, auto_tuning);
2628
DROPSCHEMA IF EXISTS test1 CASCADE;

‎sql/top_queries.sql‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -33,7 +33,7 @@ CREATE TABLE t2 AS SELECT mod(gs,10) AS x, mod(gs+1,10) AS y
3333
SELECTcount(*)FROM (SELECT x, yFROM t1GROUP BY GROUPING SETS ((x,y), (x), (y), ()))AS q1;
3434
SELECTcount(*)FROM (SELECT x, yFROM t2GROUP BY GROUPING SETS ((x,y), (x), (y), ()))AS q1;
3535

36-
SELECTnum,to_char(error,'9.99EEEE')FROM aqo_cardinality_error(false)AS te
36+
SELECT to_char(error,'9.99EEEE')FROM aqo_cardinality_error(false)AS te
3737
WHEREte.fshash= (
3838
SELECT fsFROM aqo_queries
3939
WHEREaqo_queries.queryid= (

‎storage.c‎

Lines changed: 106 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -103,6 +103,7 @@ PG_FUNCTION_INFO_V1(aqo_reset);
103103
PG_FUNCTION_INFO_V1(aqo_cleanup);
104104
PG_FUNCTION_INFO_V1(aqo_drop_class);
105105
PG_FUNCTION_INFO_V1(aqo_cardinality_error);
106+
PG_FUNCTION_INFO_V1(aqo_execution_time);
106107

107108

108109
bool
@@ -2282,7 +2283,7 @@ aqo_cardinality_error(PG_FUNCTION_ARGS)
22822283
nexecs=controlled ?sentry->execs_with_aqo :sentry->execs_without_aqo;
22832284
ce=controlled ?sentry->est_error_aqo :sentry->est_error;
22842285

2285-
values[AQE_NN]=Int32GetDatum(counter++);
2286+
values[AQE_NN]=Int32GetDatum(++counter);
22862287
values[AQE_QUERYID]=Int64GetDatum(qentry->queryid);
22872288
values[AQE_FS]=Int64GetDatum(qentry->fs);
22882289
values[AQE_NEXECS]=Int64GetDatum(nexecs);
@@ -2296,3 +2297,107 @@ aqo_cardinality_error(PG_FUNCTION_ARGS)
22962297
tuplestore_donestoring(tupstore);
22972298
return (Datum)0;
22982299
}
2300+
2301+
typedefenum {
2302+
ET_NN=0,ET_QUERYID,ET_FS,ET_EXECTIME,ET_NEXECS,ET_TOTAL_NCOLS
2303+
}et_output_order;
2304+
2305+
/*
2306+
* XXX: maybe to merge with aqo_cardinality_error ?
2307+
* XXX: Do we really want sequental number ?
2308+
*/
2309+
Datum
2310+
aqo_execution_time(PG_FUNCTION_ARGS)
2311+
{
2312+
boolcontrolled=PG_GETARG_BOOL(0);
2313+
ReturnSetInfo*rsinfo= (ReturnSetInfo*)fcinfo->resultinfo;
2314+
TupleDesctupDesc;
2315+
MemoryContextper_query_ctx;
2316+
MemoryContextoldcontext;
2317+
Tuplestorestate*tupstore;
2318+
Datumvalues[AQE_TOTAL_NCOLS];
2319+
boolnulls[AQE_TOTAL_NCOLS];
2320+
HASH_SEQ_STATUShash_seq;
2321+
QueriesEntry*qentry;
2322+
StatEntry*sentry;
2323+
intcounter=0;
2324+
2325+
/* check to see if caller supports us returning a tuplestore */
2326+
if (rsinfo==NULL|| !IsA(rsinfo,ReturnSetInfo))
2327+
ereport(ERROR,
2328+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2329+
errmsg("set-valued function called in context that cannot accept a set")));
2330+
if (!(rsinfo->allowedModes&SFRM_Materialize))
2331+
ereport(ERROR,
2332+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2333+
errmsg("materialize mode required, but it is not allowed in this context")));
2334+
2335+
/* Switch into long-lived context to construct returned data structures */
2336+
per_query_ctx=rsinfo->econtext->ecxt_per_query_memory;
2337+
oldcontext=MemoryContextSwitchTo(per_query_ctx);
2338+
2339+
/* Build a tuple descriptor for our result type */
2340+
if (get_call_result_type(fcinfo,NULL,&tupDesc)!=TYPEFUNC_COMPOSITE)
2341+
elog(ERROR,"return type must be a row type");
2342+
Assert(tupDesc->natts==ET_TOTAL_NCOLS);
2343+
2344+
tupstore=tuplestore_begin_heap(true, false,work_mem);
2345+
rsinfo->returnMode=SFRM_Materialize;
2346+
rsinfo->setResult=tupstore;
2347+
rsinfo->setDesc=tupDesc;
2348+
2349+
MemoryContextSwitchTo(oldcontext);
2350+
2351+
LWLockAcquire(&aqo_state->queries_lock,LW_SHARED);
2352+
LWLockAcquire(&aqo_state->stat_lock,LW_SHARED);
2353+
2354+
hash_seq_init(&hash_seq,queries_htab);
2355+
while ((qentry=hash_seq_search(&hash_seq))!=NULL)
2356+
{
2357+
boolfound;
2358+
double*et;
2359+
int64nexecs;
2360+
intnvals;
2361+
doubletm=0;
2362+
2363+
memset(nulls,0,ET_TOTAL_NCOLS*sizeof(nulls[0]));
2364+
2365+
sentry= (StatEntry*)hash_search(stat_htab,&qentry->queryid,
2366+
HASH_FIND,&found);
2367+
if (!found)
2368+
/* Statistics not found by some reason. Just go further */
2369+
continue;
2370+
2371+
nvals=controlled ?sentry->cur_stat_slot_aqo :sentry->cur_stat_slot;
2372+
if (nvals==0)
2373+
/* No one stat slot filled */
2374+
continue;
2375+
2376+
nexecs=controlled ?sentry->execs_with_aqo :sentry->execs_without_aqo;
2377+
et=controlled ?sentry->exec_time_aqo :sentry->exec_time;
2378+
2379+
if (!controlled)
2380+
{
2381+
inti;
2382+
/* Calculate average execution time */
2383+
for (i=0;i<nvals;i++)
2384+
tm+=et[i];
2385+
tm /=nvals;
2386+
}
2387+
else
2388+
tm=et[nvals-1];
2389+
2390+
values[ET_NN]=Int32GetDatum(++counter);
2391+
values[ET_QUERYID]=Int64GetDatum(qentry->queryid);
2392+
values[ET_FS]=Int64GetDatum(qentry->fs);
2393+
values[ET_NEXECS]=Int64GetDatum(nexecs);
2394+
values[ET_EXECTIME]=Float8GetDatum(tm);
2395+
tuplestore_putvalues(tupstore,tupDesc,values,nulls);
2396+
}
2397+
2398+
LWLockRelease(&aqo_state->stat_lock);
2399+
LWLockRelease(&aqo_state->queries_lock);
2400+
2401+
tuplestore_donestoring(tupstore);
2402+
return (Datum)0;
2403+
}

0 commit comments

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