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

Commit2f4e6cd

Browse files
danolivoAlena Rybakina
authored and
Alena Rybakina
committed
One more step to make AQO relocatable. Replace stored procedure aqo_cleanup()
with the one, implemented in C.BTW, fix issue, then AQO takes control on queries, involving only a setof TEMP tables. Now AQO learn on queries with at least one plane table,permanently stored in a database.Fix regression and TAP tests: somewhere because of changed behaviour, somewhereit caused by mistakes.
1 parent9a1244f commit2f4e6cd

17 files changed

+382
-144
lines changed

‎aqo--1.4--1.5.sql‎

Lines changed: 3 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -59,7 +59,7 @@ CREATE FUNCTION aqo_query_stat(
5959
OUT planning_time_without_aqodouble precision[],
6060
OUT cardinality_error_with_aqodouble precision[],
6161
OUT cardinality_error_without_aqodouble precision[],
62-
OUT executions_with_aqobigint,
62+
OUT executions_with_aqobigint,
6363
OUT executions_without_aqobigint
6464
)
6565
RETURNS SETOF record
@@ -190,38 +190,8 @@ COMMENT ON FUNCTION aqo_drop_class(bigint) IS
190190
-- Returns number of deleted rows from aqo_queries and aqo_data tables.
191191
--
192192
CREATE OR REPLACEFUNCTIONaqo_cleanup(OUT nfsinteger, OUT nfssinteger)
193-
AS $$
194-
DECLARE
195-
lfsbigint;
196-
lfssinteger;
197-
BEGIN
198-
-- Save current number of rows
199-
SELECTcount(*)FROM aqo_queries INTO nfs;
200-
SELECTcount(*)FROM aqo_data INTO nfss;
201-
202-
FOR lfs,lfssINSELECTq1.fs,q1.fssFROM (
203-
SELECT fs, fss, unnest(oids)AS reloid
204-
FROM aqo_data)AS q1
205-
WHEREq1.reloid NOTIN (SELECToidFROM pg_class)
206-
GROUP BY (q1.fs,q1.fss)
207-
LOOP
208-
-- IF (fs = 0) THEN
209-
-- DELETE FROM aqo_data WHERE fsspace_hash = fss;
210-
-- continue;
211-
-- END IF;
212-
213-
-- Remove ALL feature space if one of oids isn't exists
214-
PERFORM aqo_queries_remove(lfs);
215-
PERFORM aqo_stat_remove(lfs);
216-
PERFORM aqo_qtexts_remove(lfs);
217-
PERFORM aqo_data_remove(lfs,NULL);
218-
END LOOP;
219-
220-
-- Calculate difference with previous state of knowledge base
221-
nfs := nfs- (SELECTcount(*)FROM aqo_queries);
222-
nfss := nfss- (SELECTcount(*)FROM aqo_data);
223-
END;
224-
$$ LANGUAGE plpgsql;
193+
AS'MODULE_PATHNAME'
194+
LANGUAGE C STRICT VOLATILE;
225195

226196
COMMENT ON FUNCTION aqo_cleanup() IS
227197
'Remove unneeded rows from the AQO ML storage';

‎aqo_shared.c‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -216,25 +216,25 @@ aqo_init_shmem(void)
216216

217217
info.keysize=sizeof(((StatEntry*)0)->queryid);
218218
info.entrysize=sizeof(StatEntry);
219-
stat_htab=ShmemInitHash("AQO Stat HTAB",64,fs_max_items,
219+
stat_htab=ShmemInitHash("AQO Stat HTAB",fs_max_items,fs_max_items,
220220
&info,HASH_ELEM |HASH_BLOBS);
221221

222222
/* Init shared memory table for query texts */
223223
info.keysize=sizeof(((QueryTextEntry*)0)->queryid);
224224
info.entrysize=sizeof(QueryTextEntry);
225-
qtexts_htab=ShmemInitHash("AQO Query Texts HTAB",64,fs_max_items,
225+
qtexts_htab=ShmemInitHash("AQO Query Texts HTAB",fs_max_items,fs_max_items,
226226
&info,HASH_ELEM |HASH_BLOBS);
227227

228228
/* Shared memory hash table for the data */
229229
info.keysize=sizeof(data_key);
230230
info.entrysize=sizeof(DataEntry);
231-
data_htab=ShmemInitHash("AQO Data HTAB",64,fss_max_items,
231+
data_htab=ShmemInitHash("AQO Data HTAB",fss_max_items,fss_max_items,
232232
&info,HASH_ELEM |HASH_BLOBS);
233233

234234
/* Shared memory hash table for queries */
235235
info.keysize=sizeof(((QueriesEntry*)0)->queryid);
236236
info.entrysize=sizeof(QueriesEntry);
237-
queries_htab=ShmemInitHash("AQO Queries HTAB",64,fs_max_items,
237+
queries_htab=ShmemInitHash("AQO Queries HTAB",fs_max_items,fs_max_items,
238238
&info,HASH_ELEM |HASH_BLOBS);
239239

240240
LWLockRelease(AddinShmemInitLock);

‎aqo_shared.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,7 @@ typedef struct AQOSharedState
3030

3131
/* Storage fields */
3232
LWLockstat_lock;/* lock for access to stat storage */
33+
boolstat_changed;
3334

3435
LWLockqtexts_lock;/* Lock for shared fields below */
3536
dsa_handleqtexts_dsa_handler;/* DSA area for storing of query texts */

‎expected/aqo_learn.out‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -236,10 +236,10 @@ SELECT count(*) FROM tmp1;
236236
(1 row)
237237

238238
-- Remove data on some unneeded instances of tmp1 table.
239-
SELECT aqo_cleanup();
240-
aqo_cleanup
241-
-------------
242-
(9,18)
239+
SELECT* FROMaqo_cleanup();
240+
nfs | nfss
241+
-----+------
242+
9 | 18
243243
(1 row)
244244

245245
-- Result of the query below should be empty

‎expected/forced_stat_collection.out‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -55,4 +55,11 @@ SELECT query_text FROM aqo_query_texts ORDER BY (md5(query_text));
5555
SELECT count(*) FROM person WHERE age<18 AND passport IS NOT NULL;
5656
(3 rows)
5757

58+
DROP TABLE person;
59+
SELECT 1 FROM aqo_reset(); -- Full remove of ML data before the end
60+
?column?
61+
----------
62+
1
63+
(1 row)
64+
5865
DROP EXTENSION aqo;

‎expected/temp_tables.out‎

Lines changed: 19 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -16,10 +16,11 @@ SELECT count(*) FROM tt AS t1, tt AS t2;
1616
0
1717
(1 row)
1818

19-
SELECT * FROM aqo_data;
20-
fs | fss | nfeatures | features | targets | reliability | oids
21-
----+-----+-----------+----------+---------+-------------+------
22-
(0 rows)
19+
SELECT query_text FROM aqo_query_texts; -- Default row should be returned
20+
query_text
21+
---------------------------------------
22+
COMMON feature space (do not delete!)
23+
(1 row)
2324

2425
-- Should be stored in the ML base
2526
SELECT count(*) FROM pt;
@@ -40,30 +41,30 @@ SELECT count(*) FROM pt AS pt1, tt AS tt1, tt AS tt2, pt AS pt2;
4041
0
4142
(1 row)
4243

43-
SELECT count(*) FROM aqo_data;
44+
SELECT count(*) FROM aqo_data; -- Don't bother about false negatives because of trivial query plans
4445
count
4546
-------
4647
10
4748
(1 row)
4849

4950
DROP TABLE tt;
50-
SELECT aqo_cleanup();
51-
aqo_cleanup
52-
-------------
53-
(0,0)
51+
SELECT* FROMaqo_cleanup();
52+
nfs | nfss
53+
-----+------
54+
0 | 0
5455
(1 row)
5556

56-
SELECT count(*) FROM aqo_data; -- Shouldbe the same as above
57+
SELECT count(*) FROM aqo_data; -- Shouldreturn the same as previous call above
5758
count
5859
-------
5960
10
6061
(1 row)
6162

6263
DROP TABLE pt;
63-
SELECT aqo_cleanup();
64-
aqo_cleanup
65-
-------------
66-
(3,10)
64+
SELECT* FROMaqo_cleanup();
65+
nfs | nfss
66+
-----+------
67+
3 | 10
6768
(1 row)
6869

6970
SELECT count(*) FROM aqo_data; -- Should be 0
@@ -74,13 +75,11 @@ SELECT count(*) FROM aqo_data; -- Should be 0
7475

7576
SELECT query_text FROM aqo_queries aq LEFT JOIN aqo_query_texts aqt
7677
ON aq.queryid = aqt.queryid
77-
ORDER BY (md5(query_text)); -- TODO: should contain just one row
78-
query_text
79-
------------------------------------------
80-
SELECT count(*) FROM tt AS t1, tt AS t2;
78+
ORDER BY (md5(query_text)); -- The only the common class is returned
79+
query_text
80+
---------------------------------------
8181
COMMON feature space (do not delete!)
82-
SELECT count(*) FROM tt;
83-
(3 rows)
82+
(1 row)
8483

8584
-- Test learning on temporary table
8685
CREATE TABLE pt AS SELECT x AS x, (x % 10) AS y FROM generate_series(1,100) AS x;

‎expected/top_queries.out‎

Lines changed: 4 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@ SET aqo.force_collect_stat = 'on';
1010
--
1111
CREATE TEMP TABLE ttt AS SELECT count(*) AS cnt FROM generate_series(1,10);
1212
CREATE TABLE ttp AS SELECT count(*) AS cnt FROM generate_series(1,10);
13-
SELECT count(*) AS cnt FROM ttt WHERE cnt % 100 = 0;
13+
SELECT count(*) AS cnt FROM ttt WHERE cnt % 100 = 0; -- Ignore it
1414
cnt
1515
-----
1616
0
@@ -31,8 +31,7 @@ SELECT num FROM aqo_execution_time(false);
3131
num
3232
-----
3333
1
34-
2
35-
(2 rows)
34+
(1 row)
3635

3736
-- Without the AQO control queries with and without temp tables are logged.
3837
SELECT query_text,nexecs
@@ -41,9 +40,8 @@ WHERE ce.id = aqt.queryid
4140
ORDER BY (md5(query_text));
4241
query_text | nexecs
4342
------------------------------------------------------+--------
44-
SELECT count(*) AS cnt FROM ttt WHERE cnt % 100 = 0; | 1
4543
SELECT count(*) AS cnt FROM ttp WHERE cnt % 100 = 0; | 1
46-
(2 rows)
44+
(1 row)
4745

4846
--
4947
-- num of query which uses the table t2 should be bigger than num of query which
@@ -93,10 +91,9 @@ ORDER BY (md5(query_text));
9391
query_text | nexecs
9492
------------------------------------------------------------------------------------------------+--------
9593
SELECT count(*) FROM (SELECT x, y FROM t2 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1; | 1
96-
SELECT count(*) AS cnt FROM ttt WHERE cnt % 100 = 0; | 1
9794
SELECT count(*) AS cnt FROM ttp WHERE cnt % 100 = 0; | 1
9895
SELECT count(*) FROM (SELECT x, y FROM t1 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1; | 1
99-
(4 rows)
96+
(3 rows)
10097

10198
SELECT 1 FROM aqo_reset();
10299
?column?

‎expected/unsupported.out‎

Lines changed: 17 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -579,11 +579,23 @@ ORDER BY (md5(query_text),error) DESC;
579579
| ON q1.x = q2.x+1;
580580
(12 rows)
581581

582-
DROP TABLE t,t1 CASCADE;
583-
SELECT aqo_cleanup();
584-
aqo_cleanup
585-
-------------
586-
(12,42)
582+
DROP TABLE t,t1 CASCADE; -- delete all tables used in the test
583+
SELECT count(*) FROM aqo_data; -- Just to detect some changes in the logic. May some false positives really bother us here?
584+
count
585+
-------
586+
42
587+
(1 row)
588+
589+
SELECT * FROM aqo_cleanup();
590+
nfs | nfss
591+
-----+------
592+
12 | 42
593+
(1 row)
594+
595+
SELECT count(*) FROM aqo_data; -- No one row should be returned
596+
count
597+
-------
598+
0
587599
(1 row)
588600

589601
-- Look for any remaining queries in the ML storage.

‎preprocessing.c‎

Lines changed: 10 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -367,7 +367,8 @@ typedef struct AQOPreWalkerCtx
367367

368368
/*
369369
* Examine a fully-parsed query, and return TRUE iff any relation underlying
370-
* the query is a system relation or no one relation touched by the query.
370+
* the query is a system relation or no one permanent (non-temporary) relation
371+
* touched by the query.
371372
*/
372373
staticbool
373374
isQueryUsingSystemRelation(Query*query)
@@ -458,11 +459,17 @@ isQueryUsingSystemRelation_walker(Node *node, void *context)
458459
boolis_catalog=IsCatalogRelation(rel);
459460
boolis_aqo_rel=IsAQORelation(rel);
460461

461-
table_close(rel,AccessShareLock);
462462
if (is_catalog||is_aqo_rel)
463+
{
464+
table_close(rel,AccessShareLock);
463465
return true;
466+
}
467+
468+
if (rel->rd_rel->relpersistence!=RELPERSISTENCE_TEMP)
469+
/* Plane non TEMP table */
470+
ctx->trivQuery= false;
464471

465-
ctx->trivQuery= false;
472+
table_close(rel,AccessShareLock);
466473
}
467474
elseif (rte->rtekind==RTE_FUNCTION)
468475
{

‎sql/aqo_learn.sql‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -124,7 +124,7 @@ WHERE t1.a = t2.b AND t2.a = t3.b AND t3.a = t4.b;
124124
SELECTcount(*)FROM tmp1;
125125

126126
-- Remove data on some unneeded instances of tmp1 table.
127-
SELECT aqo_cleanup();
127+
SELECT*FROMaqo_cleanup();
128128

129129
-- Result of the query below should be empty
130130
SELECT*FROM aqo_query_texts aqt1, aqo_query_texts aqt2

0 commit comments

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