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

On Wed, 27 Nov 2013 13:02:20 -0500, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.)

We deliberately try to keep our queries fairly simple for several
reasons. This isn't the most complicated, but they don't get much more
than this. I'll have them start with 10MB and see what they get.