Re: [HACKERS] postgres_fdw bug in 9.6

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I started to look at this patch again today and got cold feet. It
> seems to me that this entire design on the posted patch is based on
> your remarks in http://postgr.es/m/13242.1481582736@sss.pgh.pa.us --

> # One way that we could make things better is to rely on the knowledge
> # that EPQ isn't asked to evaluate joins for more than one row per input
> # relation, and therefore using merge or hash join technology is really
> # overkill. We could make a tree of simple nestloop joins, which aren't
> # going to care about sort order, if we could identify the correct join
> # clauses to apply.

> However, those remarks are almost entirely wrong.

Hm, one of us is confused. Possibly it's me, but:

> It's true that the
> relations for which we have EPQ tuples will only ever output a single
> tuple,

Yup, and that is all of them. We should have a tuple identity
(ctid or whole-row) for every base relation in an EPQ test.

> but because of what you said in the quoted text above, this
> does NOT imply that the recheck plan never needs to worry about
> dealing with a large number of rows, as the following example shows.

This example doesn't prove anything whatsoever about what happens during
EPQ, though, or at least it shouldn't. If we're examining more than one
row then we're doing it wrong, because the point of the EPQ evaluation
is only to determine whether one single joined row (still) meets the
query conditions.

> After some thought, it seems that there's a much simpler way that we
> could fix the problem you identified in that original email: if the
> EPQ path isn't properly sorted, have postgres_fdw's
> add_paths_with_pathkeys_for_rel stick a Sort node on top of it. I
> tried this and it does indeed fix Jeff Janes' initial test case.

Hm. Simple is certainly good, but if there's multiple rows coming
back during an EPQ recheck then I think we have a performance problem.

regards, tom lane