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 330C7D1C9E9 for ; Thu, 20 Nov 2003 21:13:01 +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 10907-07 for ; Thu, 20 Nov 2003 17:12:30 -0400 (AST) Received: from b0x.supernerd.com (unknown [208.186.134.90]) by svr1.postgresql.org (Postfix) with ESMTP id 0410AD1B545 for ; Thu, 20 Nov 2003 17:12:29 -0400 (AST) Received: from trogdor (unknown [67.42.122.198]) by b0x.supernerd.com (Postfix) with ESMTP id 3DAB25FFF2; Thu, 20 Nov 2003 14:08:40 -0700 (MST) Message-ID: <01c601c3afab$0445d660$0700a8c0@trogdor> From: "Rick Gigger" To: "Alvaro Herrera Munoz" Cc: "Mike Mascari" , "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> <20031120210600.GC26865@dcc.uchile.cl> Subject: Re: performance problem Date: Thu, 20 Nov 2003 14:12:32 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit 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/1146 X-Sequence-Number: 52796 Ah, I didn't realize that you could just do an ANALYZE. I thought there was only VACUUM ANALYZE but that can't run inside of a transaction. Thanks, rg ----- Original Message ----- From: "Alvaro Herrera Munoz" To: "Rick Gigger" Cc: "Mike Mascari" ; "PgSQL General ML" Sent: Thursday, November 20, 2003 2:06 PM Subject: Re: [GENERAL] performance problem On Thu, Nov 20, 2003 at 01:52:10PM -0700, Rick Gigger wrote: > I worked around this by starting the transaction and inserting the 45,000 > rows and then killing it. The I removed the index and readded it which > apparently gathered some stats and since there were all of the dead tuples > in there from the failed transaction it now decided that it should use the > index. I reran the script and this time it took 5 minutes again instead of > 1 1/2 hours. Stats are not collected automatically. You should run ANALYZE after importing your data. And it's probably faster to create the index after the data is loaded, too. -- Alvaro Herrera () Y una voz del caos me habló y me dijo "Sonríe y sé feliz, podría ser peor". Y sonreí. Y fui feliz. Y fue peor.