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

Commit57fb3a7

Browse files
committed
Change tests: AQO report status only in explain verbose mode.
1 parentd761cdd commit57fb3a7

File tree

6 files changed

+59
-49
lines changed

6 files changed

+59
-49
lines changed

‎expected/aqo_controlled.out

Lines changed: 5 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -199,8 +199,7 @@ WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
199199
Index Scan using aqo_test0_idx_a on aqo_test0
200200
Index Cond: (a < 3)
201201
Filter: ((b < 3) AND (c < 3) AND (d < 3))
202-
Using aqo: true
203-
(4 rows)
202+
(3 rows)
204203

205204
EXPLAIN (COSTS FALSE)
206205
SELECT * FROM aqo_test0
@@ -210,8 +209,7 @@ WHERE a < 5 AND b < 5 AND c < 5 AND d < 5;
210209
Index Scan using aqo_test0_idx_a on aqo_test0
211210
Index Cond: (a < 5)
212211
Filter: ((b < 5) AND (c < 5) AND (d < 5))
213-
Using aqo: true
214-
(4 rows)
212+
(3 rows)
215213

216214
EXPLAIN (COSTS FALSE)
217215
SELECT t1.a, t2.b FROM aqo_test0 AS t1, aqo_test0 AS t2
@@ -224,8 +222,7 @@ WHERE t1.a < 1 AND t1.b < 1 AND t2.c < 1 AND t2.d < 1;
224222
Filter: (b < 1)
225223
-> Seq Scan on aqo_test0 t2
226224
Filter: ((c < 1) AND (d < 1))
227-
Using aqo: true
228-
(7 rows)
225+
(6 rows)
229226

230227
EXPLAIN (COSTS FALSE)
231228
SELECT t1.a, t2.b FROM aqo_test1 AS t1, aqo_test0 AS t2
@@ -256,8 +253,7 @@ WHERE t1.a < 1 AND t3.b < 1 AND t2.c < 1 AND t3.d < 0 AND t1.a = t2.a AND t1.b =
256253
Filter: (c < 1)
257254
-> Seq Scan on aqo_test0 t3
258255
Filter: ((b < 1) AND (d < 0))
259-
Using aqo: true
260-
(11 rows)
256+
(10 rows)
261257

262258
EXPLAIN (COSTS FALSE)
263259
SELECT t1.a AS a, t2.a AS b, t3.a AS c
@@ -274,8 +270,7 @@ WHERE t1.a = t2.b AND t2.a = t3.b;
274270
-> Seq Scan on aqo_test1 t2
275271
-> Hash
276272
-> Seq Scan on aqo_test1 t3
277-
Using aqo: true
278-
(10 rows)
273+
(9 rows)
279274

280275
EXPLAIN (COSTS FALSE)
281276
SELECT t1.a AS a, t2.a AS b, t3.a AS c

‎expected/aqo_disabled.out

Lines changed: 2 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -92,8 +92,7 @@ WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
9292
Index Scan using aqo_test0_idx_a on aqo_test0 (cost=0.28..8.35 rows=3 width=16)
9393
Index Cond: (a < 3)
9494
Filter: ((b < 3) AND (c < 3) AND (d < 3))
95-
Using aqo: true
96-
(4 rows)
95+
(3 rows)
9796

9897
EXPLAIN SELECT t1.a, t2.b, t3.c
9998
FROM aqo_test1 AS t1, aqo_test0 AS t2, aqo_test0 AS t3
@@ -110,8 +109,7 @@ WHERE t1.a < 1 AND t3.b < 1 AND t2.c < 1 AND t3.d < 0 AND t1.a = t2.a AND t1.b =
110109
Filter: (c < 1)
111110
-> Seq Scan on aqo_test0 t3 (cost=0.00..41.02 rows=1 width=8)
112111
Filter: ((b < 1) AND (d < 0))
113-
Using aqo: true
114-
(11 rows)
112+
(10 rows)
115113

116114
SET aqo.mode = 'disabled';
117115
EXPLAIN SELECT * FROM aqo_test0

‎expected/aqo_forced.out

Lines changed: 2 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -65,8 +65,7 @@ WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
6565
Index Scan using aqo_test0_idx_a on aqo_test0
6666
Index Cond: (a < 3)
6767
Filter: ((b < 3) AND (c < 3) AND (d < 3))
68-
Using aqo: true
69-
(4 rows)
68+
(3 rows)
7069

