Horton

Re: Problems with plan estimates in postgres_fdw

Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>

View thread View on postgr.es
From: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andrew Gierth <andrew@tao11.riddles.org.uk>, pgsql-hackers@postgresql.org
Date: 2018-12-26T07:35:21Z
Lists: pgsql-hackers

Attachments

(2018/12/17 22:09), Etsuro Fujita wrote:
> Here is a set of WIP patches for pushing down ORDER BY LIMIT to the remote:

> For some regression test cases with ORDER BY and/or LIMIT, I noticed
> that these patches still cannot push down those clause to the remote. I
> guess it would be needed to tweak the cost/size estimation added by
> these patches, but I didn't look at that in detail yet. Maybe I'm
> missing something, though.

I looked into that.  In the previous patch, I estimated costs for 
performing grouping/aggregation with ORDER BY remotely, using the same 
heuristic as scan/join cases if appropriate (i.e., I assumed that a 
remote sort for grouping/aggregation also costs 20% extra), but that 
seems too large for grouping/aggregation.  So I reduced that to 5% 
extra.  The result showed that some test cases can be pushed down, but 
some still cannot.  I think we could push down the ORDER BY in all cases 
by reducing the extra cost to a much smaller value, but I'm not sure 
what value is reasonable.

Attached is an updated version of the patch.  Other changes:

* Modified estimate_path_cost_size() further so that it accounts for 
tlist eval cost as well
* Added more comments
* Simplified code a little bit

I'll add this to the upcoming CF.

Best regards,
Etsuro Fujita

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.