Re: planner missing a trick for foreign tables w/OR conditions

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> 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.

> Thoughts?

The problem is that that optimization is a crock; see the comments
for create_or_index_quals(). We can't just turn it loose to CNF-ify
every OR it might find. The case that we support at the moment is
to CNF-ify whichever single OR condition looks like the best win,
and it's hard to see how to do that without any index knowledge.

In principle, when we're using remote estimates, we could probably
ask the remote server about each possibility ... but that could be
expensive.

regards, tom lane