7170
EXPLAIN (COSTS FALSE)
7271
SELECT * FROM aqo_test0
@@ -76,8 +75,7 @@ WHERE a < 5 AND b < 5 AND c < 5 AND d < 5;
7675
Index Scan using aqo_test0_idx_a on aqo_test0
7776
Index Cond: (a < 5)
7877
Filter: ((b < 5) AND (c < 5) AND (d < 5))
79-
Using aqo: true
80-
(4 rows)
78+
(3 rows)
8179

8280
DROP INDEX aqo_test0_idx_a;
8381
DROP TABLE aqo_test0;

‎expected/aqo_intelligent.out

Lines changed: 38 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -410,8 +410,7 @@ WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
410410
Index Scan using aqo_test0_idx_a on aqo_test0 (cost=0.28..8.35 rows=3 width=16)
411411
Index Cond: (a < 3)
412412
Filter: ((b < 3) AND (c < 3) AND (d < 3))
413-
Using aqo: true
414-
(4 rows)
413+
(3 rows)
415414

416415
EXPLAIN SELECT * FROM aqo_test0
417416
WHERE a < 4 AND b < 4 AND c < 4 AND d < 4;
@@ -420,8 +419,7 @@ WHERE a < 4 AND b < 4 AND c < 4 AND d < 4;
420419
Index Scan using aqo_test0_idx_a on aqo_test0 (cost=0.28..8.38 rows=4 width=16)
421420
Index Cond: (a < 4)
422421
Filter: ((b < 4) AND (c < 4) AND (d < 4))
423-
Using aqo: true
424-
(4 rows)
422+
(3 rows)
425423

426424
EXPLAIN SELECT * FROM aqo_test0
427425
WHERE a < 5 AND b < 5 AND c < 5 AND d < 5;
@@ -430,8 +428,7 @@ WHERE a < 5 AND b < 5 AND c < 5 AND d < 5;
430428
Index Scan using aqo_test0_idx_a on aqo_test0 (cost=0.28..8.40 rows=5 width=16)
431429
Index Cond: (a < 5)
432430
Filter: ((b < 5) AND (c < 5) AND (d < 5))
433-
Using aqo: true
434-
(4 rows)
431+
(3 rows)
435432

436433
EXPLAIN SELECT t1.a, t2.b FROM aqo_test0 AS t1, aqo_test0 AS t2
437434
WHERE t1.a < 1 AND t1.b < 1 AND t2.c < 1 AND t2.d < 1;
@@ -443,8 +440,7 @@ WHERE t1.a < 1 AND t1.b < 1 AND t2.c < 1 AND t2.d < 1;
443440
Filter: (b < 1)
444441
-> Seq Scan on aqo_test0 t2 (cost=0.00..41.02 rows=1 width=4)
445442
Filter: ((c < 1) AND (d < 1))
446-
Using aqo: true
447-
(7 rows)
443+
(6 rows)
448444

449445
EXPLAIN SELECT t1.a, t2.b FROM aqo_test1 AS t1, aqo_test0 AS t2
450446
WHERE t1.a < 1 AND t2.b < 1 AND t2.c < 1 AND t2.d < 1 AND t1.a = t2.a;
@@ -456,8 +452,7 @@ WHERE t1.a < 1 AND t2.b < 1 AND t2.c < 1 AND t2.d < 1 AND t1.a = t2.a;
456452
-> Index Scan using aqo_test0_idx_a on aqo_test0 t2 (cost=0.28..8.30 rows=1 width=8)
457453
Index Cond: (a = t1.a)
458454
Filter: ((b < 1) AND (c < 1) AND (d < 1))
459-
Using aqo: true
460-
(7 rows)
455+
(6 rows)
461456

462457
EXPlAIN SELECT t1.a, t2.b, t3.c
463458
FROM aqo_test1 AS t1, aqo_test0 AS t2, aqo_test0 AS t3
@@ -474,8 +469,7 @@ WHERE t1.a < 1 AND t3.b < 1 AND t2.c < 1 AND t3.d < 0 AND t1.a = t2.a AND t1.b =
474469
Filter: (c < 1)
475470
-> Seq Scan on aqo_test0 t3 (cost=0.00..41.02 rows=1 width=8)
476471
Filter: ((b < 1) AND (d < 0))
477-
Using aqo: true
478-
(11 rows)
472+
(10 rows)
479473

480474
EXPLAIN SELECT t1.a AS a, t2.a AS b, t3.a AS c
481475
FROM aqo_test1 AS t1, aqo_test1 AS t2, aqo_test1 AS t3
@@ -491,8 +485,7 @@ WHERE t1.a = t2.b AND t2.a = t3.b;
491485
-> Seq Scan on aqo_test1 t2 (cost=0.00..1.20 rows=20 width=8)
492486
-> Hash (cost=1.20..1.20 rows=20 width=8)
493487
-> Seq Scan on aqo_test1 t3 (cost=0.00..1.20 rows=20 width=8)
494-
Using aqo: true
495-
(10 rows)
488+
(9 rows)
496489

