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

Commit9079bc4

Browse files
committed
Add into AQO a GUC on minimum number of joins threshold.
If number of joins in a query less than this value - ignore this query.Also, rewrite (and rename) the aqo_drop routine.
1 parentd452014 commit9079bc4

File tree

11 files changed

+506
-15
lines changed

11 files changed

+506
-15
lines changed

‎aqo--1.4--1.5.sql‎

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -77,6 +77,7 @@ END;
7777
$$ LANGUAGE plpgsql;
7878

7979
DROPFUNCTIONpublic.top_time_queries;
80+
DROPFUNCTIONpublic.aqo_drop;
8081

8182
--
8283
-- Show execution time of queries, for which AQO has statistics.
@@ -131,3 +132,38 @@ $$ LANGUAGE plpgsql;
131132

132133
COMMENT ON FUNCTION public.show_execution_time(boolean) IS
133134
'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.';
135+
136+
CREATE OR REPLACEFUNCTIONpublic.aqo_drop_class(idbigint)
137+
RETURNSintegerAS $$
138+
DECLARE
139+
fsbigint;
140+
numinteger;
141+
BEGIN
142+
IF (id=0) THEN
143+
raise EXCEPTION'[AQO] Cannot remove basic class %.', id;
144+
END IF;
145+
146+
SELECT fspace_hashFROMpublic.aqo_queriesWHERE (query_hash= id) INTO fs;
147+
148+
IF (fs ISNULL) THEN
149+
raise WARNING'[AQO] Nothing to remove for the class %.', id;
150+
RETURN0;
151+
END IF;
152+
153+
IF (fs<> id) THEN
154+
raise WARNING'[AQO] Removing query class has non-generic feature space value: id = %, fs = %.', id, fs;
155+
END IF;
156+
157+
SELECTcount(*)FROMpublic.aqo_dataWHERE fspace_hash= fs INTO num;
158+
159+
/*
160+
* Remove the only from aqo_queries table. All other data will be removed by
161+
* CASCADE deletion.
162+
*/
163+
DELETEFROMpublic.aqo_queriesWHERE query_hash= id;
164+
RETURN num;
165+
END;
166+
$$ LANGUAGE plpgsql;
167+
168+
COMMENT ON FUNCTION public.aqo_drop_class(bigint) IS
169+
'Remove info about an query class from AQO ML knowledge base.';

‎aqo.c‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -199,6 +199,18 @@ _PG_init(void)
199199
NULL
200200
);
201201

202+
DefineCustomIntVariable("aqo.join_threshold",
203+
"Sets the threshold of number of JOINs in query beyond which AQO is used.",
204+
NULL,
205+
&aqo_join_threshold,
206+
0,
207+
0,INT_MAX /1000,
208+
PGC_USERSET,
209+
0,
210+
NULL,
211+
NULL,
212+
NULL);
213+
202214
prev_shmem_startup_hook=shmem_startup_hook;
203215
shmem_startup_hook=aqo_init_shmem;
204216
prev_planner_hook=planner_hook;

‎aqo.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -173,6 +173,7 @@ extern boolaqo_enabled;
173173
externboolforce_collect_stat;
174174
externboolaqo_show_hash;
175175
externboolaqo_show_details;
176+
externintaqo_join_threshold;
176177

