Re: nested query vs left join: query planner very confused

David Rysdam <drysdam(at)ll(dot)mit(dot)edu> writes:
> effective_cache_size - 12000MB
> shared_buffers - 1024MB
> random_page_cost - is commented out
> cpu_tuple_cost - commented out
> work_mem - commented out

> I assume you guys already know the default values for those last 3 on a
> 9.0.x server...

Default work_mem is only 1MB, so that probably explains why you're not
getting a hashed subplan here. Have them knock it up some, say on the
order of 10MB. (If none of your queries are any more complicated than
this one, you could go higher. But keep in mind that a backend can use
work_mem per sort/hash/materialize step, not per query --- so complex
queries can use many times work_mem. Multiply that by the number of
backends, and you can end up in swap hell pretty quickly with an over
optimistic value.)

regards, tom lane