CREATE INDEX rather sluggish
От | Gavin Hamill |
---|---|
Тема | CREATE INDEX rather sluggish |
Дата | |
Msg-id | 442B9625.1060408@laterooms.com обсуждение исходный текст |
Ответы |
Re: CREATE INDEX rather sluggish
Re: CREATE INDEX rather sluggish |
Список | 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.
В списке pgsql-performance по дате отправления: