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 4BF65D1B8A3 for ; Fri, 6 Feb 2004 06:54:11 +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 60353-05 for ; Fri, 6 Feb 2004 02:54:08 -0400 (AST) Received: from octo.speedsite.com (ghanima.speedsite.com [206.126.224.5]) by svr1.postgresql.org (Postfix) with SMTP id 7BEE7D1B47D for ; Fri, 6 Feb 2004 02:54:06 -0400 (AST) Received: (qmail 1380 invoked from network); 6 Feb 2004 06:54:03 -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 06:54:03 -0000 User-Agent: Microsoft-Entourage/10.1.1.2418 Date: Fri, 06 Feb 2004 00:54:01 -0600 Subject: Re: Drop indexes inside transaction? From: Steve Lane To: Message-ID: In-Reply-To: <24577.1076048581@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/71 X-Sequence-Number: 12245 On 2/6/04 12:23 AM, "Tom Lane" wrote: > Steve Lane writes: >> 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). > > No, I'd say that's the key part of the story. Details? I have another post out there called "index re-creation speed" that delves into this. Here's the table: Table "test_responses_2" Attribute | Type | Modifier -------------------+-------------------+---------- id_response | integer | id_code | integer | id_survey | integer | id_topic | integer | id_item | integer | id_administration | integer | response_int | smallint | response_txt | character varying | rec_handle | character varying | Indices: id_administration_test_key, id_code_test_key, id_item_test_key, id_response_test_key, id_survey_test_key, id_topic_test_key Id_item_test_key is the one that drives up the speed of the COPY dramatically. 65 distinct values in that column for the given data set. > >> So I hit on the idea of doing the same thing, but inside a transaction. In >> theory that should affect no one else. > > ... other than locking them out of the table while the transaction runs. Ha! I knew that that tasty-looking lunch had to have a price tag. > That doesn't sound like what you want to do. In any case, reindexing > the table will get slower and slower as the pre-existing data in the > table expands. Yes, I've been running some tests and it stops being very acceptable around 3-5M rows. So am I more or less out of luck? The issue, again, is how to insert at most 80-100K rows into a running system, at a time determined by users, into a fairly heavily-indexed table. I thought of dropping the indexes and deferring index recreation -- maybe even having a very frequent cron job rebuild the indexes. All that buys me is being able to return control to the initiating user quickly. It still has the issue of expanding reindex time and, I'm guessing, the issue of locking out other users as well. Am I simply asking too much of my tools here? -- sgl