From: Antonin Houska <ah@cybertec.at>
To: Jeevan Chalke <jeevan.chalke@enterprisedb.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Unclear regression test for postgres_fdw
Date: Fri, 01 Dec 2017 10:01:34 +0100
Message-ID: <16549.1512118894@localhost> (raw)
In-Reply-To: <CAM2+6=UGBx+WhUnX9O+vYEgtSeaLP-SKmKMVBNcSOFRqwDCL=g@mail.gmail.com>
References: <15265.1511985971@localhost>
<CAM2+6=Um1iMhWhPHqz8v=4aYNS_+d9-fE4dcaLF4dEoXFoyZkA@mail.gmail.com>
<CAM2+6=UGBx+WhUnX9O+vYEgtSeaLP-SKmKMVBNcSOFRqwDCL=g@mail.gmail.com>
Jeevan Chalke <jeevan.chalke@enterprisedb.com> wrote:
> On Thu, Nov 30, 2017 at 3:44 PM, Jeevan Chalke <jeevan.chalke@enterprisedb.com> wrote:
>
> 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.
I've just verified that postgresGetForeignUpperPaths() does return here:
/*
* If input rel is not safe to pushdown, then simply return as we cannot
* perform any post-join operations on the foreign server.
*/
if (!input_rel->fdw_private ||
!((PgFdwRelationInfo *) input_rel->fdw_private)->pushdown_safe)
return;
I see no other problems here. You may need to add the patch to the next CF so
it does not get lost.
--
Antonin Houska
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de, http://www.cybertec.at
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: ah@cybertec.at, jeevan.chalke@enterprisedb.com
Subject: Re: Unclear regression test for postgres_fdw
In-Reply-To: <16549.1512118894@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox