From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrew Gierth <andrew@tao11.riddles.org.uk>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Problems with plan estimates in postgres_fdw
Date: Thu, 02 Aug 2018 10:41:48 -0400
Message-ID: <1175.1533220908@sss.pgh.pa.us> (raw)
In-Reply-To: <87pnz1aby9.fsf@news-spur.riddles.org.uk>
References: <87pnz1aby9.fsf@news-spur.riddles.org.uk>
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.
regards, tom lane
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: tgl@sss.pgh.pa.us, andrew@tao11.riddles.org.uk
Subject: Re: Problems with plan estimates in postgres_fdw
In-Reply-To: <1175.1533220908@sss.pgh.pa.us>
* 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