|
1 | 1 | CREATE EXTENSION aqo; |
| 2 | +SELECT true FROM aqo_reset(); |
| 3 | + ?column? |
| 4 | +---------- |
| 5 | + t |
| 6 | +(1 row) |
| 7 | + |
2 | 8 | SET aqo.join_threshold = 0; |
3 | 9 | SET aqo.mode = 'learn'; |
4 | 10 | SET aqo.show_details = 'on'; |
@@ -56,28 +62,25 @@ SELECT str AS result |
56 | 62 | FROM expln(' |
57 | 63 | SELECT x1,y1 FROM A LEFT JOIN b ON A.x1 = B.y1 WHERE x1 = 5 AND x2 = 5;') AS str |
58 | 64 | WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%' and str NOT LIKE '%Sort Method%'; |
59 | | -result |
60 | | ------------------------------------------------------------- |
61 | | -Hash Left Join (actual rows=10000 loops=1) |
| 65 | + result |
| 66 | +-------------------------------------------------------- |
| 67 | +Nested Loop Left Join (actual rows=10000 loops=1) |
62 | 68 | AQO not used |
63 | 69 | Output: a.x1, b.y1 |
64 | | - Hash Cond: (a.x1 = b.y1) |
65 | 70 | -> Seq Scan on public.a (actual rows=100 loops=1) |
66 | 71 | AQO: rows=100, error=0% |
67 | 72 | Output: a.x1, a.x2, a.x3 |
68 | 73 | Filter: ((a.x1 = 5) AND (a.x2 = 5)) |
69 | 74 | Rows Removed by Filter: 900 |
70 | | - -> Hash (actual rows=100 loops=1) |
71 | | - Output: b.y1 |
72 | | - -> Seq Scan on public.b (actual rows=100 loops=1) |
73 | | - AQO: rows=100, error=0% |
74 | | - Output: b.y1 |
75 | | - Filter: (b.y1 = 5) |
76 | | - Rows Removed by Filter: 900 |
| 75 | + -> Seq Scan on public.b (actual rows=100 loops=100) |
| 76 | + AQO: rows=100, error=0% |
| 77 | + Output: b.y1, b.y2, b.y3 |
| 78 | + Filter: (b.y1 = 5) |
| 79 | + Rows Removed by Filter: 900 |
77 | 80 | Using aqo: true |
78 | 81 | AQO mode: LEARN |
79 | 82 | JOINS: 0 |
80 | | -(19 rows) |
| 83 | +(16 rows) |
81 | 84 |
|
82 | 85 | SELECT str AS result |
83 | 86 | FROM expln(' |
@@ -516,29 +519,29 @@ SELECT str AS result |
516 | 519 | FROM expln(' |
517 | 520 | SELECT * FROM (A LEFT JOIN B ON A.x1 = B.y1) sc left join C on sc.x1=C.z1;') AS str |
518 | 521 | WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%' and str NOT LIKE '%Sort Method%'; |
519 | | -result |
520 | | -------------------------------------------------------------------------- |
521 | | - HashRight Join (actual rows=10000000 loops=1) |
522 | | - AQO: rows=1, error=-999999900% |
| 522 | + result |
| 523 | +------------------------------------------------------------------- |
| 524 | + HashLeft Join (actual rows=10000000 loops=1) |
| 525 | + AQO not used |
523 | 526 | Output: a.x1, a.x2, a.x3, b.y1, b.y2, b.y3, c.z1, c.z2, c.z3 |
524 | | - Hash Cond: (b.y1 =a.x1) |
525 | | - ->Seq Scan on public.b(actual rows=1000 loops=1) |
526 | | - AQO: rows=1000, error=0% |
527 | | - Output: b.y1, b.y2, b.y3 |
528 | | -->Hash(actual rows=100000 loops=1) |
529 | | -Output: a.x1, a.x2, a.x3, c.z1, c.z2, c.z3 |
530 | | --> Hash Left Join (actualrows=100000 loops=1) |
531 | | -AQO: rows=1, error=-9999900% |
532 | | - Output: a.x1, a.x2, a.x3, c.z1, c.z2, c.z3 |
533 | | -Hash Cond: (a.x1 = c.z1) |
534 | | - -> Seq Scan on public.a (actual rows=1000 loops=1) |
| 527 | + Hash Cond: (a.x1 =c.z1) |
| 528 | + ->Hash Left Join(actual rows=100000 loops=1) |
| 529 | + AQO not used |
| 530 | + Output:a.x1, a.x2, a.x3,b.y1, b.y2, b.y3 |
| 531 | +HashCond: (a.x1 = b.y1) |
| 532 | +-> Seq Scan on public.a (actual rows=1000 loops=1) |
| 533 | + AQO:rows=1000, error=0% |
| 534 | +Output: a.x1, a.x2, a.x3 |
| 535 | +-> Hash (actual rows=1000 loops=1) |
| 536 | +Output: b.y1, b.y2, b.y3 |
| 537 | + -> Seq Scan on public.b (actual rows=1000 loops=1) |
535 | 538 | AQO: rows=1000, error=0% |
536 | | - Output:a.x1, a.x2, a.x3 |
537 | | --> Hash (actual rows=1000 loops=1) |
538 | | -Output: c.z1, c.z2, c.z3 |
539 | | --> Seq Scan on public.c (actual rows=1000 loops=1) |
540 | | -AQO: rows=1000, error=0% |
541 | | -Output: c.z1, c.z2, c.z3 |
| 539 | + Output:b.y1, b.y2, b.y3 |
| 540 | + -> Hash (actual rows=1000 loops=1) |
| 541 | + Output: c.z1, c.z2, c.z3 |
| 542 | + -> Seq Scan on public.c (actual rows=1000 loops=1) |
| 543 | + AQO: rows=1000, error=0% |
| 544 | + Output: c.z1, c.z2, c.z3 |
542 | 545 | Using aqo: true |
543 | 546 | AQO mode: LEARN |
544 | 547 | JOINS: 1 |
|