On Thu, Nov 30, 2017 at 1:36 AM, Antonin Houska <ah@cybertec.at> wrote:
The following test
-- Input relation to aggregate push down hook is not safe to pushdown and thus -- the aggregate cannot be pushed down to foreign server. explain (verbose, costs off) select count(t1.c3) from ft1 t1, ft1 t2 where t1.c1 = postgres_fdw_abs(t1.c2);
produces the following plan
QUERY PLAN ---------------------------------------------------------------------------------------------------------- Aggregate Output: count(t1.c3) -> Nested Loop Output: t1.c3 -> Foreign Scan on public.ft1 t2 Remote SQL: SELECT NULL FROM "S 1"."T 1" -> Materialize Output: t1.c3 -> Foreign Scan on public.ft1 t1 Output: t1.c3 Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE (("C 1" = public.postgres_fdw_abs(c2)))
which is not major problem as such, but gdb shows that the comment "aggregate cannot be pushed" is not correct. In fact, postgresGetForeignUpperPaths() *does* create the upper path.
The reason that UPPERREL_GROUP_AGG is eventually not used seems to be that postgresGetForeignJoinPaths() -> add_foreign_grouping_paths() -> estimate_path_cost_size() estimates the join cost in rather generic way. While the remote server can push the join clause down to the inner relation of NL, the postgres_fdw cost computation assumes that the join clause is applied to each pair of output and input tuple.
I don't think that the postgres_fdw's estimate can be fixed easily, but if the impact of "shipability" on (not) using the upper relation should be tested, we need a different test.
Oops. My bad.
Agree with your analysis.
Will send a patch fixing this testcase.
Attached patch to fix the test case. In new test case I am using a JOIN query where JOIN condition is not safe to push down and hence the JOIN itself is unsafe. Due to which AggPushDown does not consider that relation. Also, I have used ft2 in the query which has use_remote_estimate set to true.
