X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id CEB38D1D400 for ; Thu, 20 Nov 2003 22:21:18 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 20064-10 for ; Thu, 20 Nov 2003 18:20:50 -0400 (AST) Received: from b0x.supernerd.com (unknown [208.186.134.90]) by svr1.postgresql.org (Postfix) with ESMTP id 909F1D1B555 for ; Thu, 20 Nov 2003 18:20:47 -0400 (AST) Received: from trogdor (unknown [67.42.122.198]) by b0x.supernerd.com (Postfix) with ESMTP id 5BF545FFF2; Thu, 20 Nov 2003 15:16:59 -0700 (MST) Message-ID: <01d701c3afb4$90b68280$0700a8c0@trogdor> From: "Rick Gigger" To: "Mike Mascari" Cc: "PgSQL General ML" 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> Subject: Re: performance problem Date: Thu, 20 Nov 2003 15:20:53 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/1149 X-Sequence-Number: 52799 > 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 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.