177178
/*
178179
* It is mostly needed for auto tuning of query. with auto tuning mode aqo

‎expected/aqo_learn.out‎

Lines changed: 234 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -500,6 +500,240 @@ SELECT * FROM check_estimated_rows('
500500
17 | 17
501501
(1 row)
502502

503+
-- Test limit on number of joins
504+
SET aqo.mode = 'learn';
505+
SELECT * FROM aqo_drop_class(0);
506+
ERROR: [AQO] Cannot remove basic class 0.
507+
CONTEXT: PL/pgSQL function aqo_drop_class(bigint) line 7 at RAISE
508+
SELECT * FROM aqo_drop_class(42);
509+
WARNING: [AQO] Nothing to remove for the class 42.
510+
aqo_drop_class
511+
----------------
512+
0
513+
(1 row)
514+
515+
-- Remove all data from ML knowledge base
516+
SELECT count(*) FROM (
517+
SELECT aqo_drop_class(q1.id::bigint) FROM (
518+
SELECT query_hash AS id
519+
FROM aqo_queries WHERE query_hash <> 0) AS q1
520+
) AS q2;
521+
count
522+
-------
523+
7
524+
(1 row)
525+
526+
SELECT count(*) FROM aqo_data;
527+
count
528+
-------
529+
0
530+
(1 row)
531+
532+
SET aqo.join_threshold = 3;
533+
SELECT * FROM check_estimated_rows('SELECT * FROM aqo_test1;');
534+
estimated | actual
535+
-----------+--------
536+
20 | 20
537+
(1 row)
538+
539+
SELECT * FROM check_estimated_rows('SELECT * FROM aqo_test1 AS t1, aqo_test1 AS t2 WHERE t1.a = t2.b');
540+
estimated | actual
541+
-----------+--------
542+
20 | 19
543+
(1 row)
544+
545+
SELECT count(*) FROM aqo_data; -- Return 0 - do not learn on the queries above
546+
count
547+
-------
548+
0
549+
(1 row)
550+
551+
SELECT * FROM check_estimated_rows('
552+
SELECT *
553+
FROM aqo_test1 AS t1, aqo_test1 AS t2, aqo_test1 AS t3, aqo_test1 AS t4
554+
WHERE t1.a = t2.b AND t2.a = t3.b AND t3.a = t4.b;
555+
');
556+
estimated | actual
557+
-----------+--------
558+
20 | 17
559+
(1 row)
560+
561+
SELECT count(*) FROM -- Learn on the query
562+
(SELECT fspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1
563+
;
564+
count
565+
-------
566+
1
567+
(1 row)
568+
569+
SELECT query_text FROM aqo_query_texts WHERE query_hash <> 0; -- Check query
570+
query_text
571+
----------------------------------------------------------------------------
572+
explain analyze +
573+
SELECT * +
574+
FROM aqo_test1 AS t1, aqo_test1 AS t2, aqo_test1 AS t3, aqo_test1 AS t4+
575+
WHERE t1.a = t2.b AND t2.a = t3.b AND t3.a = t4.b; +
576+
577+
(1 row)
578+
579+
SET aqo.join_threshold = 1;
580+
SELECT * FROM check_estimated_rows('SELECT * FROM aqo_test1;');
581+
estimated | actual
582+
-----------+--------
583+
20 | 20
584+
(1 row)
585+
586+
SELECT * FROM check_estimated_rows('SELECT * FROM aqo_test1 AS t1, aqo_test1 AS t2 WHERE t1.a = t2.b');
587+
estimated | actual
588+
-----------+--------
589+
20 | 19
590+
(1 row)
591+
592+
SELECT count(*) FROM
593+
(SELECT fspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1
594+
; -- Learn on a query with one join
595+
count
596+
-------
597+
2
598+
(1 row)
599+
600+
SET aqo.join_threshold = 0;
601+
SELECT * FROM check_estimated_rows('SELECT * FROM aqo_test1;');
602+
estimated | actual
603+
-----------+--------
604+
20 | 20
605+
(1 row)
606+
607+
SELECT count(*) FROM
608+
(SELECT fspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1
609+
; -- Learn on the query without any joins now
610+
count
611+
-------
612+
3
613+
(1 row)
614+
615+
SET aqo.join_threshold = 1;
616+
SELECT * FROM check_estimated_rows('SELECT * FROM aqo_test1 t1 JOIN aqo_test1 AS t2 USING (a)');
617+
estimated | actual
618+
-----------+--------
619+
20 | 20
620+
(1 row)
621+
622+
SELECT count(*) FROM
623+
(SELECT fspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1
624+
; -- See one more query in the AQO knowledge base
625+
count
626+
-------
627+
4
628+
(1 row)
629+
630+
SELECT * FROM check_estimated_rows('WITH selected AS (SELECT * FROM aqo_test1 t1) SELECT count(*) FROM selected');
631+
estimated | actual
632+
-----------+--------
633+
1 | 1
634+
(1 row)
635+
636+
SELECT * FROM check_estimated_rows('
637+
WITH selected AS (
638+
SELECT * FROM aqo_test1 t1 JOIN aqo_test1 AS t2 USING (a)
639+
) SELECT count(*) FROM selected')
640+
;
641+
estimated | actual
642+
-----------+--------
643+
1 | 1
644+
(1 row)
645+
646+
SELECT count(*) FROM (SELECT fspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1; -- +1
647+
count
648+
-------
649+
5
650+
(1 row)
651+
652+
-- InitPlan
653+
SELECT * FROM check_estimated_rows('
654+
SELECT * FROM aqo_test1 AS t1 WHERE t1.a IN (
655+
SELECT t2.a FROM aqo_test1 AS t2 JOIN aqo_test1 AS t3 ON (t2.b = t3.a)
656+
)');
657+
estimated | actual
658+
-----------+--------
659+
20 | 19
660+
(1 row)
661+
662+
SELECT count(*) FROM (SELECT fspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1; -- +1
663+
count
664+
-------
665+
6
666+
(1 row)
667+
668+
-- SubPlan
669+
SELECT * FROM check_estimated_rows('
670+
SELECT (
671+
SELECT avg(t2.a) FROM aqo_test1 AS t2 JOIN aqo_test1 AS t3 ON (t2.b = t3.a) AND (t2.a = t1.a)
672+
) FROM aqo_test1 AS t1;
673+
');
674+
estimated | actual
675+
-----------+--------
676+
20 | 20
677+
(1 row)
678+
679+
SELECT count(*) FROM (SELECT fspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1; -- +1
680+
count
681+
-------
682+
7
683+
(1 row)
684+
685+
-- Subquery
686+
SET aqo.join_threshold = 3;
687+
SELECT * FROM check_estimated_rows('
688+
SELECT * FROM aqo_test1 AS t1,
689+
(SELECT t2.a FROM aqo_test1 AS t2 JOIN aqo_test1 AS t3 ON (t2.b = t3.a)) q1
690+
WHERE q1.a*t1.a = t1.a + 15;
691+
'); -- Two JOINs, ignore it
692+
estimated | actual
693+
-----------+--------
694+
2 | 4
695+
(1 row)
696+
697+
SELECT count(*) FROM (SELECT fspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1; -- +1
698+
count
699+
-------
700+
7
701+
(1 row)
702+
703+
SET aqo.join_threshold = 2;
704+
SELECT * FROM check_estimated_rows('
705+
SELECT * FROM aqo_test1 AS t1,
706+
(SELECT t2.a FROM aqo_test1 AS t2 JOIN aqo_test1 AS t3 ON (t2.b = t3.a)) q1
707+
WHERE q1.a*t1.a = t1.a + 15;
708+
'); -- One JOIN from subquery, another one from the query
709+
estimated | actual
710+
-----------+--------
711+
2 | 4
712+
(1 row)
713+
714+
SELECT count(*) FROM (SELECT fspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1; -- +1
715+
count
716+
-------
717+
8
718+
(1 row)
719+
720+
SELECT * FROM check_estimated_rows('
721+
WITH selected AS (
722+
SELECT t2.a FROM aqo_test1 t1 JOIN aqo_test1 AS t2 USING (a)
723+
) SELECT count(*) FROM aqo_test1 t3, selected WHERE selected.a = t3.a')
724+
; -- One JOIN extracted from CTE, another - from a FROM part of the query
725+
estimated | actual
726+
-----------+--------
727+
1 | 1
728+
(1 row)
729+
730+
SELECT count(*) FROM (SELECT fspace_hash FROM aqo_data GROUP BY (fspace_hash)) AS q1; -- +1
731+
count
732+
-------
733+
9
734+
(1 row)
735+
736+
RESET aqo.join_threshold;
503737
DROP INDEX aqo_test0_idx_a;
504738
DROP TABLE aqo_test0;
505739
DROP INDEX aqo_test1_idx_a;

‎expected/gucs.out‎

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,48 @@
11
CREATE EXTENSION aqo;
2+
-- Check interface variables and their default values. Detect, if default value
3+
-- of a GUC is changed.
4+
SHOW aqo.join_threshold;
5+
aqo.join_threshold
6+
--------------------
7+
0
8+
(1 row)
9+
10+
SHOW aqo.learn_statement_timeout;
11+
aqo.learn_statement_timeout
12+
-----------------------------
13+
off
14+
(1 row)
15+
16+
SHOW aqo.show_hash;
17+
aqo.show_hash
18+
---------------
19+
off
20+
(1 row)
21+
22+
SHOW aqo.show_details;
23+
aqo.show_details
24+
------------------
25+
off
26+
(1 row)
27+
28+
SHOW aqo.force_collect_stat;
29+
aqo.force_collect_stat
30+
------------------------
31+
off
32+
(1 row)
33+
34+
SHOW aqo.mode;
35+
aqo.mode
36+
------------
37+
controlled
38+
(1 row)
39+
240
SET aqo.mode = 'learn';
341
SET aqo.show_details = true;
442
CREATE TABLE t(x int);
543
INSERT INTO t (x) (SELECT * FROM generate_series(1, 100) AS gs);
644
ANALYZE t;
45+
-- Check AQO addons to explain (the only stable data)
746
EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF)
847
SELECT x FROM t;
948
QUERY PLAN
@@ -41,6 +80,12 @@ SELECT obj_description('public.show_execution_time'::regproc::oid);
4180
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.
4281
(1 row)
4382

83+
SELECT obj_description('public.aqo_drop_class'::regproc::oid);
84+
obj_description
85+
--------------------------------------------------------------
86+
Remove info about an query class from AQO ML knowledge base.
87+
(1 row)
88+
4489
\df show_cardinality_errors
4590
List of functions
4691
Schema | Name | Result data type | Argument data types | Type
@@ -55,4 +100,11 @@ SELECT obj_description('public.show_execution_time'::regproc::oid);
55100
public | show_execution_time | TABLE(num bigint, id bigint, fshash bigint, exec_time double precision, nexecs bigint) | controlled boolean | func
56101
(1 row)
57102

103+
\df aqo_drop_class
104+
List of functions
105+
Schema | Name | Result data type | Argument data types | Type
106+
--------+----------------+------------------+---------------------+------
107+
public | aqo_drop_class | integer | id bigint | func
108+
(1 row)
109+
58110
DROP EXTENSION aqo;

0 commit comments

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