CREATE INDEX rather sluggish

From: Gavin Hamill <gdh(at)laterooms(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: CREATE INDEX rather sluggish
Date: 2006-03-30 08:26:13
Message-ID: 442B9625.1060408@laterooms.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Markus Schaber 2006-03-30 08:29:51 Re: Decide between Postgresql and Mysql (help of
Previous Message Greg Quinn 2006-03-30 05:57:23 [Solved] Slow performance on Windows .NET and OleDb