I've been asked to investigate a case of a foreign join not occurring on the foreign server as would have been expected.
I've narrowed this down and the problem seems to only occur with outer type joins. The problem can be reproduced by the attached test_case.sql Upon investigation I've discovered that the problem relates to the citext extension not being in the shippable_extensions List for the joinrel. Since the extension is not white listed, the qual on the citext column is disallowed from being pushed down into the foreign server by is_shippable(). This happens to work fine for INNER JOINs since the qual makes it into baserestrictinfo an is properly classified by the following fragment in postgresGetForeignRelSize() /* * Identify which baserestrictinfo clauses can be sent to the remote * server and which can't. */ classifyConditions(root, baserel, baserel->baserestrictinfo, &fpinfo->remote_conds, &fpinfo->local_conds); The attached patch, based on 9.6, fixes the problem by properly processing the foreign server options in postgresGetForeignJoinPaths(). I ended up shifting the code which does this into functions to allow it to be reused. I also ended up shifting out the code which processes the table options so that it is consistent. Reviews from people a bit closer to the foreign join pushdown code are welcome. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
test_caee.sql
Description: Binary data
foreign_outerjoin_pushdown_fix.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
