Thread: How to tune a SELECT in 7.0.2

I'm just starting with PostgreSQL, and doing it in
a (maybe too) big way.  I'm aiming to use it for
some database research, so I've got about a GB of
synthetic data from the OLAP benchmark.

To begin with, I have some chores, and they're taking
an unreasonably long time.  I need help.  I suspect
in the first place that I need to so something to
speed up sorting because I have a CREATE TABLE AS
that has run over a day now with no signs of ending.

The query plan from EXPLAIN is pure SORT and MERGE
JOIN, which is as I suspect it should be.  On this
hardware, a UNIX sort of the text file the data
came from (while the CTAS was still going on) took
1.5 hours of user+sys time, and just over 3 hours
elapsed.  There are three tables, so there are
three sorts and two merges (no sort on the output of
the first merge). So I would expect maybe 3x5 or 15
hours to be pessimistic, and I'm way beyond that
already.

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.

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.

I'm very glad to get directions to RTFM, if it comes
with a pointer to which (or which part of the) FM to R.
I've already read all the FMs that came with the
product.

++ kevin



--
Kevin O'Gorman  (805) 650-6274  mailto:kogorman@pacbell.net
Permanent e-mail forwarder:  mailto:Kevin.O'Gorman.64@Alum.Dartmouth.org
At school: mailto:kogorman@cs.ucsb.edu
Web: http://www.cs.ucsb.edu/~kogorman/index.html
Web: http://trixie.kosman.via.ayuda.com/~kevin/index.html

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