Re: Problems with plan estimates in postgres_fdw

From: Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Problems with plan estimates in postgres_fdw
Date: 2019-02-22 08:17:24
Message-ID: 5C6FB014.9040403@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Jeff,

(2019/02/21 6:19), Jeff Janes wrote:
> On Wed, Jan 30, 2019 at 6:12 AM Etsuro Fujita
> <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp <mailto:fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>> wrote:
>
> (2018/12/28 15:50), Etsuro Fujita wrote:
> > Attached is a new version of the
> > patch.
>
> Here is an updated version of the patch set. Changes are:
>
> * In the previous version, LIMIT without OFFSET was not performed
> remotely as the costs was calculated the same as the costs of
> performing
> it locally. (Actually, such LIMIT was performed remotely in a case in
> the postgres_fdw regression test, but that was due to a bug :(.) I
> think we should prefer performing such LIMIT remotely as that avoids
> extra row fetches from the remote that performing it locally might
> cause, so I tweaked the costs estimated in
> estimate_path_cost_size(), to
> ensure that we'll prefer performing such LIMIT remotely.

> With your tweaks, I'm still not seeing the ORDER-less LIMIT get pushed
> down when using use_remote_estimate in a simple test case, either with
> this set of patches, nor in the V4 set. However, without
> use_remote_estimate, the LIMIT is now getting pushed with these patches
> when it does not in HEAD.

Good catch! I think the root cause of that is: when using
use_remote_estimate, remote estimates obtained through remote EXPLAIN
are rounded off to two decimal places, which I completely overlooked.
Will fix. I think I can post a new version early next week.

> See attached test case, to be run in new database named 'foo'.

Thanks for the review and the test case!

Best regards,
Etsuro Fujita

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Kyotaro HORIGUCHI 2019-02-22 08:19:56 Re: shared-memory based stats collector
Previous Message Yuzuko Hosoya 2019-02-22 08:14:04 Problem with default partition pruning