Re: An issue in remote query optimization

Abbas Butt <abbas(dot)butt(at)enterprisedb(dot)com> writes:
> Postgres_fdw optimizes remote queries by pushing down the where clause.
> This feature does not work consistently when the query is executed from
> within a pl/pgsql function. The optimization works when the function
> executes the query for the first 5 times, and fails afterwards.

Well, it's switching to a generic plan. Your first five executions look
like (ignoring the startup transient):

> 2017-01-31 00:39:25 PST LOG: duration: 0.315 ms plan:
> Query Text: select b from foreign_numbers where a=x
> Foreign Scan on public.foreign_numbers (cost=100.00..111.91 rows=1
> width=516)
> Output: b
> Remote SQL: SELECT b FROM public.numbers WHERE ((a = 2))

with actual durations ranging from 0.250 to 0.315 ms. After that
you get generic plans:

> 2017-01-31 00:39:25 PST LOG: duration: 0.251 ms plan:
> Query Text: select b from foreign_numbers where a=x
> Foreign Scan on public.foreign_numbers (cost=100.00..114.91 rows=1
> width=516)
> Output: b
> Filter: (foreign_numbers.a = $3)
> Remote SQL: SELECT a, b FROM public.numbers

with actual durations ranging from 0.223 to 0.251 ms.

So where's the slowdown, exactly? It looks to me like the planner
has concluded that a custom plan is not enough better than generic
to justify repeated planning cost, and it looks to me like it was right.

> Note that the remote query does not contain the WHERE clause after the 5th
> invocation.

You'd have to take that up with the author of the FDW you're using.
Optimization of queries involving foreign tables is almost completely
on the head of the FDW, and this one seems not to know about pushing
down WHERE clauses that involve Param nodes.

regards, tom lane