From f5d6bce63ceb3c59a964814bb0df5a0648e750e5 Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Mon, 24 Oct 2016 22:36:24 -0400 Subject: [PATCH] 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 bit in the hopes of getting remote aggregation to win consistently. Jeevan Chalke, per suggestion from Tom Lane --- .../postgres_fdw/expected/postgres_fdw.out | 58 ++++++++----------- contrib/postgres_fdw/sql/postgres_fdw.sql | 4 +- 2 files changed, 27 insertions(+), 35 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 88b696cede..2745ad5652 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -3123,40 +3123,32 @@ select c2, sum from "S 1"."T 1" t1, lateral (select sum(t2.c1 + t1."C 1") sum fr reset enable_hashagg; -- Check with placeHolderVars explain (verbose, costs off) -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; - QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Sort - Output: q.b, (count(ft4.c1)), (sum(q.a)) - Sort Key: q.b, (count(ft4.c1)) - -> GroupAggregate - Output: q.b, count(ft4.c1), sum(q.a) - Group Key: q.b - -> Sort - Output: q.b, ft4.c1, q.a - Sort Key: q.b - -> Hash Left Join - Output: q.b, ft4.c1, q.a - Hash Cond: ((ft4.c1)::numeric = q.b) - -> Foreign Scan on public.ft4 - Output: ft4.c1, ft4.c2, ft4.c3 - Remote SQL: SELECT c1 FROM "S 1"."T 3" WHERE ((c1 >= 10)) AND ((c1 <= 15)) - -> Hash - Output: q.b, q.a - -> Subquery Scan on q - Output: q.b, q.a - -> Foreign Scan - Output: (min(13)), (avg(ft1.c1)), (NULL::bigint) - Relations: Aggregate on ((public.ft1) INNER JOIN (public.ft2)) - 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)))) -(23 rows) +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); + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------------- + Aggregate + Output: sum(q.a), count(q.b) + -> Nested Loop Left Join + Output: q.a, q.b + Join Filter: ((ft4.c1)::numeric <= q.b) + -> Foreign Scan on public.ft4 + Output: ft4.c1, ft4.c2, ft4.c3 + Remote SQL: SELECT c1 FROM "S 1"."T 3" + -> Materialize + Output: q.a, q.b + -> Subquery Scan on q + Output: q.a, q.b + -> Foreign Scan + Output: (13), (avg(ft1.c1)), (NULL::bigint) + Relations: Aggregate on ((public.ft2) LEFT JOIN (public.ft1)) + 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")))) +(16 rows) -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; - b | count | sum ----------------------+-------+----- - 12.0000000000000000 | 1 | 13 - | 2 | -(2 rows) +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); + sum | count +-----+------- + 650 | 50 +(1 row) -- Not supported cases -- Grouping sets diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index bb9d41a1b3..f48743c390 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -808,8 +808,8 @@ reset enable_hashagg; -- Check with placeHolderVars explain (verbose, costs off) -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; -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; +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); +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); -- Not supported cases -- 2.40.0