Abbas Butt <abbas.b...@enterprisedb.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 -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers