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

Commitf5d6bce

Browse files
committed
postgres_fdw: Try again to stabilize aggregate pushdown regression tests.
A query that only aggregates one row isn't a great argument for pushdown,and buildfarm member brolga decides against it. Adjust the query a bitin the hopes of getting remote aggregation to win consistently.Jeevan Chalke, per suggestion from Tom Lane
1 parent2dde01c commitf5d6bce

File tree

2 files changed

+27
-35
lines changed

2 files changed

+27
-35
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 25 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -3123,40 +3123,32 @@ select c2, sum from "S 1"."T 1" t1, lateral (select sum(t2.c1 + t1."C 1") sum fr
31233123
reset enable_hashagg;
31243124
-- Check with placeHolderVars
31253125
explain (verbose, costs off)
3126-
select q.b, count(ft4.c1), sum(q.a) from ft4 left join (select min(13), avg(ft1.c1), sum(ft2.c1) from ft1 right join ft2 on (ft1.c1 = ft2.c1) where ft1.c1 = 12) q(a, b, c) on (ft4.c1 = q.b) where ft4.c1 between 10 and 15 group by q.b order by 1 nulls last, 2;
3127-
QUERY PLAN
3128-
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3129-
Sort
3130-
Output: q.b, (count(ft4.c1)), (sum(q.a))
3131-
Sort Key: q.b, (count(ft4.c1))
3132-
-> GroupAggregate
3133-
Output: q.b, count(ft4.c1), sum(q.a)
3134-
Group Key: q.b
3135-
-> Sort
3136-
Output: q.b, ft4.c1, q.a
3137-
Sort Key: q.b
3138-
-> Hash Left Join
3139-
Output: q.b, ft4.c1, q.a
3140-
Hash Cond: ((ft4.c1)::numeric = q.b)
3141-
-> Foreign Scan on public.ft4
3142-
Output: ft4.c1, ft4.c2, ft4.c3
3143-
Remote SQL: SELECT c1 FROM "S 1"."T 3" WHERE ((c1 >= 10)) AND ((c1 <= 15))
3144-
-> Hash
3145-
Output: q.b, q.a
3146-
-> Subquery Scan on q
3147-
Output: q.b, q.a
3148-
-> Foreign Scan
3149-
Output: (min(13)), (avg(ft1.c1)), (NULL::bigint)
3150-
Relations: Aggregate on ((public.ft1) INNER JOIN (public.ft2))
3151-
Remote SQL: SELECT min(13), avg(r1."C 1"), NULL::bigint FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r2."C 1" = 12)) AND ((r1."C 1" = 12))))
3152-
(23 rows)
3126+
select sum(q.a), count(q.b) from ft4 left join (select 13, avg(ft1.c1), sum(ft2.c1) from ft1 right join ft2 on (ft1.c1 = ft2.c1)) q(a, b, c) on (ft4.c1 <= q.b);
3127+
QUERY PLAN
3128+
----------------------------------------------------------------------------------------------------------------------------------------------------------
3129+
Aggregate
3130+
Output: sum(q.a), count(q.b)
3131+
-> Nested Loop Left Join
3132+
Output: q.a, q.b
3133+
Join Filter: ((ft4.c1)::numeric <= q.b)
3134+
-> Foreign Scan on public.ft4
3135+
Output: ft4.c1, ft4.c2, ft4.c3
3136+
Remote SQL: SELECT c1 FROM "S 1"."T 3"
3137+
-> Materialize
3138+
Output: q.a, q.b
3139+
-> Subquery Scan on q
3140+
Output: q.a, q.b
3141+
-> Foreign Scan
3142+
Output: (13), (avg(ft1.c1)), (NULL::bigint)
3143+
Relations: Aggregate on ((public.ft2) LEFT JOIN (public.ft1))
3144+
Remote SQL: SELECT 13, avg(r1."C 1"), NULL::bigint FROM ("S 1"."T 1" r2 LEFT JOIN "S 1"."T 1" r1 ON (((r1."C 1" = r2."C 1"))))
3145+
(16 rows)
31533146

3154-
select q.b, count(ft4.c1), sum(q.a) from ft4 left join (select min(13), avg(ft1.c1), sum(ft2.c1) from ft1 right join ft2 on (ft1.c1 = ft2.c1) where ft1.c1 = 12) q(a, b, c) on (ft4.c1 = q.b) where ft4.c1 between 10 and 15 group by q.b order by 1 nulls last, 2;
3155-
b | count | sum
3156-
---------------------+-------+-----
3157-
12.0000000000000000 | 1 | 13
3158-
| 2 |
3159-
(2 rows)
3147+
select sum(q.a), count(q.b) from ft4 left join (select 13, avg(ft1.c1), sum(ft2.c1) from ft1 right join ft2 on (ft1.c1 = ft2.c1)) q(a, b, c) on (ft4.c1 <= q.b);
3148+
sum | count
3149+
-----+-------
3150+
650 | 50
3151+
(1 row)
31603152

31613153
-- Not supported cases
31623154
-- Grouping sets

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -808,8 +808,8 @@ reset enable_hashagg;
808808

809809
-- Check with placeHolderVars
810810
explain (verbose, costs off)
811-
selectq.b,count(ft4.c1),sum(q.a)from ft4left join (selectmin(13),avg(ft1.c1),sum(ft2.c1)from ft1right join ft2on (ft1.c1=ft2.c1)whereft1.c1=12) q(a, b, c)on (ft4.c1=q.b)whereft4.c1 between10and15group byq.border by1 nulls last,2;
812-
selectq.b,count(ft4.c1),sum(q.a)from ft4left join (selectmin(13),avg(ft1.c1),sum(ft2.c1)from ft1right join ft2on (ft1.c1=ft2.c1)whereft1.c1=12) q(a, b, c)on (ft4.c1=q.b)whereft4.c1 between10and15group byq.border by1 nulls last,2;
811+
selectsum(q.a),count(q.b)from ft4left join (select13,avg(ft1.c1),sum(ft2.c1)from ft1right join ft2on (ft1.c1=ft2.c1)) q(a, b, c)on (ft4.c1<=q.b);
812+
selectsum(q.a),count(q.b)from ft4left join (select13,avg(ft1.c1),sum(ft2.c1)from ft1right join ft2on (ft1.c1=ft2.c1)) q(a, b, c)on (ft4.c1<=q.b);
813813

814814

815815
-- Not supported cases

0 commit comments

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