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

Commitd452014

Browse files
committed
Fix cardinality error calculation.
Switch from the top_queries(n) routine to show_execution_time(controlled)to unify AQO interface.
1 parent8879624 commitd452014

File tree

7 files changed

+145
-17
lines changed

7 files changed

+145
-17
lines changed

‎aqo--1.4--1.5.sql

Lines changed: 63 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -5,15 +5,17 @@
55

66
--
77
-- Re-create the aqo_data table. Do so to keep the columns order.
8+
-- The oids array contains oids of permanent tables only. It is used for cleanup
9+
-- ML knowledge base from queries that refer to removed tables.
810
--
911
DROPTABLEpublic.aqo_data CASCADE;
1012
CREATETABLEpublic.aqo_data (
1113
fspace_hashbigintNOT NULLREFERENCESpublic.aqo_queriesON DELETE CASCADE,
1214
fsspace_hashintNOT NULL,
13-
nfeaturesintNOT NULL,
14-
featuresdouble precision[][],
15-
targetsdouble precision[],
16-
oidsoid [] DEFAULTNULL,
15+
nfeaturesintNOT NULL,
16+
featuresdouble precision[][],
17+
targetsdouble precision[],
18+
oidsoid [] DEFAULTNULL,
1719
reliabilitydouble precision []
1820
);
1921
CREATEUNIQUE INDEXaqo_fss_access_idxONpublic.aqo_data (fspace_hash, fsspace_hash);
@@ -72,4 +74,60 @@ BEGIN
7274
END LOOP;
7375
END LOOP;
7476
END;
75-
$$ LANGUAGE plpgsql;
77+
$$ LANGUAGE plpgsql;
78+
79+
DROPFUNCTIONpublic.top_time_queries;
80+
81+
--
82+
-- Show execution time of queries, for which AQO has statistics.
83+
-- controlled - show stat on executions where AQO was used for cardinality
84+
-- estimations, or not used (controlled = false).
85+
-- Last case is possible in disabled mode with aqo.force_collect_stat = 'on'.
86+
--
87+
CREATE OR REPLACEFUNCTIONpublic.show_execution_time(controlledboolean)
88+
RETURNS TABLE(numbigint, idbigint, fshashbigint, exec_time float, nexecsbigint)
89+
AS $$
90+
BEGIN
91+
IF (controlled) THEN
92+
-- Show a query execution time made with AQO support for the planner
93+
-- cardinality estimations. Here we return result of last execution.
94+
RETURN QUERY
95+
SELECT
96+
row_number() OVER (ORDER BY (exectime, queryid, fs_hash)DESC)AS nn,
97+
queryid, fs_hash, exectime, execs
98+
FROM (
99+
SELECT
100+
aq.query_hashAS queryid,
101+
aq.fspace_hashAS fs_hash,
102+
execution_time_with_aqo[array_length(execution_time_with_aqo,1)]AS exectime,
103+
executions_with_aqoAS execs
104+
FROMpublic.aqo_queries aqJOINpublic.aqo_query_stat aqs
105+
ONaq.query_hash=aqs.query_hash
106+
WHERE TRUE= ANY (SELECT unnest(execution_time_with_aqo)IS NOT NULL)
107+
)AS q1
108+
ORDER BY nnASC;
109+
110+
ELSE
111+
-- Show a query execution time made without any AQO advise.
112+
-- Return an average value across all executions.
113+
RETURN QUERY
114+
SELECT
115+
row_number() OVER (ORDER BY (exectime, queryid, fs_hash)DESC)AS nn,
116+
queryid, fs_hash, exectime, execs
117+
FROM (
118+
SELECT
119+
aq.query_hashAS queryid,
120+
aq.fspace_hashAS fs_hash,
121+
array_avg(execution_time_without_aqo)AS exectime,
122+
executions_without_aqoAS execs
123+
FROMpublic.aqo_queries aqJOINpublic.aqo_query_stat aqs
124+
ONaq.query_hash=aqs.query_hash
125+
WHERE TRUE= ANY (SELECT unnest(execution_time_without_aqo)IS NOT NULL)
126+
)AS q1
127+
ORDER BY (nn)ASC;
128+
END IF;
129+
END;
130+
$$ LANGUAGE plpgsql;
131+
132+
COMMENT ON FUNCTION public.show_execution_time(boolean) IS
133+
'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.';

‎expected/gucs.out

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -35,4 +35,24 @@ SELECT obj_description('public.show_cardinality_errors'::regproc::oid);
3535
Get cardinality error of queries the last time they were executed. Order queries according to an error value.
3636
(1 row)
3737

38+
SELECT obj_description('public.show_execution_time'::regproc::oid);
39+
obj_description
40+
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
41+
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.
42+
(1 row)
43+
44+
\df show_cardinality_errors
45+
List of functions
46+
Schema | Name | Result data type | Argument data types | Type
47+
--------+-------------------------+------------------------------------------------------------------------------------+---------------------+------
48+
public | show_cardinality_errors | TABLE(num bigint, id bigint, fshash bigint, error double precision, nexecs bigint) | controlled boolean | func
49+
(1 row)
50+
51+
\df show_execution_time
52+
List of functions
53+
Schema | Name | Result data type | Argument data types | Type
54+
--------+---------------------+----------------------------------------------------------------------------------------+---------------------+------
55+
public | show_execution_time | TABLE(num bigint, id bigint, fshash bigint, exec_time double precision, nexecs bigint) | controlled boolean | func
56+
(1 row)
57+
3858
DROP EXTENSION aqo;

‎expected/top_queries.out

Lines changed: 32 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -21,16 +21,32 @@ SELECT count(*) AS cnt FROM ttp WHERE cnt % 100 = 0;
2121
0
2222
(1 row)
2323

24-
SELECT num FROM top_time_queries(3);
25-
NOTICE: Top 3 execution time queries
24+
SELECT num FROM show_execution_time(true); -- Just for checking, return zero.
25+
num
26+
-----
27+
(0 rows)
28+
29+
SELECT num FROM show_execution_time(false);
2630
num
2731
-----
2832
1
2933
2
3034
(2 rows)
3135

36+
-- Without the AQO control queries with and without temp tables are logged.
37+
SELECT query_text,nexecs
38+
FROM show_execution_time(false) ce, aqo_query_texts aqt
39+
WHERE ce.id = aqt.query_hash
40+
ORDER BY (md5(query_text));
41+
query_text | nexecs
42+
------------------------------------------------------+--------
43+
SELECT count(*) AS cnt FROM ttt WHERE cnt % 100 = 0; | 1
44+
SELECT count(*) AS cnt FROM ttp WHERE cnt % 100 = 0; | 1
45+
(2 rows)
46+
3247
--
33-
-- num of query uses table t2 should be bigger than num of query uses table t1 and be the first
48+
-- num of query which uses the table t2 should be bigger than num of query which
49+
-- uses the table t1 and must be the first
3450
--
3551
CREATE TABLE t1 AS SELECT mod(gs,10) AS x, mod(gs+1,10) AS y
3652
FROM generate_series(1,1000) AS gs;
@@ -68,3 +84,16 @@ SELECT count(*) FROM show_cardinality_errors(true);
6884
0
6985
(1 row)
7086

