Message-ID: <5BBC4140.50403@lab.ntt.co.jp>
Date: Tue, 09 Oct 2018 14:48:48 +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>
In-Reply-To: <5BB739CD.6040002@lab.ntt.co.jp>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andrew Gierth <andrew@tao11.riddles.org.uk>, pgsql-hackers@postgresql.org
Precedence: bulk

(2018/10/05 19:15), Etsuro Fujita wrote:
> (2018/08/02 23:41), Tom Lane wrote:
>> Andrew Gierth<andrew@tao11.riddles.org.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.

Will work on it unless somebody else wants to.

Best regards,
Etsuro Fujita