From: Gavin Hamill <gdh@laterooms.com>
To: pgsql-performance@postgresql.org
Subject: CREATE INDEX rather sluggish
Date: Thu, 30 Mar 2006 09:26:13 +0100
Message-ID: <442B9625.1060408@laterooms.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gdh@laterooms.com
Subject: Re: CREATE INDEX rather sluggish
In-Reply-To: <442B9625.1060408@laterooms.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox