Re: Cost overestimation of foreign JOIN

On 12/1/20 6:17 PM, Ashutosh Bapat wrote:
> On Mon, Nov 30, 2020 at 11:56 PM Andrey Lepikhov
> <a(dot)lepikhov(at)postgrespro(dot)ru> wrote:
>>
>> On 30.11.2020 22:38, Tom Lane wrote:
>>> Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru> writes:
>>> If you're unhappy with the planning results you get for this,
>>> why don't you have use_remote_estimate turned on?
>>
>> I have a mixed load model. Large queries are suitable for additional
>> estimate queries. But for many simple SELECT's that touch a small
>> portion of the data, the latency has increased significantly. And I
>> don't know how to switch the use_remote_estimate setting in such case.
>
> You may disable use_remote_estimates for given table or a server. So
> if tables participating in short queries are different from those in
> the large queries, you could set use_remote_estimate at table level to
> turn it off for the first set. Otherwise, we need a FDW level GUC
> which can be turned on/off for a given session or a query.

Currently I implemented another technique:
- By default, use_remote_estimate is off.
- On the estimate_path_cost_size() some estimation criteria is checked.
If true, we force remote estimation for this JOIN.
This approach solves the push-down problem in my case - TPC-H test with
6 servers/instances. But it is not so scalable, as i want.
>
> Generally use_remote_estimate isn't scalable and there have been
> discussions about eliminating the need of it. But no concrete proposal
> has come yet.
>
Above I suggested to use results of cost calculation on local JOIN,
assuming that in the case of postgres_fdw wrapper very likely, that
foreign server will use the same type of join (or even better, if it has
some index, for example).
If this approach is of interest, I can investigate it.

--
regards,
Andrey Lepikhov
Postgres Professional