Re: Cost overestimation of foreign JOIN

From: "Andrey V(dot) Lepikhov" <a(dot)lepikhov(at)postgrespro(dot)ru>
To: Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Cost overestimation of foreign JOIN
Date: 2020-12-02 10:49:46
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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.

Andrey Lepikhov
Postgres Professional

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2020-12-02 11:13:38 Re: Corner-case bug in pg_rewind
Previous Message Drouvot, Bertrand 2020-12-02 10:46:35 Re: [UNVERIFIED SENDER] Re: [BUG] orphaned function