Re: work-mem how do I identify the proper size

In response to "Campbell, Lance" <lance(at)uiuc(dot)edu>:

> If I set work-mem at a particular amount of memory how do I answer the
> following questions:
>
> 1) How many of my queries were able to run inside the memory I
> allocated for work-mem?
>
> 2) How many of my queries had to run from disk because work-mem
> was not set high enough?
>
> 3) If a query had to go to disk in order to be sorted or completed
> is there a way to identify how much memory it would have taken in order
> to run the query from memory?

I don't know of any good way to answer these questions on current versions.

I have a patch in for 8.3 that logs the usage of temporary files, which
helps with some of this.

It'd be nice to have additional debug logging that tells you:
1) when a sort/join operation uses disk instead of memory
2) A higher level debugging that announces "this query used temp files for
some operations".

#1 would be nice for optimizing, but may involve a lot of overhead.

#2 could (potentially) be enabled on production servers to flag queries
that need investigated, without generating a significant amount of logging
overhead.

Hopefully I'll get some time to try to hack some stuff together for this
soon.

A little bit of playing around shows that cost estimates for queries change
radically when the system thinks it will be creating temp files (which
makes sense ...)

Notice these two partial explains:
-> Sort (cost=54477.32..55674.31 rows=478798 width=242)
-> Sort (cost=283601.32..284798.31 rows=478798 width=242)

These are explains of the same query (a simple select * + order by on a
non-indexed column) The first one is taken with work_mem set at 512m,
which would appear to be enough space to do the entire sort in memory.
The second is with work_mem set to 128k.

More interesting is that that actual runtime doesn't differ by nearly
that much: 3100ms vs 2200ms. (I've realized that my setting for
random_page_cost is too damn high for this hardware -- thanks for
causing me to look at that ... :)

Anyway -- hope that helps.

--
Bill Moran
Collaborative Fusion Inc.