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

Commit9416d48

Browse files
author
Alena Rybakina
committed
Rewriting the statement_timeout test to spend less time on its execution. unfortunately,
this does not completely solve the problem of the imbalance betweenthe cost of resources expended (namely, the duration of the test) andits usefulness, since its results are ignored.We cannot completely exclude the test from the test, since it is necessaryto know about cases of test failure during the further development of the extension.
1 parent763b45b commit9416d48

File tree

2 files changed

+29
-29
lines changed

2 files changed

+29
-29
lines changed

‎expected/statement_timeout.out

Lines changed: 16 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -17,38 +17,38 @@ BEGIN
1717
END IF;
1818
END LOOP;
1919
END; $$;
20-
CREATE TABLE t AS SELECT * FROM generate_series(1,100) AS x;
20+
CREATE TABLE t AS SELECT * FROM generate_series(1,50) AS x;
2121
ANALYZE t;
2222
DELETE FROM t WHERE x > 5; -- Force optimizer to make overestimated prediction.
2323
CREATE EXTENSION IF NOT EXISTS aqo;
2424
SET aqo.join_threshold = 0;
2525
SET aqo.mode = 'learn';
2626
SET aqo.show_details = 'off';
2727
SET aqo.learn_statement_timeout = 'on';
28-
SET statement_timeout =800; -- [0.8s]
29-
SELECT *, pg_sleep(1) FROM t;
28+
SET statement_timeout =100; -- [0.1s]
29+
SELECT *, pg_sleep(0.1) FROM t;
3030
NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data.
3131
ERROR: canceling statement due to statement timeout
3232
SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;'); -- haven't any partial data
3333
check_estimated_rows
3434
----------------------
35-
100
35+
50
3636
(1 row)
3737

3838
-- Don't learn because running node has smaller cardinality than an optimizer prediction
39-
SET statement_timeout =3500;
40-
SELECT *, pg_sleep(1) FROM t;
39+
SET statement_timeout =400;
40+
SELECT *, pg_sleep(0.1) FROM t;
4141
NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data.
4242
ERROR: canceling statement due to statement timeout
4343
SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;');
4444
check_estimated_rows
4545
----------------------
46-
100
46+
50
4747
(1 row)
4848

4949
-- We have a real learning data.
50-
SET statement_timeout =10000;
51-
SELECT *, pg_sleep(1) FROM t;
50+
SET statement_timeout =8000;
51+
SELECT *, pg_sleep(0.1) FROM t;
5252
x | pg_sleep
5353
---+----------
5454
1 |
@@ -74,8 +74,8 @@ SELECT 1 FROM aqo_reset();
7474
1
7575
(1 row)
7676

77-
SET statement_timeout =800;
78-
SELECT *, pg_sleep(1) FROM t; -- Not learned
77+
SET statement_timeout =100;
78+
SELECT *, pg_sleep(0.1) FROM t; -- Not learned
7979
NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data.
8080
ERROR: canceling statement due to statement timeout
8181
SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;');
@@ -84,18 +84,18 @@ SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;');
8484
2
8585
(1 row)
8686

87-
SET statement_timeout =3500;
88-
SELECT *, pg_sleep(1) FROM t; -- Learn!
87+
SET statement_timeout =500;
88+
SELECT *, pg_sleep(0.1) FROM t; -- Learn!
8989
NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data.
9090
ERROR: canceling statement due to statement timeout
9191
SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;');
9292
check_estimated_rows
9393
----------------------
94-
3
94+
2
9595
(1 row)
9696

97-
SET statement_timeout =5500;
98-
SELECT *, pg_sleep(1) FROM t; -- Get reliable data
97+
SET statement_timeout =800;
98+
SELECT *, pg_sleep(0.1) FROM t; -- Get reliable data
9999
x | pg_sleep
100100
---+----------
101101
1 |

‎sql/statement_timeout.sql

Lines changed: 13 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,7 @@ BEGIN
1818
END LOOP;
1919
END; $$;
2020

21-
CREATETABLEtASSELECT*FROM generate_series(1,100)AS x;
21+
CREATETABLEtASSELECT*FROM generate_series(1,50)AS x;
2222
ANALYZE t;
2323
DELETEFROM tWHERE x>5;-- Force optimizer to make overestimated prediction.
2424

@@ -28,18 +28,18 @@ SET aqo.mode = 'learn';
2828
SETaqo.show_details='off';
2929
SETaqo.learn_statement_timeout='on';
3030

31-
SET statement_timeout=800;-- [0.8s]
32-
SELECT*, pg_sleep(1)FROM t;
31+
SET statement_timeout=100;-- [0.1s]
32+
SELECT*, pg_sleep(0.1)FROM t;
3333
SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;');-- haven't any partial data
3434

3535
-- Don't learn because running node has smaller cardinality than an optimizer prediction
36-
SET statement_timeout=3500;
37-
SELECT*, pg_sleep(1)FROM t;
36+
SET statement_timeout=400;
37+
SELECT*, pg_sleep(0.1)FROM t;
3838
SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;');
3939

4040
-- We have a real learning data.
41-
SET statement_timeout=10000;
42-
SELECT*, pg_sleep(1)FROM t;
41+
SET statement_timeout=8000;
42+
SELECT*, pg_sleep(0.1)FROM t;
4343
SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;');
4444

4545
-- Force to make an underestimated prediction
@@ -48,16 +48,16 @@ ANALYZE t;
4848
INSERT INTO t (x) (SELECT*FROM generate_series(3,5)AS x);
4949
SELECT1FROM aqo_reset();
5050

51-
SET statement_timeout=800;
52-
SELECT*, pg_sleep(1)FROM t;-- Not learned
51+
SET statement_timeout=100;
52+
SELECT*, pg_sleep(0.1)FROM t;-- Not learned
5353
SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;');
5454

55-
SET statement_timeout=3500;
56-
SELECT*, pg_sleep(1)FROM t;-- Learn!
55+
SET statement_timeout=500;
56+
SELECT*, pg_sleep(0.1)FROM t;-- Learn!
5757
SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;');
5858

59-
SET statement_timeout=5500;
60-
SELECT*, pg_sleep(1)FROM t;-- Get reliable data
59+
SET statement_timeout=800;
60+
SELECT*, pg_sleep(0.1)FROM t;-- Get reliable data
6161
SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;');
6262

6363
DROPTABLE t;

0 commit comments

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