X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 46C06D1B897 for ; Fri, 6 Feb 2004 03:48:52 +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 14741-09 for ; Thu, 5 Feb 2004 23:48:53 -0400 (AST) Received: from octo.speedsite.com (ghanima.speedsite.com [206.126.224.5]) by svr1.postgresql.org (Postfix) with SMTP id 2EAADD1B49E for ; Thu, 5 Feb 2004 23:48:48 -0400 (AST) Received: (qmail 27505 invoked from network); 6 Feb 2004 03:48:37 -0000 Received: from c-24-13-165-197.client.comcast.net (HELO ?24.13.165.197?) (slane@24.13.165.197) by ghanima.speedsite.com with SMTP; 6 Feb 2004 03:48:37 -0000 User-Agent: Microsoft-Entourage/10.1.1.2418 Date: Thu, 05 Feb 2004 21:48:35 -0600 Subject: Drop indexes inside transaction? From: Steve Lane To: Message-ID: In-Reply-To: <4193.1076000989@sss.pgh.pa.us> Mime-version: 1.0 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/67 X-Sequence-Number: 12241 Hello all: Question here about how best to optimize a large data load. Data load is ad hoc and so needs to be able to run during production hours. We have a piece of software written in a desktop RAD environment (FileMaker Pro). Our users are using this tool to generate data that we need to get into postgresql. We're sending it through a middleware layer written in PHP. A single data load will generate from 10K-100K rows. Initially we did everything procedurally through PHP. 100K inserts, each one called through several layers of PHP abstraction. Bad idea. Current idea is to have PHP dump the data to a file, and suck the whole file in at once somehow. So far, so good: PHP can create the file in 6 minutes for 100K rows. That's actually acceptable. Now we want to use COPY to bring the data in. The target table has 6 indexes. Without indexes, naturally, we can load 80K rows in 2 seconds. With indexes, 46 seconds. (oddly, ONE index contributes 40+ seconds of that, yet they're all similar, single-column indexes. Anyway, that's another story). Normally, in a batch-like environment, I'd feel free to drop the indexes, load, reindex. That's perfectly fast. But the environment needs to be live, and those indexes are vital to a reporting engine that can be hit at any time. So we can't just drop them, even briefly. So I hit on the idea of doing the same thing, but inside a transaction. In theory that should affect no one else. To my delight, the transaction drop-copy-reindex ran in 7 seconds. I guess I'm just wondering how that's possible. I hate to sound like a superstitious goof, but it sounds to good to be true. At best, I figured to pay the whole penalty at the time of COMMIT -- that it would be fast up to that point, and then of course need to do exactly the same work as the transactionless version, as far as reconciling indexes or whatever the more accurately technical term is. So: is this too good to be true? Or is this actually a good way to do this? Any other observations on the whole process? Is there a better or different approach, or other things we should consider? Any and all thoughts are welcome. -- sgl ======================================================= Steve Lane Vice President The Moyer Group 14 North Peoria St Suite 2H Chicago, IL 60607 Voice: (312) 433-2421 Email: slane@moyergroup.com Fax: (312) 850-3930 Web: http://www.moyergroup.com =======================================================