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 3AB68D1B8A3 for ; Fri, 6 Feb 2004 04:36:58 +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 28610-04 for ; Fri, 6 Feb 2004 00:36:55 -0400 (AST) Received: from octo.speedsite.com (ghanima.speedsite.com [206.126.224.5]) by svr1.postgresql.org (Postfix) with SMTP id D4A0FD1CAD9 for ; Fri, 6 Feb 2004 00:36:54 -0400 (AST) Received: (qmail 31888 invoked from network); 6 Feb 2004 04:36:53 -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 04:36:53 -0000 User-Agent: Microsoft-Entourage/10.1.1.2418 Date: Thu, 05 Feb 2004 22:36:52 -0600 Subject: Index (re)-creation speed From: Steve Lane To: Message-ID: In-Reply-To: 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/68 X-Sequence-Number: 12242 All: Been looking at speed issues related to loading large data sets, and experimenting with dropping and rebuilding indexes. I noticed some interesting behavior on the table in question. 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 When I drop and rebuild the indexes, they take oddly varying amounts of time to rebuild. I rebuilt them in the following order, with the following rough times. I took a guess that the speed of the rebuild might be related to the number of distinct values in the column -- this seems true in some cases but not in others. Here are the times: id_response 38 secs (86000 distinct) id_topic 33 secs (6 distinct) id_survey 13 secs (1 distinct) id_code 39 secs (1444 distinct) id_item 40 secs (65 distinct) id_administration 13 secs (1 distinct) Is there anything I should be learning from this? -- 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 =======================================================