From: Antonin Houska <ah@cybertec.at>
To: pgsql-hackers@postgresql.org
Subject: Unclear regression test for postgres_fdw
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Date: Wed, 29 Nov 2017 21:06:11 +0100
Message-ID: <15265.1511985971@localhost>
Precedence: bulk

The following test

-- Input relation to aggregate push down hook is not safe to pushdown and t=
hus
-- 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 =3D postgres_fdw_abs(t1=
.c2);

produces the following plan

                                                QUERY PLAN=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
---------------------------------------------------------------------------=
-------------------------------
 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" =
=3D public.postgres_fdw_abs(c2)))

which is not major problem as such, but gdb shows that the comment "aggrega=
te
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. Wh=
ile
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.

--=20
Antonin Houska
Cybertec Sch=C3=B6nig & Sch=C3=B6nig GmbH
Gr=C3=B6hrm=C3=BChlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de, http://www.cybertec.at