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.