Re: Conditional query plans.

"Michael Richards" <michael(at)fastmail(dot)ca> writes:
> The second and probably less optimal plan would be to create a hash
> of these 25 answers and do a sequential scan on users updating rows
> where id is found in that hash.

Given the presence of the "materialize" nodes, I don't think this query
plan is quite as nonoptimal as you think, especially for ~25 rows out of
the subplan. It's a linear search over a 25-entry table for each outer
row, but so what? With hundreds or thousands of rows out of the
subquery, it'd be nice to have a smarter table lookup method, agreed,
but here it hardly matters.

Something that's been on the todo list for a long time is to try to
convert WHERE foo IN (SELECT ...) queries into some kind of join,
instead of a subselect. With that approach we'd be able to use merge
or hash strategies to match up inner and outer rows, which'd work a lot
better when there are large numbers of rows involved. It might actually
happen for 7.2...

regards, tom lane