Faheem Mitha <faheem(at)email(dot)unc(dot)edu> wrote:
>> If you're concerned about memory usage, try reducing work_mem;
>> you've probably got it set to something huge.
>
> work_mem = 1 GB (see diag.{tex/pdf}).
>
> The point isn't that I'm using so much memory. Again, my question
> is, why are these changes affecting memory usage so drastically?
Because the planner looks at a very wide variety of plans, some of
which may use many allocations of work_mem size, and some of which
don't. The costs are compared and the lowest cost one is chosen. If
you are close to the "tipping point" then even a very small change
might affect which is chosen. It pays to keep the work_mem setting
sane so that unexpected plan changes don't cause problems.
Look at the plans and their costs to get a feel for what's being
chosen and why. Although it's a very bad idea to use these in
production, you can often shift the plan to something you *think*
would be better using the enable_* settings, to see what the planner
thinks such a plan will cost and where it thinks the cost would be;
that can help in tuning the settings.
>> You might need to create some indices, too.
>
> Ok. To what purpose? This query picks up everything from the
> tables and the planner does table scans, so conventional wisdom
> and indeed my experience, says that indexes are not going to be so
> useful.
There are situations where scanning the entire table to build up a
hash table is more expensive than using an index. Why not test it?
-Kevin