Re: Problems with plan estimates in postgres_fdw

From: Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Problems with plan estimates in postgres_fdw
Date: 2018-12-26 07:35:21
Message-ID: 5C232F39.9060509@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

(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

Attachment Content-Type Size
0001-postgres_fdw-Perform-UPPERREL_ORDERED-step-remotely.patch text/x-patch 46.2 KB
0002-postgres_fdw-Perform-UPPERREL_FINAL-step-remotely.patch text/x-patch 95.4 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Fabien COELHO 2018-12-26 07:40:00 RE: Timeout parameters
Previous Message Michael Paquier 2018-12-26 06:24:01 Re: Don't wake up to check trigger file if none is configured