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

Commite810d1d

Browse files
Andrey Kazarinovdanolivo
Andrey Kazarinov
authored andcommitted
[PGPRO-6374] relocatable aqo
1 parent8a99337 commite810d1d

20 files changed

+266
-142
lines changed

‎Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -25,7 +25,8 @@ REGRESS =aqo_disabled \
2525
plancache\
2626
statement_timeout\
2727
temp_tables\
28-
top_queries
28+
top_queries\
29+
relocatable
2930

3031
fdw_srcdir =$(top_srcdir)/contrib/postgres_fdw
3132
stat_srcdir =$(top_srcdir)/contrib/pg_stat_statements

‎aqo--1.0--1.1.sql

Lines changed: 9 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,13 +1,13 @@
1-
ALTERTABLEpublic.aqo_query_texts ALTER COLUMN query_text TYPEtext;
1+
ALTERTABLE aqo_query_texts ALTER COLUMN query_text TYPEtext;
22

33

4-
DROPINDEXpublic.aqo_queries_query_hash_idx CASCADE;
5-
DROPINDEXpublic.aqo_query_texts_query_hash_idx CASCADE;
6-
DROPINDEXpublic.aqo_query_stat_idx CASCADE;
7-
DROPINDEXpublic.aqo_fss_access_idx CASCADE;
4+
DROPINDEX aqo_queries_query_hash_idx CASCADE;
5+
DROPINDEX aqo_query_texts_query_hash_idx CASCADE;
6+
DROPINDEX aqo_query_stat_idx CASCADE;
7+
DROPINDEX aqo_fss_access_idx CASCADE;
88

99
CREATEUNIQUE INDEXaqo_fss_access_idx
10-
ONpublic.aqo_data (fspace_hash, fsspace_hash);
10+
ON aqo_data (fspace_hash, fsspace_hash);
1111

1212

1313
CREATE OR REPLACEFUNCTIONaqo_migrate_to_1_1_get_pk(rel regclass) RETURNS regclassAS $$
@@ -28,15 +28,15 @@ $$ LANGUAGE plpgsql;
2828
DO $$
2929
BEGIN
3030
EXECUTEpg_catalog.format('ALTER TABLE %s RENAME to %s',
31-
aqo_migrate_to_1_1_get_pk('public.aqo_queries'),
31+
aqo_migrate_to_1_1_get_pk('aqo_queries'),
3232
'aqo_queries_query_hash_idx');
3333

3434
EXECUTEpg_catalog.format('ALTER TABLE %s RENAME to %s',
35-
aqo_migrate_to_1_1_get_pk('public.aqo_query_texts'),
35+
aqo_migrate_to_1_1_get_pk('aqo_query_texts'),
3636
'aqo_query_texts_query_hash_idx');
3737

3838
EXECUTEpg_catalog.format('ALTER TABLE %s RENAME to %s',
39-
aqo_migrate_to_1_1_get_pk('public.aqo_query_stat'),
39+
aqo_migrate_to_1_1_get_pk('aqo_query_stat'),
4040
'aqo_query_stat_idx');
4141
END
4242
$$;

‎aqo--1.0.sql

Lines changed: 14 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1,21 +1,21 @@
11
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
22
\echo Use"CREATE EXTENSION aqo" to load this file. \quit
33

