8000 Introduce the feature_subspace regression test. · postgrespro/aqo@2785297 · GitHub
[go: up one dir, main page]
Skip to content
8000

Commit 2785297

Browse files
danolivoAlena Rybakina
danolivo
authored and
Alena Rybakina
committed
Introduce the feature_subspace regression test.
Search in neighbour classes disclosed some issues which we have been ignoring for a long time. But now we should fix them to get a practically usable tool. These problems mostly related to a subspace encoding algorithm.
1 parent 96a7c2d commit 2785297

File tree

5 files changed

+113
-1
lines changed

5 files changed

+113
-1
lines changed
10000

‎Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -28,7 +28,8 @@ REGRESS = aqo_disabled \
2828
temp_tables \
2929
top_queries \
3030
relocatable\
31-
look_a_like
31+
look_a_like \
32+
feature_subspace
3233

3334
fdw_srcdir = $(top_srcdir)/contrib/postgres_fdw
3435
stat_srcdir = $(top_srcdir)/contrib/pg_stat_statements

‎expected/feature_subspace.out

Lines changed: 72 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,72 @@
1+
-- This test related to some issues on feature subspace calculation
2+
CREATE EXTENSION aqo;
3+
SET aqo.mode = 'learn';
4+
SET aqo.join_threshold = 0;
5+
SET aqo.show_details = 'on';
6+
CREATE TABLE a AS (SELECT gs AS x FROM generate_series(1,10) AS gs);
7+
CREATE TABLE b AS (SELECT gs AS x FROM generate_series(1,100) AS gs);
8+
--
9+
-- A LEFT JOIN B isn't equal B LEFT JOIN A.
10+
--
11+
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
12+
SELECT * FROM a LEFT JOIN b USING (x);
13+
QUERY PLAN
14+
-----------------------------------------------------
15+
Merge Left Join (actual rows=10 loops=1)
16+
AQO not used
17+
Merge Cond: (a.x = b.x)
18+
-> Sort (actual rows=10 loops=1)
19+
AQO not used
20+
Sort Key: a.x
21+
Sort Method: quicksort Memory: 25kB
22+
-> Seq Scan on a (actual rows=10 loops=1)
23+
AQO not used
24+
-> Sort (actual rows=11 loops=1)
25+
AQO not used
26+
Sort Key: b.x
27+
Sort Method: quicksort Memory: 30kB
28+
-> Seq Scan on b (actual rows=100 loops=1)
29+
AQO not used
30+
Using aqo: true
31+
AQO mode: LEARN
32+
JOINS: 0
33+
(18 rows)
34+
35+
-- TODO: Using method of other classes neighbours we get a bad estimation.
36+
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
37+
SELECT * FROM b LEFT JOIN a USING (x);
38+
QUERY PLAN
39+
------------------------------------------------------
40+
Hash Left Join (actual rows=100 loops=1)
41+
AQO: rows=10, error=-900%
42+
Hash Cond: (b.x = a.x)
43+
-> Seq Scan on b (actual rows=100 loops=1)
44+
AQO: rows=100, error=0%
45+
-> Hash (actual rows=10 loops=1)
46+
AQO not used
47+
Buckets: 1024 Batches: 1 Memory Usage: 9kB
48+
-> Seq Scan on a (actual rows=10 loops=1)
49+
AQO: rows=10, error=0%
50+
Using aqo: true
51+
AQO mode: LEARN
52+
JOINS: 0
53+
(13 rows)
54+
55+
-- Look into the reason: two JOINs from different classes have the same FSS.
56+
SELECT to_char(d1.targets[1], 'FM999.00') AS target FROM aqo_data d1
57+
JOIN aqo_data d2 ON (d1.fs <> d2.fs AND d1.fss = d2.fss)
58+
WHERE 'a'::regclass = ANY (d1.oids) AND 'b'::regclass = ANY (d1.oids);
59+
target
60+
--------
61+
2.30
62+
4.61
63+
(2 rows)
64+
65+
DROP TABLE a,b CASCADE;
66+
SELECT true FROM aqo_reset();
67+
bool
68+
------
69+
t
70+
(1 row)
71+
72+
DROP EXTENSION aqo;

‎expected/look_a_like.out

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -230,4 +230,11 @@ WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%';
230230
JOINS: 0
231231
(19 rows)
232232

233+
DROP TABLE a,b CASCADE;
234+
SELECT true FROM aqo_reset();
235+
bool
236+
------
237+
t
238+
(1 row)
239+
233240
DROP EXTENSION aqo CASCADE;

‎sql/feature_subspace.sql

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
-- This test related to some issues on feature subspace calculation
2+
3+
CREATE EXTENSION aqo;
4+
5+
SET aqo.mode = 'learn';
6+
SET aqo.join_threshold = 0;
7+
SET aqo.show_details = 'on';
8+
9+
CREATE TABLE a AS (SELECT gs AS x FROM generate_series(1,10) AS gs);
10+
CREATE TABLE b AS (SELECT gs AS x FROM generate_series(1,100) AS gs);
11+
12+
--
13+
-- A LEFT JOIN B isn't equal B LEFT JOIN A.
14+
--
15+
16+
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
17+
SELECT * FROM a LEFT JOIN b USING (x);
18+
19+
-- TODO: Using method of other classes neighbours we get a bad estimation.
20+
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
21+
SELECT * FROM b LEFT JOIN a USING (x);
22+
23+
-- Look into the reason: two JOINs from different classes have the same FSS.
24+
SELECT to_char(d1.targets[1], 'FM999.00') AS target FROM aqo_data d1
25+
JOIN aqo_data d2 ON (d1.fs <> d2.fs AND d1.fss = d2.fss)
26+
WHERE 'a'::regclass = ANY (d1.oids) AND 'b'::regclass = ANY (d1.oids);
27+
28+
DROP TABLE a,b CASCADE;
29+
SELECT true FROM aqo_reset();
30+
DROP EXTENSION aqo;

‎sql/look_a_like.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -69,4 +69,6 @@ FROM expln('
6969
SELECT x,y FROM A,B WHERE x < 10 and y > 10 AND A.x = B.y;') AS str
7070
WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%';
7171

72+
DROP TABLE a,b CASCADE;
73+
SELECT true FROM aqo_reset();
7274
DROP EXTENSION aqo CASCADE;

0 commit comments

Comments
 (0)
0