X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 36A7711F68E4 for ; Thu, 30 Mar 2006 04:28:01 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 01804-03 for ; Thu, 30 Mar 2006 04:28:00 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from fon.nation-net.com (213-2-2-26.uk.vianw.net [213.2.2.26]) by postgresql.org (Postfix) with ESMTP id E95B611F66D7 for ; Thu, 30 Mar 2006 04:27:58 -0400 (AST) Received: from fon.nation-net.com (localhost [127.0.0.1]) by fon.nation-net.com (Postfix) with ESMTP id 022FA136E92 for ; Thu, 30 Mar 2006 09:27:56 +0100 (BST) Received: from [194.24.251.10] (bum.net [194.24.251.10]) by fon.nation-net.com (Postfix) with ESMTP id E9CD6136E84 for ; Thu, 30 Mar 2006 09:27:55 +0100 (BST) Message-ID: <442B9625.1060408@laterooms.com> Date: Thu, 30 Mar 2006 09:26:13 +0100 From: Gavin Hamill User-Agent: Mozilla Thunderbird 1.0.7 (X11/20051013) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: CREATE INDEX rather sluggish Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-AV-Checked: ClamAV using ClamSMTP X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.143 required=5 tests=[AWL=0.143] X-Spam-Score: 0.143 X-Spam-Level: X-Archive-Number: 200603/605 X-Sequence-Number: 18042 Hullo, I have pg 8.1.3 on an 8-CPU AIX 5.3 box with 16GB of RAM, and I'm finding that it's taking an age to CREATE INDEX on a large table: Column | Type | Modifiers ----------------+------------------------+--------------------------------------------------------------------- ID | integer | not null default nextval(('public.keyword_id_seq'::text)::regclass) Text | character varying(200) | Longitude | numeric(16,5) | Latitude | numeric(16,5) | AreaID | integer | SearchCount | integer | not null default 0 Radius | integer | LanguageID | integer | KeywordType | character varying(20) | LowerText | character varying(200) | NumberOfHotels | integer | CountryID | integer | FriendlyText | character varying(200) | Indexes: 2006-03-29 21:39:38 BST LOG: duration: 41411.625 ms statement: CREATE INDEX ix_keyword_areaid ON "Keyword" USING btree ("AreaID"); 2006-03-29 21:42:46 BST LOG: duration: 188550.644 ms statement: CREATE INDEX ix_keyword_lonlat ON "Keyword" USING btree ("Longitude", "Latitude"); 2006-03-29 21:46:41 BST LOG: duration: 234864.571 ms statement: CREATE INDEX ix_keyword_lowertext ON "Keyword" USING btree ("LowerText"); 2006-03-29 21:52:32 BST LOG: duration: 350757.565 ms statement: CREATE INDEX ix_keyword_type ON "Keyword" USING btree ("KeywordType"); The table has just under six million rows - should it really be taking nearly six minutes to add an index? These log snippets were taking during a pg_restore on a newly created db, so there should be no issues with the table needing vacuuming. What parameters in the postgresql.conf are pertinent here? I have shared_buffers 120000 work_mem 16384 maintenance_work_mem = 262144 for starters... any advice would be warmly welcomed! Cheers, Gavin.