From: Rick Gigger <rick@alpinenetworking.com>
To: Mike Mascari <mascarm@mascari.com>
Cc: PgSQL General ML <pgsql-general@postgresql.org>
Subject: Re: performance problem
Date: Thu, 20 Nov 2003 15:20:53 -0700
Message-ID: <01d701c3afb4$90b68280$0700a8c0@trogdor> (raw)
References: <1069164075.986.239.camel@bbking.uio.no>
<20031118142919.GI4953@libertyrms.info>
<1069180780.2769.91.camel@haggis.homelan>
<019301c3ae07$d714e220$0700a8c0@trogdor>
<87llqdqy37.fsf@asmodeus.mcnaught.org>
<20031118205057.K606@hermes.hilbert.loc>
<01b201c3ae14$92bd3c00$0700a8c0@trogdor>
<3FBA8911.5060805@mascari.com>
<01cc01c3ae1c$b54fa980$0700a8c0@trogdor>
<01b401c3afa8$2ba201f0$0700a8c0@trogdor>
<3FBD2E6F.20405@mascari.com>
> If you examine the behavior of pg_dump output, you'll notice that it
> doesn't built indexes until after the COPY command has completed the
> data import. It's a waste of cpu cycles and disk bandwidth to update
> indexes on upon every insert. Your script should:
>
> 1) Drop all indexes on the relation
> 2) Use COPY if possible instead of INSERT to import the data
> 3) Recreate the indexes
> 4) Use UPDATE to update as necessary
I never thought of dropping the indexes in the middle of the script and
readding them. I guess that would be fine as long as nothing else was
running that needed the index while it was gone. I don't think 7.2.x
supports using COPY unless you are inserting all of the fields which I don't
want to do although I suppose I could just insert all of the defaults. Is
that correct?
> > I am using 7.2.4. Has this improved in later versions? I'm not
concerened
> > since this is a very rare thing to need to do and it's obviously
possible to
> > work around but it would be nice if postgres could figure things like
that
> > out on it's own. (It certainly would have saved me a lot of time and
> > confusion last night at about 3 am). Is there a way to for the use of a
> > specific index on a query?
>
> You can force the use of an index scan by turning sequential scans to off:
>
SET ENABLE_SEQSCAN TO OFF;
Thanks.
> But the problem is that the statistics didn't match the data. You
> could have:
>
> 1) Used INSERTs to insert the data into a relation with an index
> 2) Executed ANALYZE <foo> to update the statistics
> 3) Perform the UPDATE
That would have been the easiest thing. I wanted to do that but I thought
that you had to do a vacuum (which I couldn't do in the transaction)
together with analyze. I didn't realize that analyze was a command all by
itself.
> After the UPDATE, you'll still have dead tuples (the original rows)
> which require that they be marked as dead, and so you should
> occassionally run either VACUUM to mark them as such or VACUUM FULL to
> reclaim the dead space or VACUUM FULL ANALYZE to also update the
> relation's statistics.
I do them all nightly with cron on all my important databases. I just had a
problem with need the analyze to happen in the middle of the transaction in
this one special case.
> I would just execute the steps I outlined above with COPY and not
> worry about ANALYZEs and VACUUMs in a script. People often run VACUUM
> ANALYZE in a 1/day cron job and VACUUM FULL ANALYZE in a 1/week cron
> job. Then, of course, there's REINDEX...
Doing the vacuum full analyze doesn't take long on to do if I do it once a
day so I just do that.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: rick@alpinenetworking.com, mascarm@mascari.com, pgsql-general@postgresql.org
Subject: Re: performance problem
In-Reply-To: <01d701c3afb4$90b68280$0700a8c0@trogdor>
References: <1069164075.986.239.camel@bbking.uio.no> <20031118142919.GI4953@libertyrms.info> <1069180780.2769.91.camel@haggis.homelan> <019301c3ae07$d714e220$0700a8c0@trogdor> <87llqdqy37.fsf@asmodeus.mcnaught.org> <20031118205057.K606@hermes.hilbert.loc> <01b201c3ae14$92bd3c00$0700a8c0@trogdor> <3FBA8911.5060805@mascari.com> <01cc01c3ae1c$b54fa980$0700a8c0@trogdor> <01b401c3afa8$2ba201f0$0700a8c0@trogdor> <3FBD2E6F.20405@mascari.com> <01d701c3afb4$90b68280$0700a8c0@trogdor>
* 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