Horton

Re: Problems with plan estimates in postgres_fdw

Jeff Janes <jeff.janes@gmail.com>

View thread View on postgr.es
From: Jeff Janes <jeff.janes@gmail.com>
To: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Andrew Gierth <andrew@tao11.riddles.org.uk>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2019-02-20T21:19:03Z
Lists: pgsql-hackers

Attachments

On Wed, Jan 30, 2019 at 6:12 AM Etsuro Fujita <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.

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

Cheers,

Jeff

Commits

  1. postgres_fdw: Perform the (FINAL, NULL) upperrel operations remotely.

  2. Refactor create_limit_path() to share cost adjustment code with FDWs.

  3. postgres_fdw: Modify regression tests for EPQ-related planning problems.

  4. postgres_fdw: Perform the (ORDERED, NULL) upperrel operations remotely.

  5. Save PathTargets for distinct/ordered relations in root->upper_targets[].

  6. Split create_foreignscan_path() into three functions.

  7. Fix test case for 'outer pathkeys do not match mergeclauses' fix.

  8. postgres_fdw: Avoid 'outer pathkeys do not match mergeclauses' error.