Re: Foreign join search stops on the first try

This code was written long ago. So I may have some recollection
errors. But AFAIR, the reasons we wanted to avoid repeated
estimation/planning for the same foreign join rel were
1. If use_remote_estimate = true, we fetch EXPLAIN output from the
foreign server for various pathkeys. Fetching EXPLAIN output is
expensive. Irrespective of the join order being considered locally, we
expect the foreign server to give us the same cost since the join is
the same. So we avoid running EXPLAIN again and again.
2. If use_remote_estimate = false, the logic to estimate a foreign
join locally is independent of the join order so should yield same
cost again and again. For some reason that doesn't seem to be the case
here.

On Tue, Jan 25, 2022 at 1:26 PM Alexander Pyhalov
<a(dot)pyhalov(at)postgrespro(dot)ru> wrote:

>
> Without patch:
>
> explain analyze verbose SELECT * FROM order_line, stock, district WHERE
> ol_d_id = 1 AND d_id = 1 AND (ol_o_id < d_next_o_id) AND ol_o_id >=
> (d_next_o_id - 20) AND s_i_id = ol_i_id AND s_quantity < 11;
... clipped
> test.stock WHERE ((s_quantity < 11))
> Planning Time: 1.812 ms
> Execution Time: 8.534 ms
> (15 rows)
>
> With patch:
>
> explain analyze verbose SELECT * FROM order_line, stock, district WHERE
> ol_d_id = 1 AND d_id = 1 AND (ol_o_id < d_next_o_id) AND ol_o_id >=
> (d_next_o_id - 20) AND s_i_id = ol_i_id AND s_quantity < 11;
>
... clipped
> Planning Time: 0.928 ms
> Execution Time: 4.511 ms

It is surprising that the planning time halves with the patch. I
expected it to increase slightly since we will compute estimates
thrice instead of once.

What is use_remote_estimate? Is it ON/OFF?

If we want to proceed along this line, we should take care not to fire
more EXPLAIN queries on the foreign server.

--
Best Wishes,
Ashutosh Bapat