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

Commitb7374f1

Browse files
committed
Replace aqo_data table with shmem hash table + DSA + file storage.
1 parent16275bf commitb7374f1

26 files changed

+779
-553
lines changed

‎aqo--1.4--1.5.sql‎

Lines changed: 35 additions & 37 deletions
Original file line numberDiff line numberDiff line change
@@ -60,35 +60,31 @@ RETURNS SETOF record
6060
AS'MODULE_PATHNAME','aqo_query_stat'
6161
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
6262

63+
CREATEFUNCTIONaqo_data(
64+
OUT fsbigint,
65+
OUT fssinteger,
66+
OUT nfeaturesinteger,
67+
OUT featuresdouble precision[][],
68+
OUT targetsdouble precision[],
69+
OUT reliabilitydouble precision[],
70+
OUT oidsinteger[]
71+
)
72+
RETURNS SETOF record
73+
AS'MODULE_PATHNAME','aqo_data'
74+
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
75+
CREATEFUNCTIONaqo_data_remove(fsbigint, fssint) RETURNS bool
76+
AS'MODULE_PATHNAME'
77+
LANGUAGE C PARALLEL SAFE;
78+
6379
CREATEVIEWaqo_query_statASSELECT*FROM aqo_query_stat();
6480
CREATEVIEWaqo_query_textsASSELECT*FROM aqo_query_texts();
81+
CREATEVIEWaqo_dataASSELECT*FROM aqo_data();
6582

6683
CREATEFUNCTIONaqo_stat_remove(fsbigint) RETURNS bool
6784
AS'MODULE_PATHNAME'
6885
LANGUAGE C STRICT PARALLEL SAFE;
6986

70-
--
71-
-- Re-create the aqo_data table.
72-
-- The oids array contains oids of permanent tables only. It is used for cleanup
73-
-- ML knowledge base from queries that refer to removed tables.
74-
--
75-
CREATETABLEaqo_data (
76-
fspace_hashbigintNOT NULLREFERENCES aqo_queriesON DELETE CASCADE,
77-
fsspace_hashintNOT NULL,
78-
nfeaturesintNOT NULL,
79-
featuresdouble precision[][],
80-
targetsdouble precision[],
81-
82-
-- oids of permanent tables only. It is used for cleanup
83-
-- ML knowledge base from queries that refer to removed tables.
84-
oidsoid [] DEFAULTNULL,
85-
86-
reliabilitydouble precision []
87-
);
88-
CREATEUNIQUE INDEXaqo_fss_access_idxON aqo_data (fspace_hash, fsspace_hash);
89-
9087
INSERT INTO aqo_queriesVALUES (0, false, false,0, false);
91-
-- INSERT INTO aqo_query_texts VALUES (0, 'COMMON feature space (do not delete!)');
9288
-- a virtual query for COMMON feature space
9389

9490
CREATETRIGGERaqo_queries_invalidate AFTERUPDATEORDELETEOR TRUNCATE
@@ -155,29 +151,30 @@ COMMENT ON FUNCTION aqo_execution_time(boolean) IS
155151
CREATE OR REPLACEFUNCTIONaqo_drop_class(queryidbigint)
156152
RETURNSintegerAS $$
157153
DECLARE
158-
fsbigint;
154+
lfsbigint;
159155
numinteger;
160156
BEGIN
161157
IF (queryid=0) THEN
162158
raise EXCEPTION'[AQO] Cannot remove basic class %.', queryid;
163159
END IF;
164160

165-
SELECT fspace_hashFROM aqo_queriesWHERE (query_hash= queryid) INTOfs;
161+
SELECT fspace_hashFROM aqo_queriesWHERE (query_hash= queryid) INTOlfs;
166162

167-
IF (fs ISNULL) THEN
163+
IF (lfs ISNULL) THEN
168164
raise WARNING'[AQO] Nothing to remove for the class %.', queryid;
169165
RETURN0;
170166
END IF;
171167

172-
IF (fs<> queryid) THEN
168+
IF (lfs<> queryid) THEN
173169
raise WARNING'[AQO] Removing query class has non-generic feature space value: id = %, fs = %.', queryid, fs;
174170
END IF;
175171

176-
SELECTcount(*)FROM aqo_dataWHEREfspace_hash=fs INTO num;
172+
SELECTcount(*)FROM aqo_dataWHEREfs=lfs INTO num;
177173

178174
DELETEFROM aqo_queriesWHERE query_hash= queryid;
179175
PERFORM aqo_stat_remove(queryid);
180176
PERFORM aqo_qtexts_remove(queryid);
177+
PERFORM aqo_data_remove(lfs,NULL);
181178
RETURN num;
182179
END;
183180
$$ LANGUAGE plpgsql;
@@ -195,28 +192,29 @@ COMMENT ON FUNCTION aqo_drop_class(bigint) IS
195192
CREATE OR REPLACEFUNCTIONaqo_cleanup(OUT nfsinteger, OUT nfssinteger)
196193
AS $$
197194
DECLARE
198-
fsbigint;
199-
fssinteger;
195+
lfsbigint;
196+
lfssinteger;
200197
BEGIN
201198
-- Save current number of rows
202199
SELECTcount(*)FROM aqo_queries INTO nfs;
203200
SELECTcount(*)FROM aqo_data INTO nfss;
204201

205-
FORfs,fssINSELECTq1.fs,q1.fssFROM (
206-
SELECTfspace_hashfs, fsspace_hash fss, unnest(oids)AS reloid
202+
FORlfs,lfssINSELECTq1.fs,q1.fssFROM (
203+
SELECT fs, fss, unnest(oids)AS reloid
207204
FROM aqo_data)AS q1
208205
WHEREq1.reloid NOTIN (SELECToidFROM pg_class)
209206
GROUP BY (q1.fs,q1.fss)
210207
LOOP
211-
IF (fs=0) THEN
212-
DELETEFROM aqo_dataWHERE fsspace_hash= fss;
213-
continue;
214-
END IF;
208+
-- IF (fs = 0) THEN
209+
-- DELETE FROM aqo_data WHERE fsspace_hash = fss;
210+
-- continue;
211+
-- END IF;
215212

216213
-- Remove ALL feature space if one of oids isn't exists
217-
DELETEFROM aqo_queriesWHERE fspace_hash= fs;
218-
PERFORM*FROM aqo_stat_remove(fs);
219-
PERFORM*FROM aqo_qtexts_remove(fs);
214+
DELETEFROM aqo_queriesWHERE fspace_hash= lfs;
215+
PERFORM aqo_stat_remove(lfs);
216+
PERFORM aqo_qtexts_remove(lfs);
217+
PERFORM aqo_data_remove(lfs,NULL);
220218
END LOOP;
221219

222220
-- Calculate difference with previous state of knowledge base

‎aqo.h‎

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -256,11 +256,8 @@ extern bool update_query(uint64 qhash, uint64 fhash,
256256
boollearn_aqo,booluse_aqo,boolauto_tuning);
257257
externboolload_fss_ext(uint64fs,intfss,OkNNrdata*data,List**reloids,
258258
boolisSafe);
259-
externboolload_fss(uint64fs,intfss,OkNNrdata*data,List**reloids,
260-
boolwideSearch);
261259
externboolupdate_fss_ext(uint64fs,intfss,OkNNrdata*data,
262260
List*reloids,boolisTimedOut);
263-
externboolupdate_fss(uint64fs,intfss,OkNNrdata*data,List*reloids);
264261
externboolmy_index_insert(RelationindexRelation,Datum*values,bool*isnull,
265262
ItemPointerheap_t_ctid,RelationheapRelation,
266263
IndexUniqueCheckcheckUnique);

‎aqo_shared.c‎

Lines changed: 20 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,7 @@ AQOSharedState *aqo_state = NULL;
2727
HTAB*fss_htab=NULL;
2828
staticintaqo_htab_max_items=1000;
2929
staticintfs_max_items=1000;/* Max number of different feature spaces in ML model */
30+
staticintfss_max_items=10000;
3031
staticuint32temp_storage_size=1024*1024*10;/* Storage size, in bytes */
3132
staticdsm_segment*seg=NULL;
3233

@@ -181,6 +182,7 @@ aqo_init_shmem(void)
181182
fss_htab=NULL;
182183
stat_htab=NULL;
183184
qtexts_htab=NULL;
185+
data_htab=NULL;
184186

185187
LWLockAcquire(AddinShmemInitLock,LW_EXCLUSIVE);
186188
aqo_state=ShmemInitStruct("AQO",sizeof(AQOSharedState),&found);
@@ -190,12 +192,17 @@ aqo_init_shmem(void)
190192

191193
LWLockInitialize(&aqo_state->lock,LWLockNewTrancheId());
192194
aqo_state->dsm_handler=DSM_HANDLE_INVALID;
195+
193196
aqo_state->qtexts_dsa_handler=DSM_HANDLE_INVALID;
194197
aqo_state->qtext_trancheid=LWLockNewTrancheId();
195198
aqo_state->qtexts_changed= false;
199+
aqo_state->data_dsa_handler=DSM_HANDLE_INVALID;
200+
aqo_state->data_trancheid=LWLockNewTrancheId();
201+
aqo_state->data_changed= false;
196202

197203
LWLockInitialize(&aqo_state->stat_lock,LWLockNewTrancheId());
198204
LWLockInitialize(&aqo_state->qtexts_lock,LWLockNewTrancheId());
205+
LWLockInitialize(&aqo_state->data_lock,LWLockNewTrancheId());
199206
}
200207

201208
info.keysize=sizeof(htab_key);
@@ -218,17 +225,25 @@ aqo_init_shmem(void)
218225
fs_max_items,fs_max_items,
219226
&info,HASH_ELEM |HASH_BLOBS);
220227

228+
/* Shared memory hash table for the data */
229+
info.keysize=sizeof(data_key);
230+
info.entrysize=sizeof(DataEntry);
231+
data_htab=ShmemInitHash("AQO Data HTAB",
232+
fss_max_items,fss_max_items,
233+
&info,HASH_ELEM |HASH_BLOBS);
234+
221235
LWLockRelease(AddinShmemInitLock);
222236
LWLockRegisterTranche(aqo_state->lock.tranche,"AQO");
223237
LWLockRegisterTranche(aqo_state->stat_lock.tranche,"AQO Stat Lock Tranche");
224238
LWLockRegisterTranche(aqo_state->qtexts_lock.tranche,"AQO QTexts Lock Tranche");
225239
LWLockRegisterTranche(aqo_state->qtext_trancheid,"AQO Query Texts Tranche");
226-
240+
LWLockRegisterTranche(aqo_state->data_lock.tranche,"AQO Data Lock Tranche");
241+
LWLockRegisterTranche(aqo_state->data_trancheid,"AQO Data Tranche");
227242

228243
if (!IsUnderPostmaster)
229244
{
230245
before_shmem_exit(on_shmem_shutdown, (Datum)0);
231-
aqo_stat_load();
246+
aqo_stat_load();/* Doesn't use DSA, so can be loaded in postmaster */
232247
}
233248
}
234249

@@ -249,6 +264,9 @@ aqo_memsize(void)
249264
size=MAXALIGN(sizeof(AQOSharedState));
250265
size=add_size(size,hash_estimate_size(aqo_htab_max_items,sizeof(htab_entry)));
251266
size=add_size(size,hash_estimate_size(fs_max_items,sizeof(AQOSharedState)));
267+
size=add_size(size,hash_estimate_size(fs_max_items,sizeof(StatEntry)));
268+
size=add_size(size,hash_estimate_size(fs_max_items,sizeof(QueryTextEntry)));
269+
size=add_size(size,hash_estimate_size(fss_max_items,sizeof(DataEntry)));
252270

253271
returnsize;
254272
}

‎aqo_shared.h‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -35,6 +35,11 @@ typedef struct AQOSharedState
3535
dsa_handleqtexts_dsa_handler;/* DSA area for storing of query texts */
3636
intqtext_trancheid;
3737
boolqtexts_changed;
38+
39+
LWLockdata_lock;/* Lock for shared fields below */
40+
dsa_handledata_dsa_handler;
41+
intdata_trancheid;
42+
booldata_changed;
3843
}AQOSharedState;
3944

4045

‎expected/aqo_controlled.out‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -301,7 +301,7 @@ DROP TABLE aqo_test2;
301301
SELECT aqo_reset();
302302
aqo_reset
303303
-----------
304-
22
304+
50
305305
(1 row)
306306

307307
DROP EXTENSION aqo;

‎expected/aqo_disabled.out‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -219,7 +219,7 @@ SELECT count(*) FROM aqo_queries WHERE query_hash <> fspace_hash; -- Should be z
219219
SELECT aqo_reset();
220220
aqo_reset
221221
-----------
222-
8
222+
18
223223
(1 row)
224224

225225
DROP EXTENSION aqo;

‎expected/aqo_forced.out‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -86,7 +86,7 @@ DROP TABLE aqo_test1;
8686
SELECT aqo_reset();
8787
aqo_reset
8888
-----------
89-
0
89+
3
9090
(1 row)
9191

9292
DROP EXTENSION aqo;

‎expected/aqo_intelligent.out‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -507,7 +507,7 @@ DROP TABLE aqo_test1;
507507
SELECT aqo_reset();
508508
aqo_reset
509509
-----------
510-
48
510+
103
511511
(1 row)
512512

513513
DROP EXTENSION aqo;

‎expected/aqo_learn.out‎

Lines changed: 14 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -252,7 +252,7 @@ WHERE aqt1.query_text = aqt2.query_text AND aqt1.queryid <> aqt2.queryid;
252252
-- Fix the state of the AQO data
253253
SELECT min(reliability),sum(nfeatures),query_text
254254
FROM aqo_data ad, aqo_query_texts aqt
255-
WHERE aqt.queryid = ad.fspace_hash
255+
WHERE aqt.queryid = ad.fs
256256
GROUP BY (query_text) ORDER BY (md5(query_text))
257257
;
258258
min | sum | query_text
@@ -524,8 +524,8 @@ SELECT * FROM check_estimated_rows('
524524
20 | 17
525525
(1 row)
526526

527-
SELECT count(*) FROM
528-
(SELECTfspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1
527+
SELECT count(*) FROM -- Learn on the query
528+
(SELECTfs FROM aqo_data GROUP BY (fs)) AS q1
529529
;
530530
count
531531
-------
@@ -557,8 +557,8 @@ SELECT * FROM check_estimated_rows(
557557
(1 row)
558558

559559
SELECT count(*) FROM
560-
(SELECTfspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1
561-
; -- Learn on anewquery with one join (cardinality of this join AQO extracted from previous 3-join query)
560+
(SELECTfs FROM aqo_data GROUP BY (fs)) AS q1
561+
; -- Learn on a query with one join
562562
count
563563
-------
564564
2
@@ -572,7 +572,7 @@ SELECT * FROM check_estimated_rows('SELECT * FROM aqo_test1;');
572572
(1 row)
573573

574574
SELECT count(*) FROM
575-
(SELECTfspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1
575+
(SELECTfs FROM aqo_data GROUP BY (fs)) AS q1
576576
; -- Learn on the query without any joins now
577577
count
578578
-------
@@ -587,7 +587,7 @@ SELECT * FROM check_estimated_rows('SELECT * FROM aqo_test1 t1 JOIN aqo_test1 AS
587587
(1 row)
588588

589589
SELECT count(*) FROM
590-
(SELECTfspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1
590+
(SELECTfs FROM aqo_data GROUP BY (fs)) AS q1
591591
; -- See one more query in the AQO knowledge base
592592
count
593593
-------
@@ -610,7 +610,7 @@ SELECT * FROM check_estimated_rows('
610610
1 | 1
611611
(1 row)
612612

613-
SELECT count(*) FROM (SELECTfspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1; -- +1
613+
SELECT count(*) FROM (SELECTfs FROM aqo_data GROUP BY (fs)) AS q1; -- +1
614614
count
615615
-------
616616
5
@@ -626,7 +626,7 @@ SELECT * FROM check_estimated_rows('
626626
20 | 19
627627
(1 row)
628628

629-
SELECT count(*) FROM (SELECTfspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1; -- +1
629+
SELECT count(*) FROM (SELECTfs FROM aqo_data GROUP BY (fs)) AS q1; -- +1
630630
count
631631
-------
632632
6
@@ -643,7 +643,7 @@ SELECT * FROM check_estimated_rows('
643643
20 | 20
644644
(1 row)
645645

646-
SELECT count(*) FROM (SELECTfspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1; -- +1
646+
SELECT count(*) FROM (SELECTfs FROM aqo_data GROUP BY (fs)) AS q1; -- +1
647647
count
648648
-------
649649
7
@@ -661,7 +661,7 @@ SELECT * FROM check_estimated_rows('
661661
2 | 4
662662
(1 row)
663663

664-
SELECT count(*) FROM (SELECTfspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1; -- +1
664+
SELECT count(*) FROM (SELECTfs FROM aqo_data GROUP BY (fs)) AS q1; -- +1
665665
count
666666
-------
667667
7
@@ -678,7 +678,7 @@ SELECT * FROM check_estimated_rows('
678678
2 | 4
679679
(1 row)
680680

681-
SELECT count(*) FROM (SELECTfspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1; -- +1
681+
SELECT count(*) FROM (SELECTfs FROM aqo_data GROUP BY (fs)) AS q1; -- +1
682682
count
683683
-------
684684
8
@@ -694,7 +694,7 @@ SELECT * FROM check_estimated_rows('
694694
1 | 1
695695
(1 row)
696696

697-
SELECT count(*) FROM (SELECTfspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1; -- +1
697+
SELECT count(*) FROM (SELECTfs FROM aqo_data GROUP BY (fs)) AS q1; -- +1
698698
count
699699
-------
700700
9
@@ -710,7 +710,7 @@ DROP TABLE aqo_test1;
710710
SELECT aqo_reset();
711711
aqo_reset
712712
-----------
713-
18
713+
44
714714
(1 row)
715715

716716
DROP EXTENSION aqo;

0 commit comments

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