Re: Problems with plan estimates in postgres_fdw

(2018/08/02 23:41), Tom Lane wrote:
> Andrew Gierth<andrew(at)tao11(dot)riddles(dot)org(dot)uk> writes:
>> [ postgres_fdw is not smart about exploiting fast-start plans ]
>
> Yeah, that's basically not accounted for at all in the current design.
>
>> One possibility: would it be worth adding an option to EXPLAIN that
>> makes it assume cursor_tuple_fraction?
>
> [ handwaving ahead ]
>
> I wonder whether it could be done without destroying postgres_fdw's
> support for old servers, by instead including a LIMIT in the query sent
> for explaining. The trick would be to know what value to put as the
> limit, though. It'd be easy to do if we were willing to explain the query
> twice (the second time with a limit chosen as a fraction of the rowcount
> seen the first time), but man that's an expensive solution.
>
> Another component of any real fix here would be to issue "SET
> cursor_tuple_fraction" before opening the execution cursor, so as to
> ensure that we actually get an appropriate plan on the remote side.
>
> If we could tell whether there's going to be any use in fast-start plans,
> it might make sense to build two scan paths for a foreign table, one based
> on a full-table scan and one based on EXPLAIN ... LIMIT 1. This still
> means two explain requests, which is why I'm not thrilled about doing it
> unless there's a high probability of the extra explain being useful.

Agreed, but ISTM that to address the original issue, it would be enough
to jsut add LIMIT (or ORDER BY LIMIT) pushdown to postgres_fdw based on
the upper-planner-pathification work.

I'm sorry that I'm really late for the party.

Best regards,
Etsuro Fujita