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>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Problems with plan estimates in postgres_fdw
Date: Fri, 05 Oct 2018 19:15:41 +0900
Message-ID: <5BB739CD.6040002@lab.ntt.co.jp> (raw)
In-Reply-To: <1175.1533220908@sss.pgh.pa.us>
References: <87pnz1aby9.fsf@news-spur.riddles.org.uk>
<1175.1533220908@sss.pgh.pa.us>
(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.
I'm sorry that I'm really late for the party.
Best regards,
Etsuro Fujita
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: fujita.etsuro@lab.ntt.co.jp, tgl@sss.pgh.pa.us, andrew@tao11.riddles.org.uk
Subject: Re: Problems with plan estimates in postgres_fdw
In-Reply-To: <5BB739CD.6040002@lab.ntt.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox