Обсуждение: pgsql: postgres_fdw: Add ORDER BY to some remote SQL queries.
postgres_fdw: Add ORDER BY to some remote SQL queries. If the join problem's entire ORDER BY clause can be pushed to the remote server, consider a path that adds this ORDER BY clause. If use_remote_estimate is on, we cost this path using an additional remote EXPLAIN. If not, we just estimate that the path costs 20% more, which is intended to be large enough that we won't request a remote sort when it's not helpful, but small enough that we'll have the remote side do the sort when in doubt. In some cases, the remote sort might actually be free, because the remote query plan might happen to produce output that is ordered the way we need, but without remote estimates we have no way of knowing that. It might also be useful to request sorted output from the remote side if it enables an efficient merge join, but this patch doesn't attempt to handle that case. Ashutosh Bapat with revisions by me. Also reviewed by Fabrízio de Royes Mello and Jeevan Chalke. Branch ------ master Details ------- http://git.postgresql.org/pg/commitdiff/f18c944b6137329ac4a6b2dce5745c5dc21a8578 Modified Files -------------- contrib/postgres_fdw/deparse.c | 56 +++++++++++- contrib/postgres_fdw/expected/postgres_fdw.out | 64 ++++++++----- contrib/postgres_fdw/postgres_fdw.c | 116 +++++++++++++++++++++++- contrib/postgres_fdw/postgres_fdw.h | 3 + contrib/postgres_fdw/sql/postgres_fdw.sql | 13 ++- 5 files changed, 222 insertions(+), 30 deletions(-)
On Tue, Nov 3, 2015 at 06:05:11PM +0000, Robert Haas wrote: > postgres_fdw: Add ORDER BY to some remote SQL queries. > > If the join problem's entire ORDER BY clause can be pushed to the > remote server, consider a path that adds this ORDER BY clause. If > use_remote_estimate is on, we cost this path using an additional > remote EXPLAIN. If not, we just estimate that the path costs 20% > more, which is intended to be large enough that we won't request a > remote sort when it's not helpful, but small enough that we'll have > the remote side do the sort when in doubt. In some cases, the remote > sort might actually be free, because the remote query plan might > happen to produce output that is ordered the way we need, but without > remote estimates we have no way of knowing that. > > It might also be useful to request sorted output from the remote side > if it enables an efficient merge join, but this patch doesn't attempt > to handle that case. Does this complete FDW join pushdown? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription +
On Tue, Dec 1, 2015 at 11:30 AM, Bruce Momjian <bruce@momjian.us> wrote: > On Tue, Nov 3, 2015 at 06:05:11PM +0000, Robert Haas wrote: >> postgres_fdw: Add ORDER BY to some remote SQL queries. >> >> If the join problem's entire ORDER BY clause can be pushed to the >> remote server, consider a path that adds this ORDER BY clause. If >> use_remote_estimate is on, we cost this path using an additional >> remote EXPLAIN. If not, we just estimate that the path costs 20% >> more, which is intended to be large enough that we won't request a >> remote sort when it's not helpful, but small enough that we'll have >> the remote side do the sort when in doubt. In some cases, the remote >> sort might actually be free, because the remote query plan might >> happen to produce output that is ordered the way we need, but without >> remote estimates we have no way of knowing that. >> >> It might also be useful to request sorted output from the remote side >> if it enables an efficient merge join, but this patch doesn't attempt >> to handle that case. > > Does this complete FDW join pushdown? It's the most important part, but we may want to do other things. See http://www.postgresql.org/message-id/CAFjFpRfeKHiCmwJ72p4=ZvuZRQsaU9tbfyW7vwr-5PPvrCbcQQ@mail.gmail.com -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company