497490
EXPLAIN SELECT t1.a AS a, t2.a AS b, t3.a AS c, t4.a AS d
498491
FROM aqo_test1 AS t1, aqo_test1 AS t2, aqo_test1 AS t3, aqo_test1 AS t4
@@ -512,8 +505,38 @@ WHERE t1.a = t2.b AND t2.a = t3.b AND t3.a = t4.b;
512505
-> Seq Scan on aqo_test1 t3 (cost=0.00..1.20 rows=20 width=8)
513506
-> Hash (cost=1.20..1.20 rows=20 width=8)
514507
-> Seq Scan on aqo_test1 t4 (cost=0.00..1.20 rows=20 width=8)
508+
(13 rows)
509+
510+
EXPLAIN verbose SELECT t1.a AS a, t2.a AS b, t3.a AS c, t4.a AS d
511+
FROM aqo_test1 AS t1, aqo_test1 AS t2, aqo_test1 AS t3, aqo_test1 AS t4
512+
WHERE t1.a = t2.b AND t2.a = t3.b AND t3.a = t4.b;
513+
QUERY PLAN
514+
--------------------------------------------------------------------------------------------
515+
Hash Join (cost=4.35..6.37 rows=20 width=16)
516+
Output: t1.a, t2.a, t3.a, t4.a
517+
Hash Cond: (t3.a = t4.b)
518+
-> Hash Join (cost=2.90..4.65 rows=20 width=12)
519+
Output: t1.a, t2.a, t3.a
520+
Hash Cond: (t2.a = t3.b)
521+
-> Hash Join (cost=1.45..2.92 rows=20 width=8)
522+
Output: t1.a, t2.a
523+
Hash Cond: (t1.a = t2.b)
524+
-> Seq Scan on public.aqo_test1 t1 (cost=0.00..1.20 rows=20 width=4)
525+
Output: t1.a, t1.b
526+
-> Hash (cost=1.20..1.20 rows=20 width=8)
527+
Output: t2.a, t2.b
528+
-> Seq Scan on public.aqo_test1 t2 (cost=0.00..1.20 rows=20 width=8)
529+
Output: t2.a, t2.b
530+
-> Hash (cost=1.20..1.20 rows=20 width=8)
531+
Output: t3.a, t3.b
532+
-> Seq Scan on public.aqo_test1 t3 (cost=0.00..1.20 rows=20 width=8)
533+
Output: t3.a, t3.b
534+
-> Hash (cost=1.20..1.20 rows=20 width=8)
535+
Output: t4.a, t4.b
536+
-> Seq Scan on public.aqo_test1 t4 (cost=0.00..1.20 rows=20 width=8)
537+
Output: t4.a, t4.b
515538
Using aqo: true
516-
(14 rows)
539+
(24 rows)
517540

518541
DROP INDEX aqo_test0_idx_a;
519542
DROP TABLE aqo_test0;

‎expected/aqo_learn.out

Lines changed: 8 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -329,8 +329,7 @@ WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
329329
Index Scan using aqo_test0_idx_a on aqo_test0 (cost=0.28..8.35 rows=3 width=16)
330330
Index Cond: (a < 3)
331331
Filter: ((b < 3) AND (c < 3) AND (d < 3))
332-
Using aqo: true
333-
(4 rows)
332+
(3 rows)
334333

335334
EXPLAIN SELECT * FROM aqo_test0
336335
WHERE a < 4 AND b < 4 AND c < 4 AND d < 4;
@@ -339,8 +338,7 @@ WHERE a < 4 AND b < 4 AND c < 4 AND d < 4;
339338
Index Scan using aqo_test0_idx_a on aqo_test0 (cost=0.28..8.38 rows=4 width=16)
340339
Index Cond: (a < 4)
341340
Filter: ((b < 4) AND (c < 4) AND (d < 4))
342-
Using aqo: true
343-
(4 rows)
341+
(3 rows)
344342

345343
EXPLAIN SELECT * FROM aqo_test0
346344
WHERE a < 5 AND b < 5 AND c < 5 AND d < 5;
@@ -349,8 +347,7 @@ WHERE a < 5 AND b < 5 AND c < 5 AND d < 5;
349347
Index Scan using aqo_test0_idx_a on aqo_test0 (cost=0.28..8.40 rows=5 width=16)
350348
Index Cond: (a < 5)
351349
Filter: ((b < 5) AND (c < 5) AND (d < 5))
352-
Using aqo: true
353-
(4 rows)
350+
(3 rows)
354351

