From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-sql@hub.org
Subject: Re: [SQL] Performance
Date: Wed, 10 Mar 1999 10:37:20 -0500
Message-ID: <17587.921080240@sss.pgh.pa.us> (raw)
In-Reply-To: <199903100400.XAA42129@hub.org>
"Brett W. McCoy" <bmccoy@lan2wan.com> writes:
>> I have 1,400,000 entries (200MB) I'm inserting into a database. Radius
>> detail files as a matter of fact. Apart from COPY taking forever to load
>> that (probably due to my several indexes), it seems the select is VERY
>> slow. Any tips?
> I found that if you create an index before doing a bulk COPY, yes, it does
> take forever to load, and the select is slow. What I did was drop the
> indices built from the COPY and rebuild them. Speeded the selects up
> significantly. So now I don't build any indices until after I load my
> huge databases in.
Not building the indexes until you've done the bulk load is good advice;
it does seem a lot faster to build an index on an already-loaded table
than to construct it piecemeal during the COPY. However, either way
should result in the same index, so I don't see why it'd affect the
speed of a subsequent SELECT. Did you remember to do VACUUM ANALYZE
both times? The system is likely to ignore the index until you have
vacuumed the table.
In short, best bulk load procedure is
CREATE TABLE ...
COPY ...
CREATE INDEX(es) on table
Repeat as needed for all tables being bulk-loaded
VACUUM ANALYZE
BTW, if you use pg_dump to dump and reload a big database, pg_dump
knows about the create-indexes-last trick. But it doesn't do a VACUUM
for you; you have to do that by hand after running the reload script,
or your database will be slow.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, pgsql-sql@hub.org
Subject: Re: [SQL] Performance
In-Reply-To: <17587.921080240@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox