|
| 1 | +CREATE TABLE aqo_test0(a int, b int, c int, d int); |
| 2 | +WITH RECURSIVE t(a, b, c, d) |
| 3 | +AS ( |
| 4 | + VALUES (0, 0, 0, 0) |
| 5 | + UNION ALL |
| 6 | + SELECT t.a + 1, t.b + 1, t.c + 1, t.d + 1 FROM t WHERE t.a < 2000 |
| 7 | +) INSERT INTO aqo_test0 (SELECT * FROM t); |
| 8 | +CREATE INDEX aqo_test0_idx_a ON aqo_test0 (a); |
| 9 | +ANALYZE aqo_test0; |
| 10 | +CREATE TABLE aqo_test1(a int, b int); |
| 11 | +WITH RECURSIVE t(a, b) |
| 12 | +AS ( |
| 13 | + VALUES (1, 2) |
| 14 | + UNION ALL |
| 15 | + SELECT t.a + 1, t.b + 1 FROM t WHERE t.a < 20 |
| 16 | +) INSERT INTO aqo_test1 (SELECT * FROM t); |
| 17 | +CREATE INDEX aqo_test1_idx_a ON aqo_test1 (a); |
| 18 | +ANALYZE aqo_test1; |
| 19 | +CREATE EXTENSION aqo; |
| 20 | +SET aqo.mode = 'manual'; |
| 21 | +EXPLAIN (COSTS FALSE) |
| 22 | +SELECT * FROM aqo_test0 |
| 23 | +WHERE a < 3 AND b < 3 AND c < 3 AND d < 3; |
| 24 | + QUERY PLAN |
| 25 | +----------------------------------------------- |
| 26 | + Index Scan using aqo_test0_idx_a on aqo_test0 |
| 27 | + Index Cond: (a < 3) |
| 28 | + Filter: ((b < 3) AND (c < 3) AND (d < 3)) |
| 29 | +(3 rows) |
| 30 | + |
| 31 | +EXPLAIN (COSTS FALSE) |
| 32 | +SELECT * FROM aqo_test0 |
| 33 | +WHERE a < 5 AND b < 5 AND c < 5 AND d < 5; |
| 34 | + QUERY PLAN |
| 35 | +----------------------------------------------- |
| 36 | + Index Scan using aqo_test0_idx_a on aqo_test0 |
| 37 | + Index Cond: (a < 5) |
| 38 | + Filter: ((b < 5) AND (c < 5) AND (d < 5)) |
| 39 | +(3 rows) |
| 40 | + |
| 41 | +SET aqo.mode = 'forced'; |
| 42 | +CREATE TABLE tmp1 AS SELECT * FROM aqo_test0 |
| 43 | +WHERE a < 3 AND b < 3 AND c < 3 AND d < 3; |
| 44 | +SELECT count(*) FROM tmp1; |
| 45 | + count |
| 46 | +------- |
| 47 | + 3 |
| 48 | +(1 row) |
| 49 | + |
| 50 | +DROP TABLE tmp1; |
| 51 | +CREATE TABLE tmp1 AS SELECT * FROM aqo_test0 |
| 52 | +WHERE a < 5 AND b < 5 AND c < 5 AND d < 5; |
| 53 | +SELECT count(*) FROM tmp1; |
| 54 | + count |
| 55 | +------- |
| 56 | + 5 |
| 57 | +(1 row) |
| 58 | + |
| 59 | +DROP TABLE tmp1; |
| 60 | +EXPLAIN (COSTS FALSE) |
| 61 | +SELECT * FROM aqo_test0 |
| 62 | +WHERE a < 3 AND b < 3 AND c < 3 AND d < 3; |
| 63 | + QUERY PLAN |
| 64 | +----------------------------------------------- |
| 65 | + Index Scan using aqo_test0_idx_a on aqo_test0 |
| 66 | + Index Cond: (a < 3) |
| 67 | + Filter: ((b < 3) AND (c < 3) AND (d < 3)) |
| 68 | +(3 rows) |
| 69 | + |
| 70 | +EXPLAIN (COSTS FALSE) |
| 71 | +SELECT * FROM aqo_test0 |
| 72 | +WHERE a < 5 AND b < 5 AND c < 5 AND d < 5; |
| 73 | + QUERY PLAN |
| 74 | +----------------------------------------------- |
| 75 | + Index Scan using aqo_test0_idx_a on aqo_test0 |
| 76 | + Index Cond: (a < 5) |
| 77 | + Filter: ((b < 5) AND (c < 5) AND (d < 5)) |
| 78 | +(3 rows) |
| 79 | + |
| 80 | +DROP INDEX aqo_test0_idx_a; |
| 81 | +DROP TABLE aqo_test0; |
| 82 | +DROP INDEX aqo_test1_idx_a; |
| 83 | +DROP TABLE aqo_test1; |
| 84 | +DROP EXTENSION aqo; |
0 commit comments