355352
EXPLAIN SELECT t1.a, t2.b FROM aqo_test0 AS t1, aqo_test0 AS t2
356353
WHERE t1.a < 1 AND t1.b < 1 AND t2.c < 1 AND t2.d < 1;
@@ -362,8 +359,7 @@ WHERE t1.a < 1 AND t1.b < 1 AND t2.c < 1 AND t2.d < 1;
362359
Filter: (b < 1)
363360
-> Seq Scan on aqo_test0 t2 (cost=0.00..41.02 rows=1 width=4)
364361
Filter: ((c < 1) AND (d < 1))
365-
Using aqo: true
366-
(7 rows)
362+
(6 rows)
367363

368364
EXPLAIN SELECT t1.a, t2.b FROM aqo_test1 AS t1, aqo_test0 AS t2
369365
WHERE t1.a < 1 AND t2.b < 1 AND t2.c < 1 AND t2.d < 1 AND t1.a = t2.a;
@@ -375,8 +371,7 @@ WHERE t1.a < 1 AND t2.b < 1 AND t2.c < 1 AND t2.d < 1 AND t1.a = t2.a;
375371
-> Index Scan using aqo_test0_idx_a on aqo_test0 t2 (cost=0.28..8.30 rows=1 width=8)
376372
Index Cond: (a = t1.a)
377373
Filter: ((b < 1) AND (c < 1) AND (d < 1))
378-
Using aqo: true
379-
(7 rows)
374+
(6 rows)
380375

381376
EXPlAIN SELECT t1.a, t2.b, t3.c
382377
FROM aqo_test1 AS t1, aqo_test0 AS t2, aqo_test0 AS t3
@@ -393,8 +388,7 @@ WHERE t1.a < 1 AND t3.b < 1 AND t2.c < 1 AND t3.d < 0 AND t1.a = t2.a AND t1.b =
393388
Filter: (c < 1)
394389
-> Seq Scan on aqo_test0 t3 (cost=0.00..41.02 rows=1 width=8)
395390
Filter: ((b < 1) AND (d < 0))
396-
Using aqo: true
397-
(11 rows)
391+
(10 rows)
398392

399393
EXPLAIN SELECT t1.a AS a, t2.a AS b, t3.a AS c
400394
FROM aqo_test1 AS t1, aqo_test1 AS t2, aqo_test1 AS t3
@@ -410,8 +404,7 @@ WHERE t1.a = t2.b AND t2.a = t3.b;
410404
-> Seq Scan on aqo_test1 t2 (cost=0.00..1.20 rows=20 width=8)
411405
-> Hash (cost=1.20..1.20 rows=20 width=8)
412406
-> Seq Scan on aqo_test1 t3 (cost=0.00..1.20 rows=20 width=8)
413-
Using aqo: true
414-
(10 rows)
407+
(9 rows)
415408

416409
EXPLAIN SELECT t1.a AS a, t2.a AS b, t3.a AS c, t4.a AS d
417410
FROM aqo_test1 AS t1, aqo_test1 AS t2, aqo_test1 AS t3, aqo_test1 AS t4
@@ -431,8 +424,7 @@ WHERE t1.a = t2.b AND t2.a = t3.b AND t3.a = t4.b;
431424
-> Seq Scan on aqo_test1 t3 (cost=0.00..1.20 rows=20 width=8)
432425
-> Hash (cost=1.20..1.20 rows=20 width=8)
433426
-> Seq Scan on aqo_test1 t4 (cost=0.00..1.20 rows=20 width=8)
434-
Using aqo: true
435-
(14 rows)
427+
(13 rows)
436428

437429
DROP INDEX aqo_test0_idx_a;
438430
DROP TABLE aqo_test0;

‎sql/aqo_intelligent.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -207,6 +207,10 @@ EXPLAIN SELECT t1.a AS a, t2.a AS b, t3.a AS c, t4.a AS d
207207
FROM aqo_test1AS t1, aqo_test1AS t2, aqo_test1AS t3, aqo_test1AS t4
208208
WHEREt1.a=t2.bANDt2.a=t3.bANDt3.a=t4.b;
209209

210+
EXPLAIN verboseSELECTt1.aAS a,t2.aAS b,t3.aAS c,t4.aAS d
211+
FROM aqo_test1AS t1, aqo_test1AS t2, aqo_test1AS t3, aqo_test1AS t4
212+
WHEREt1.a=t2.bANDt2.a=t3.bANDt3.a=t4.b;
213+
210214
DROPINDEX aqo_test0_idx_a;
211215
DROPTABLE aqo_test0;
212216

0 commit comments

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