4-
CREATETABLEpublic.aqo_queries (
4+
CREATETABLEaqo_queries (
55
query_hashbigintPRIMARY KEY,
66
learn_aqobooleanNOT NULL,
77
use_aqobooleanNOT NULL,
88
fspace_hashbigintNOT NULL,
99
auto_tuningbooleanNOT NULL
1010
);
1111

12-
CREATETABLEpublic.aqo_query_texts (
13-
query_hashbigintPRIMARY KEYREFERENCESpublic.aqo_queriesON DELETE CASCADE,
12+
CREATETABLEaqo_query_texts (
13+
query_hashbigintPRIMARY KEYREFERENCES aqo_queriesON DELETE CASCADE,
1414
query_textvarcharNOT NULL
1515
);
1616

17-
CREATETABLEpublic.aqo_query_stat (
18-
query_hashbigintPRIMARY KEYREFERENCESpublic.aqo_queriesON DELETE CASCADE,
17+
CREATETABLEaqo_query_stat (
18+
query_hashbigintPRIMARY KEYREFERENCES aqo_queriesON DELETE CASCADE,
1919
execution_time_with_aqodouble precision[],
2020
execution_time_without_aqodouble precision[],
2121
planning_time_with_aqodouble precision[],
@@ -26,27 +26,27 @@ CREATE TABLE public.aqo_query_stat (
2626
executions_without_aqobigint
2727
);
2828

29-
CREATETABLEpublic.aqo_data (
30-
fspace_hashbigintNOT NULLREFERENCESpublic.aqo_queriesON DELETE CASCADE,
29+
CREATETABLEaqo_data (
30+
fspace_hashbigintNOT NULLREFERENCES aqo_queriesON DELETE CASCADE,
3131
fsspace_hashintNOT NULL,
3232
nfeaturesintNOT NULL,
3333
featuresdouble precision[][],
3434
targetsdouble precision[],
3535
UNIQUE (fspace_hash, fsspace_hash)
3636
);
3737

38-
CREATEINDEXaqo_queries_query_hash_idxONpublic.aqo_queries (query_hash);
39-
CREATEINDEXaqo_query_texts_query_hash_idxONpublic.aqo_query_texts (query_hash);
40-
CREATEINDEXaqo_query_stat_idxONpublic.aqo_query_stat (query_hash);
41-
CREATEINDEXaqo_fss_access_idxONpublic.aqo_data (fspace_hash, fsspace_hash);
38+
CREATEINDEXaqo_queries_query_hash_idxON aqo_queries (query_hash);
39+
CREATEINDEXaqo_query_texts_query_hash_idxON aqo_query_texts (query_hash);
40+
CREATEINDEXaqo_query_stat_idxON aqo_query_stat (query_hash);
41+
CREATEINDEXaqo_fss_access_idxON aqo_data (fspace_hash, fsspace_hash);
4242

43-
INSERT INTOpublic.aqo_queriesVALUES (0, false, false,0, false);
44-
INSERT INTOpublic.aqo_query_textsVALUES (0,'COMMON feature space (do not delete!)');
43+
INSERT INTO aqo_queriesVALUES (0, false, false,0, false);
44+
INSERT INTO aqo_query_textsVALUES (0,'COMMON feature space (do not delete!)');
4545
-- a virtual query for COMMON feature space
4646

4747
CREATEFUNCTIONinvalidate_deactivated_queries_cache() RETURNS trigger
4848
AS'MODULE_PATHNAME' LANGUAGE C;
4949

5050
CREATETRIGGERaqo_queries_invalidate AFTERUPDATEORDELETEOR TRUNCATE
51-
ONpublic.aqo_queries FOR EACH STATEMENT
51+
ON aqo_queries FOR EACH STATEMENT
5252
EXECUTE PROCEDURE invalidate_deactivated_queries_cache();

‎aqo--1.1--1.2.sql

Lines changed: 18 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -14,8 +14,8 @@ $$ LANGUAGE plpgsql;
1414
DO $$
1515
BEGIN
1616
EXECUTEpg_catalog.format(
17-
'ALTER TABLEpublic.aqo_data DROP CONSTRAINT %s',
18-
aqo_migrate_to_1_2_get_pk('public.aqo_data'::regclass),
17+
'ALTER TABLE aqo_data DROP CONSTRAINT %s',
18+
aqo_migrate_to_1_2_get_pk('aqo_data'::regclass),
1919
'aqo_queries_query_hash_idx');
2020
END
2121
$$;
@@ -28,7 +28,7 @@ DROP FUNCTION aqo_migrate_to_1_2_get_pk(regclass);
2828
--
2929

3030
-- Show query state at the AQO knowledge base
31-
CREATE OR REPLACEFUNCTIONpublic.aqo_status(hashbigint)
31+
CREATE OR REPLACEFUNCTIONaqo_status(hashbigint)
3232
RETURNS TABLE (
3333
"learn"BOOL,
3434
"use aqo"BOOL,
@@ -49,58 +49,58 @@ SELECTlearn_aqo,use_aqo,auto_tuning,fspace_hash,
4949
to_char(execution_time_with_aqo[n3],'9.99EEEE'),
5050
to_char(cardinality_error_with_aqo[n1],'9.99EEEE'),
5151
executions_with_aqo
52-
FROMpublic.aqo_queries aq,public.aqo_query_stat aqs,
52+
FROM aqo_queries aq, aqo_query_stat aqs,
5353
(SELECT array_length(n1,1)AS n1, array_length(n2,1)AS n2,
5454
array_length(n3,1)AS n3, array_length(n4,1)AS n4
5555
FROM
5656
(SELECT cardinality_error_with_aqoAS n1,
5757
cardinality_error_without_aqoAS n2,
5858
execution_time_with_aqoAS n3,
5959
execution_time_without_aqoAS n4
60-
FROMpublic.aqo_query_stat aqsWHERE
60+
FROM aqo_query_stat aqsWHERE
6161
aqs.query_hash= $1)AS al)AS q
6262
WHERE (aqs.query_hash=aq.query_hash)AND
6363
aqs.query_hash= $1;
6464
$func$ LANGUAGE SQL;
6565

66-
CREATE OR REPLACEFUNCTIONpublic.aqo_enable_query(hashbigint)
66+
CREATE OR REPLACEFUNCTIONaqo_enable_query(hashbigint)
6767
RETURNS VOID
6868
AS $func$
69-
UPDATEpublic.aqo_queriesSET
69+
UPDATE aqo_queriesSET
7070
learn_aqo='true',
7171
use_aqo='true'
7272
WHERE query_hash= $1;
7373
$func$ LANGUAGE SQL;
7474

75-
CREATE OR REPLACEFUNCTIONpublic.aqo_disable_query(hashbigint)
75+
CREATE OR REPLACEFUNCTIONaqo_disable_query(hashbigint)
7676
RETURNS VOID
7777
AS $func$
78-
UPDATEpublic.aqo_queriesSET
78+
UPDATE aqo_queriesSET
7979
learn_aqo='false',
8080
use_aqo='false',
8181
auto_tuning='false'
8282
WHERE query_hash= $1;
8383
$func$ LANGUAGE SQL;
8484

85-
CREATE OR REPLACEFUNCTIONpublic.aqo_clear_hist(hashbigint)
85+
CREATE OR REPLACEFUNCTIONaqo_clear_hist(hashbigint)
8686
RETURNS VOID
8787
AS $func$
88-
DELETEFROMpublic.aqo_dataWHERE fspace_hash=$1;
88+
DELETEFROM aqo_dataWHERE fspace_hash=$1;
8989
$func$ LANGUAGE SQL;
9090

9191
-- Show queries that contains 'Never executed' nodes at the plan.
92-
CREATE OR REPLACEFUNCTIONpublic.aqo_ne_queries()
92+
CREATE OR REPLACEFUNCTIONaqo_ne_queries()
9393
RETURNS SETOFint
9494
AS $func$
95-
SELECT query_hashFROMpublic.aqo_query_stat aqs
95+
SELECT query_hashFROM aqo_query_stat aqs
9696
WHERE-1= ANY (cardinality_error_with_aqo::double precision[]);
9797
$func$ LANGUAGE SQL;
9898

99-
CREATE OR REPLACEFUNCTIONpublic.aqo_drop(hashbigint)
99+
CREATE OR REPLACEFUNCTIONaqo_drop(hashbigint)
100100
RETURNS VOID
101101
AS $func$
102-
DELETEFROMpublic.aqo_queries aqWHERE (aq.query_hash= $1);
103-
DELETEFROMpublic.aqo_data adWHERE (ad.fspace_hash= $1);
104-
DELETEFROMpublic.aqo_query_stat aqWHERE (aq.query_hash= $1);
105-
DELETEFROMpublic.aqo_query_texts aqWHERE (aq.query_hash= $1);
102+
DELETEFROM aqo_queries aqWHERE (aq.query_hash= $1);
103+
DELETEFROM aqo_data adWHERE (ad.fspace_hash= $1);
104+
DELETEFROM aqo_query_stat aqWHERE (aq.query_hash= $1);
105+
DELETEFROM aqo_query_texts aqWHERE (aq.query_hash= $1);
106106
$func$ LANGUAGE SQL;

‎aqo--1.2--1.3.sql

Lines changed: 7 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,9 @@
1-
ALTERTABLEpublic.aqo_data ADD COLUMN oidstext [] DEFAULTNULL;
1+
ALTERTABLE aqo_data ADD COLUMN oidstext [] DEFAULTNULL;
22

33
--
44
-- Remove data, related to previously dropped tables, from the AQO tables.
55
--
6-
CREATE OR REPLACEFUNCTIONpublic.clean_aqo_data() RETURNS voidAS $$
6+
CREATE OR REPLACEFUNCTIONclean_aqo_data() RETURNS voidAS $$
77
DECLARE
88
aqo_data_row aqo_data%ROWTYPE;
99
aqo_queries_row aqo_queries%ROWTYPE;
@@ -29,7 +29,7 @@ BEGIN
2929
END LOOP;
3030
END IF;
3131

32-
FOR aqo_queries_rowIN (SELECT*FROMpublic.aqo_queries)
32+
FOR aqo_queries_rowIN (SELECT*FROM aqo_queries)
3333
LOOP
3434
IF (delete_row= trueAND fspace_hash_var<>0AND
3535
fspace_hash_var=aqo_queries_row.fspace_hashAND
@@ -87,7 +87,7 @@ $$ LANGUAGE plpgsql;
8787
--
8888
-- Top of queries with the highest value of execution time.
8989
--
90-
CREATE OR REPLACEFUNCTIONpublic.top_time_queries(nint)
90+
CREATE OR REPLACEFUNCTIONtop_time_queries(nint)
9191
RETURNS TABLE(numbigint,
9292
fspace_hashbigint,
9393
query_hashbigint,
@@ -103,7 +103,7 @@ BEGIN
103103
aqo_queries.query_hash,
104104
to_char(array_avg(execution_time_without_aqo),'9.99EEEE')::float,
105105
to_char(array_mse(execution_time_without_aqo),'9.99EEEE')::float
106-
FROMpublic.aqo_queriesINNER JOIN aqo_query_stat
106+
FROM aqo_queriesINNER JOIN aqo_query_stat
107107
ONaqo_queries.query_hash=aqo_query_stat.query_hash
108108
GROUP BY (execution_time_without_aqo,aqo_queries.fspace_hash,aqo_queries.query_hash)
109109
ORDER BY execution_timeDESCLIMIT n;
@@ -113,7 +113,7 @@ $$ LANGUAGE plpgsql;
113113
--
114114
-- Top of queries with largest value of total cardinality error.
115115
--
116-
CREATE OR REPLACEFUNCTIONpublic.top_error_queries(nint)
116+
CREATE OR REPLACEFUNCTIONtop_error_queries(nint)
117117
RETURNS TABLE(numbigint,
118118
fspace_hashbigint,
119119
query_hashbigint,
@@ -129,7 +129,7 @@ BEGIN
129129
aqo_queries.query_hash,
130130
to_char(array_avg(cardinality_error_without_aqo),'9.99EEEE')::float,
131131
to_char(array_mse(cardinality_error_without_aqo),'9.99EEEE')::float
132-
FROMpublic.aqo_queriesINNER JOIN aqo_query_stat
132+
FROM aqo_queriesINNER JOIN aqo_query_stat
133133
ONaqo_queries.query_hash=aqo_query_stat.query_hash
134134
GROUP BY (cardinality_error_without_aqo,aqo_queries.fspace_hash,aqo_queries.query_hash)
135135
ORDER BY errorDESCLIMIT n;

0 commit comments

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