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

Commit3374fe7

Browse files
committed
Introduce the feature_subspace regression test.
Search in neighbour classes disclosed some issues which we have been ignoringfor 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 parent9e0c3e3 commit3374fe7

File tree

5 files changed

+113
-1
lines changed

5 files changed

+113
-1
lines changed

‎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+
SETaqo.mode='learn';
6+
SETaqo.join_threshold=0;
7+
SETaqo.show_details='on';
8+
9+
CREATETABLEaAS (SELECT gsAS xFROM generate_series(1,10)AS gs);
10+
CREATETABLEbAS (SELECT gsAS xFROM 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 aLEFT 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 bLEFT 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 targetFROM aqo_data d1
25+
JOIN aqo_data d2ON (d1.fs<>d2.fsANDd1.fss=d2.fss)
26+
WHERE'a'::regclass= ANY (d1.oids)AND'b'::regclass= ANY (d1.oids);
27+
28+
DROPTABLE a,b CASCADE;
29+
SELECT trueFROM 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 NOTLIKE'Query Identifier%'and str NOTLIKE'%Memory%';
7171

72+
DROPTABLE a,b CASCADE;
73+
SELECT trueFROM aqo_reset();
7274
DROP EXTENSION aqo CASCADE;

0 commit comments

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