Re: How to tune a SELECT in 7.0.2

On Sun, Sep 17, 2000 at 09:14:23PM -0700, Kevin O'Gorman wrote:
> I've explored the SET command, and see nothing
> helpful there.  My documentation is a bit out of sync
> because I cannot SHOW NAMES (which is on the line
> with SHOW CLIENT_ENCODING) and I cannot SHOW
> CONSTRAINTS.

You probably didn't compile PostgreSQL with multibyte support.

> If there are command line switches that would help,
> I haven't found them.  I suspect I need to give
> sorting more internal memory inside the backend.
> I have 256MB ram, and not much else going on, so I
> would gladly give 65MB just to this, I just need to
> know how.

To do so, use the '-S' flag to postgres. From the User Guide:

//
-S SortSize

Specifies the amount of memory to be used by internal sorts
and hashes before resorting to temporary disk files. The value
is specified in kilobytes, and defaults to 512 kilobytes. Note
that for a complex query, several sorts and/or hashes might be
running in parallel, and each one will be allowed to use as much
as SortSize kilobytes before it starts to put data into temporary
files.
//

So you'll want to start postmaster with this flag, in addition to
whatever else you're using:

'-o -S 4096'

To allow each postgres backend to use, 4MB, or 8x more memory than
normal, for sorting.

HTH,

Neil

--
Neil Conway <neilconway@home.com>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed

Good composers borrow. Great composers steal.
        -- Igor Stravinsky