1616) INSERT INTO aqo_test1 (SELECT * FROM t);
1717CREATE INDEX aqo_test1_idx_a ON aqo_test1 (a);
1818ANALYZE aqo_test1;
19+ SET aqo.mode = 'disabled';
1920CREATE TABLE tmp1 AS SELECT * FROM aqo_test0
2021WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
2122SELECT count(*) FROM tmp1;
@@ -35,6 +36,111 @@ SELECT count(*) FROM tmp1;
3536(1 row)
3637
3738DROP TABLE tmp1;
39+ EXPLAIN SELECT * FROM aqo_test0
40+ WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
41+ QUERY PLAN
42+ ----------------------------------------------------------------------------------
43+ Index Scan using aqo_test0_idx_a on aqo_test0 (cost=0.28..8.35 rows=1 width=16)
44+ Index Cond: (a < 3)
45+ Filter: ((b < 3) AND (c < 3) AND (d < 3))
46+ (3 rows)
47+
48+ EXPLAIN SELECT t1.a, t2.b, t3.c
49+ FROM aqo_test1 AS t1, aqo_test0 AS t2, aqo_test0 AS t3
50+ WHERE t1.a < 1 AND t3.b < 1 AND t2.c < 1 AND t3.d < 0 AND t1.a = t2.a AND t1.b = t3.b;
51+ QUERY PLAN
52+ ------------------------------------------------------------------------------------------------
53+ Nested Loop (cost=0.28..50.59 rows=1 width=12)
54+ Join Filter: (t1.b = t3.b)
55+ -> Nested Loop (cost=0.28..9.56 rows=1 width=12)
56+ -> Seq Scan on aqo_test1 t1 (cost=0.00..1.25 rows=1 width=8)
57+ Filter: (a < 1)
58+ -> Index Scan using aqo_test0_idx_a on aqo_test0 t2 (cost=0.28..8.30 rows=1 width=8)
59+ Index Cond: (a = t1.a)
60+ Filter: (c < 1)
61+ -> Seq Scan on aqo_test0 t3 (cost=0.00..41.02 rows=1 width=8)
62+ Filter: ((b < 1) AND (d < 0))
63+ (10 rows)
64+
65+ CREATE EXTENSION aqo;
66+ SET aqo.mode = 'intelligent';
67+ CREATE TABLE tmp1 AS SELECT * FROM aqo_test0
68+ WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
69+ SELECT count(*) FROM tmp1;
70+ count
71+ -------
72+ 3
73+ (1 row)
74+
75+ DROP TABLE tmp1;
76+ CREATE TABLE tmp1 AS SELECT t1.a, t2.b, t3.c
77+ FROM aqo_test1 AS t1, aqo_test0 AS t2, aqo_test0 AS t3
78+ WHERE t1.a < 1 AND t3.b < 1 AND t2.c < 1 AND t3.d < 0 AND t1.a = t2.a AND t1.b = t3.b;
79+ SELECT count(*) FROM tmp1;
80+ count
81+ -------
82+ 0
83+ (1 row)
84+
85+ DROP TABLE tmp1;
86+ SET aqo.mode = 'manual';
87+ UPDATE aqo_queries SET learn_aqo = true, use_aqo = true, auto_tuning = false;
88+ EXPLAIN SELECT * FROM aqo_test0
89+ WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
90+ QUERY PLAN
91+ ----------------------------------------------------------------------------------
92+ Index Scan using aqo_test0_idx_a on aqo_test0 (cost=0.28..8.35 rows=3 width=16)
93+ Index Cond: (a < 3)
94+ Filter: ((b < 3) AND (c < 3) AND (d < 3))
95+ Using aqo: true
96+ (4 rows)
97+
98+ EXPLAIN SELECT t1.a, t2.b, t3.c
99+ FROM aqo_test1 AS t1, aqo_test0 AS t2, aqo_test0 AS t3
100+ WHERE t1.a < 1 AND t3.b < 1 AND t2.c < 1 AND t3.d < 0 AND t1.a = t2.a AND t1.b = t3.b;
101+ QUERY PLAN
102+ ------------------------------------------------------------------------------------------------
103+ Nested Loop (cost=0.28..50.59 rows=1 width=12)
104+ Join Filter: (t1.b = t3.b)
105+ -> Nested Loop (cost=0.28..9.56 rows=1 width=12)
106+ -> Seq Scan on aqo_test1 t1 (cost=0.00..1.25 rows=1 width=8)
107+ Filter: (a < 1)
108+ -> Index Scan using aqo_test0_idx_a on aqo_test0 t2 (cost=0.28..8.30 rows=1 width=8)
109+ Index Cond: (a = t1.a)
110+ Filter: (c < 1)
111+ -> Seq Scan on aqo_test0 t3 (cost=0.00..41.02 rows=1 width=8)
112+ Filter: ((b < 1) AND (d < 0))
113+ Using aqo: true
114+ (11 rows)
115+
116+ SET aqo.mode = 'disabled';
117+ EXPLAIN SELECT * FROM aqo_test0
118+ WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
119+ QUERY PLAN
120+ ----------------------------------------------------------------------------------
121+ Index Scan using aqo_test0_idx_a on aqo_test0 (cost=0.28..8.35 rows=1 width=16)
122+ Index Cond: (a < 3)
123+ Filter: ((b < 3) AND (c < 3) AND (d < 3))
124+ (3 rows)
125+
126+ EXPLAIN SELECT t1.a, t2.b, t3.c
127+ FROM aqo_test1 AS t1, aqo_test0 AS t2, aqo_test0 AS t3
128+ WHERE t1.a < 1 AND t3.b < 1 AND t2.c < 1 AND t3.d < 0 AND t1.a = t2.a AND t1.b = t3.b;
129+ QUERY PLAN
130+ ------------------------------------------------------------------------------------------------
131+ Nested Loop (cost=0.28..50.59 rows=1 width=12)
132+ Join Filter: (t1.b = t3.b)
133+ -> Nested Loop (cost=0.28..9.56 rows=1 width=12)
134+ -> Seq Scan on aqo_test1 t1 (cost=0.00..1.25 rows=1 width=8)
135+ Filter: (a < 1)
136+ -> Index Scan using aqo_test0_idx_a on aqo_test0 t2 (cost=0.28..8.30 rows=1 width=8)
137+ Index Cond: (a = t1.a)
138+ Filter: (c < 1)
139+ -> Seq Scan on aqo_test0 t3 (cost=0.00..41.02 rows=1 width=8)
140+ Filter: ((b < 1) AND (d < 0))
141+ (10 rows)
142+
143+ DROP EXTENSION aqo;
38144DROP INDEX aqo_test0_idx_a;
39145DROP TABLE aqo_test0;
40146DROP INDEX aqo_test1_idx_a;