Message-ID: <5C6FB014.9040403@lab.ntt.co.jp>
Date: Fri, 22 Feb 2019 17:17:24 +0900
From: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:11.0) Gecko/20120327 Thunderbird/11.0.1
MIME-Version: 1.0
Subject: Re: Problems with plan estimates in postgres_fdw
References: <87pnz1aby9.fsf@news-spur.riddles.org.uk>
 <1175.1533220908@sss.pgh.pa.us> <5BB739CD.6040002@lab.ntt.co.jp>
 <5BBC4140.50403@lab.ntt.co.jp> <5C17A00E.5000005@lab.ntt.co.jp>
 <5C232F39.9060509@lab.ntt.co.jp> <5C25C7B5.2010802@lab.ntt.co.jp>
 <5C518631.5080401@lab.ntt.co.jp>
 <CAMkU=1z1Ez7fb_P_0Bc1040npE5fCOnu0M1DFyOzCp=e=rBJCw@mail.gmail.com>
In-Reply-To: 
 <CAMkU=1z1Ez7fb_P_0Bc1040npE5fCOnu0M1DFyOzCp=e=rBJCw@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Andrew Gierth <andrew@tao11.riddles.org.uk>,
        pgsql-hackers <pgsql-hackers@postgresql.org>
Precedence: bulk

Hi Jeff,

(2019/02/21 6:19), Jeff Janes wrote:
> On Wed, Jan 30, 2019 at 6:12 AM Etsuro Fujita
> <fujita.etsuro@lab.ntt.co.jp <mailto:fujita.etsuro@lab.ntt.co.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