From: | Antonin Houska <ah(at)cybertec(dot)at> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Unclear regression test for postgres_fdw |
Date: | 2017-11-29 20:06:11 |
Message-ID: | 15265.1511985971@localhost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
--
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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2017-11-29 20:28:52 | Re: [HACKERS] path toward faster partition pruning |
Previous Message | Andrew Dunstan | 2017-11-29 19:17:27 | Re: [HACKERS] SQL procedures |