8000 Fix the problem with ignoring subplans in clauses · postgrespro/aqo@b7f325e · GitHub
[go: up one dir, main page]
Skip to content

Commit b7f325e

Browse files
danolivoAndrey Lepikhov
danolivo
authored and
Andrey Lepikhov
committed
Fix the problem with ignoring subplans in clauses
1 parent c8f6a2c commit b7f325e

File tree

2 files changed

+72
-0
lines changed

2 files changed

+72
-0
lines changed

‎expected/unsupported.out

Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -38,4 +38,64 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
3838
JOINS: 0
3939
(11 rows)
4040

41+
SELECT count(*) FROM t WHERE x = (SELECT avg(x) FROM t WHERE x = 1);
42+
count
43+
-------
44+
50
45+
(1 row)
46+
47+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
48+
SELECT count(*) FROM t WHERE x = (
49+
SELECT avg(x) FROM t WHERE x = 1
50+
);
51+
QUERY PLAN
52+
----------------------------------------------------------
53+
Aggregate (actual rows=1 loops=1)
54+
AQO not used
55+
InitPlan 1 (returns $0)
56+
-> Aggregate (actual rows=1 loops=1)
57+
AQO not used
58+
-> Seq Scan on t t_1 (actual rows=50 loops=1)
59+
AQO: rows=50, error=0%
60+
Filter: (x = 1)
61+
Rows Removed by Filter: 950
62+
-> Seq Scan on t (actual rows=50 loops=1)
63+
AQO: rows=50, error=0%
64+
Filter: ((x)::numeric = $0)
65+
Rows Removed by Filter: 950
66+
Using aqo: true
67+
AQO mode: LEARN
68+
JOINS: 0
69+
(16 rows)
70+
71+
SELECT count(*) FROM t WHERE x = (SELECT avg(x) FROM t t0 WHERE t0.x = t.x);
72+
count
73+
-------
74+
1000
75+
(1 row)
76+
77+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
78+
SELECT count(*) FROM t WHERE x = (
79+
SELECT avg(x) FROM t t0 WHERE t0.x = t.x
80+
);
81+
QUERY PLAN
82+
------------------------------------------------------------------
83+
Aggregate (actual rows=1 loops=1)
84+
AQO not used
85+
-> Seq Scan on t (actual rows=1000 loops=1)
86+
AQO not used
87+
Filter: ((x)::numeric = (SubPlan 1))
88+
SubPlan 1
89+
-> Aggregate (actual rows=1 loops=1000)
90+
AQO not used
91+
-> Seq Scan on t t0 (actual rows=50 loops=1000)
92+
AQO: rows=50, error=0%
93+
Filter: (x = t.x)
94+
Rows Removed by Filter: 950
95+
Using aqo: true
96+
AQO mode: LEARN
97+
JOINS: 0
98+
(15 rows)
99+
100+
41101
DROP EXTENSION aqo;

‎sql/unsupported.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,4 +12,16 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
1212
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
1313
SELECT * FROM t GROUP BY (x) HAVING x > 3;
1414

15+
SELECT count(*) FROM t WHERE x = (SELECT avg(x) FROM t WHERE x = 1);
16+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
17+
SELECT count(*) FROM t WHERE x = (
18+
SELECT avg(x) FROM t WHERE x = 1
19+
);
20+
21+
SELECT count(*) FROM t WHERE x = (SELECT avg(x) FROM t t0 WHERE t0.x = t.x);
22+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
23+
SELECT count(*) FROM t WHERE x = (
24+
SELECT avg(x) FROM t t0 WHERE t0.x = t.x
25+
);
26+
1527
DROP EXTENSION aqo;

0 commit comments

Comments
 (0)
0