Re: join from array or cursor

On Fri, Aug 21, 2009 at 1:16 PM, John DeSoi<desoi(at)pgedit(dot)com> wrote:
>
> Yes, this is the best I have come up with so far. I have a set returning
> function which returns the key and the index number. The implementation with
> a cursor looks like this:
>
> SELECT * FROM cursor_pk('c1') c LEFT JOIN foo ON (c.pk = foo.pk) ORDER BY
> c.idx;

This might be a circumstance where it's really best to just force the
planner to use a particular plan. There's no way to tell it "the order
of the array" without forcing an entirely unnecessary sort.

Something like
SET LOCAL enable_mergejoin = 'off';
SET LOCAL enable_hashjoin = 'off';
SELECT * from unnest(arr) as v(id) join tab using (id)
RESET ALL;

--
greg
http://mit.edu/~gsstark/resume.pdf