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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
