Consider a query such as:
SELECT * FROM a, b WHERE (a.x = 42 AND b.y = 43) OR (a.x = 44 AND b.z = 45);
If a and/or b are regular tables, the query planner will cleverly
consider the possibility of using an index on a to filter for rows
with a.x = 42 OR a.x = 44, or of using an index on b to filter for
rows where b.y = 43 OR b.z = 45. But if they are foreign tables, this
optimization isn't considered, because we don't intrinsically know
anything about what indexes are present on the foreign side. However,
this optimization could potentially be quite valuable. In fact, it's
arguably more useful here for regular tables, because even if no index
is present on the foreign side, applying the condition on the remote
side might eliminate enough data transfer overhead to win. The only
situation in which I can really see it losing is if the simplified
qual ends up eliminating too few rows to cover the remote side's
processing costs; I'm not sure how possible that is, or how to know
whether it might be the case.
To see how this can torpedo performance, run the attached SQL file on
an empty database, and then run these quereis:
explain analyze SELECT other.id, other.title, local.id, local.title
FROM other INNER JOIN local ON other.id = local.id WHERE local.title =
md5(1::text) OR (local.title = md5(3::text) AND other.id = 3);
explain analyze SELECT other.id, other.title, frgn.id, frgn.title FROM
other INNER JOIN frgn ON other.id = frgn.id WHERE frgn.title =
md5(1::text) OR (frgn.title = md5(3::text) AND other.id = 3);
Thoughts?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company