Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group