87+
-- Fix list of logged queries
88+
SELECT query_text,nexecs
89+
FROM show_cardinality_errors(false) ce, aqo_query_texts aqt
90+
WHERE ce.id = aqt.query_hash
91+
ORDER BY (md5(query_text));
92+
query_text | nexecs
93+
------------------------------------------------------------------------------------------------+--------
94+
SELECT count(*) FROM (SELECT x, y FROM t2 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1; | 1
95+
SELECT count(*) AS cnt FROM ttt WHERE cnt % 100 = 0; | 1
96+
SELECT count(*) AS cnt FROM ttp WHERE cnt % 100 = 0; | 1
97+
SELECT count(*) FROM (SELECT x, y FROM t1 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1; | 1
98+
(4 rows)
99+

‎expected/unsupported.out

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -546,9 +546,9 @@ WHERE aqt.query_hash = cef.id
546546
ORDER BY (md5(query_text),error) DESC;
547547
error | query_text
548548
-----------+------------------------------------------------------------------------------------------------
549-
3.00e+01 | SELECT count(*) FROM (SELECT count(*) FROM t1 GROUP BY (x,y)) AS q1;
550-
1.33e+00 | SELECT count(*) FROM (SELECT * FROM t GROUP BY (x) HAVING x > 3) AS q1;
551-
1.15e+02 | SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1;
549+
7.68e-01 | SELECT count(*) FROM (SELECT count(*) FROM t1 GROUP BY (x,y)) AS q1;
550+
7.04e-02 | SELECT count(*) FROM (SELECT * FROM t GROUP BY (x) HAVING x > 3) AS q1;
551+
1.42e+00 | SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1;
552552
0.00e+00 | SELECT * FROM +
553553
| (SELECT * FROM t WHERE x < 0) AS t0 +
554554
| JOIN +
@@ -559,14 +559,14 @@ ORDER BY (md5(query_text),error) DESC;
559559
| SELECT count(*) FROM t WHERE +
560560
| x = (SELECT avg(x) FROM t t0 WHERE t0.x = t.x + 21) OR +
561561
| x IN (SELECT avg(x) FROM t t0 WHERE t0.x = t.x + 21);
562-
3.00e+01 | SELECT count(*) FROM (SELECT x, y FROM t1 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1;
562+
4.54e-01 | SELECT count(*) FROM (SELECT x, y FROM t1 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1;
563563
0.00e+00 | SELECT count(*) FROM ( +
564564
| SELECT count(*) AS x FROM ( +
565565
| SELECT count(*) FROM t1 GROUP BY (x,y) +
566566
| ) AS q1 +
567567
| ) AS q2 +
568568
| WHERE q2.x > 1;
569-
3.00e+01 | SELECT count(*) FROM (SELECT count(*) FROM t1 GROUP BY (x,x*y)) AS q1;
569+
7.68e-01 | SELECT count(*) FROM (SELECT count(*) FROM t1 GROUP BY (x,x*y)) AS q1;
570570
0.00e+00 | SELECT count(*) FROM t WHERE x = (SELECT avg(x) FROM t WHERE x = 1);
571571
0.00e+00 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +
572572
| SELECT count(*) FROM t WHERE (SELECT avg(x) FROM t t0 WHERE t0.x = t.x) = +

‎sql/gucs.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,5 +13,9 @@ EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF)
1313

1414
-- Check existence of the interface functions.
1515
SELECT obj_description('public.show_cardinality_errors'::regproc::oid);
16+
SELECT obj_description('public.show_execution_time'::regproc::oid);
17+
18+
\df show_cardinality_errors
19+
\df show_execution_time
1620

1721
DROP EXTENSION aqo;

‎sql/top_queries.sql

Lines changed: 16 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -12,10 +12,18 @@ CREATE TEMP TABLE ttt AS SELECT count(*) AS cnt FROM generate_series(1,10);
1212
CREATETABLEttpASSELECTcount(*)AS cntFROM generate_series(1,10);
1313
SELECTcount(*)AS cntFROM tttWHERE cnt %100=0;
1414
SELECTcount(*)AS cntFROM ttpWHERE cnt %100=0;
15-
SELECT numFROM top_time_queries(3);
15+
SELECT numFROM show_execution_time(true);-- Just for checking, return zero.
16+
SELECT numFROM show_execution_time(false);
17+
18+
-- Without the AQO control queries with and without temp tables are logged.
19+
SELECT query_text,nexecs
20+
FROM show_execution_time(false) ce, aqo_query_texts aqt
21+
WHEREce.id=aqt.query_hash
22+
ORDER BY (md5(query_text));
1623

1724
--
18-
-- num of query uses table t2 should be bigger than num of query uses table t1 and be the first
25+
-- num of query which uses the table t2 should be bigger than num of query which
26+
-- uses the table t1 and must be the first
1927
--
2028
CREATETABLEt1ASSELECT mod(gs,10)AS x, mod(gs+1,10)AS y
2129
FROM generate_series(1,1000)AS gs;
@@ -35,3 +43,9 @@ WHERE te.fshash = (
3543

3644
-- Should return zero
3745
SELECTcount(*)FROM show_cardinality_errors(true);
46+
47+
-- Fix list of logged queries
48+
SELECT query_text,nexecs
49+
FROM show_cardinality_errors(false) ce, aqo_query_texts aqt
50+
WHEREce.id=aqt.query_hash
51+
ORDER BY (md5(query_text));

‎t/001_pgbench.pl

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -147,9 +147,12 @@
147147
JOIN aqo_query_texts t ON (t.query_hash = v.id)
148148
WHERE v.error > 0.");
149149
note("\n Queries:\n$res\n");
150+
$res =$node->safe_psql('postgres',"SELECT * FROM public.show_execution_time(false) v");
151+
note("\n TIMES:\n$res\n");
152+
150153
$res =$node->safe_psql('postgres',
151-
"SELECT count(*) FROMtop_time_queries(10) v
152-
WHERE v.execution_time > 0.");
154+
"SELECT count(*) FROMpublic.show_execution_time(false) v
155+
WHERE v.exec_time > 0.");
153156
is($res, 3);
154157

155158
# ##############################################################################

0 commit comments

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