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
rm32176.sql
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
