From: Stephan Vollmer <svollmer@gmx.de>
To: pgsql-general@postgresql.org
Subject: Creation of tsearch2 index is very slow
Date: Fri, 20 Jan 2006 15:01:59 +0100
Message-ID: <43D0ED57.1020608@gmx.de> (raw)
Hello!
I noticed that the creation of a GIST index for tsearch2 takes very
long - about 20 minutes. CPU utilization is 100 %, the resulting
index file size is ~25 MB. Is this behaviour normal?
Full text columns: title author_list
tsearch2 word lists: fti_title fti_author_list
tsearch2 indexes: idx_fti_title idx_fti_author_list
The table has 700,000 records. When I create a normal B-Tree index
on the same column for testing purposes, it works quite fast
(approx. 30 seconds).
The columns that should be indexed are small, only about 10 words on
average.
System specs:
Athlon64 X2 3800+, 2 GB RAM
PostgreSQL 8.1.2, Windows XP SP2
I've never noticed this problem before, so could it probably be
related to v8.1.2? Or is something in my configuration or table
definition that causes this sluggishness?
Thanks very much in advance for your help!
- Stephan
This is the table definition:
-----------------------------------------------------------------
CREATE TABLE publications
(
id int4 NOT NULL DEFAULT nextval('publications_id_seq'::regclass),
publication_type_id int4 NOT NULL DEFAULT 0,
keyword text NOT NULL,
mdate date,
"year" date,
title text,
fti_title tsvector,
author_list text,
fti_author_list tsvector,
overview_timestamp timestamp,
overview_xml text,
CONSTRAINT publications_pkey PRIMARY KEY (keyword) USING INDEX
TABLESPACE dblp_index,
CONSTRAINT publications_publication_type_id_fkey FOREIGN KEY
(publication_type_id)
REFERENCES publication_types (id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT publications_year_check CHECK (date_part('month'::text,
"year") = 1::double precision AND date_part('day'::text, "year") =
1::double precision)
)
WITHOUT OIDS;
CREATE INDEX fki_publications_publication_type_id
ON publications
USING btree
(publication_type_id)
TABLESPACE dblp_index;
CREATE INDEX idx_fti_author_list
ON publications
USING gist
(fti_author_list)
TABLESPACE dblp_index;
CREATE INDEX idx_fti_title
ON publications
USING gist
(fti_title)
TABLESPACE dblp_index;
CREATE INDEX idx_publications_year
ON publications
USING btree
("year")
TABLESPACE dblp_index;
CREATE INDEX idx_publications_year_part
ON publications
USING btree
(date_part('year'::text, "year"))
TABLESPACE dblp_index;
CREATE TRIGGER tsvectorupdate_all
BEFORE INSERT OR UPDATE
ON publications
FOR EACH ROW
EXECUTE PROCEDURE multi_tsearch2();
Attachments:
signature.asc (application/pgp-signature, 249B) download
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: svollmer@gmx.de, pgsql-general@postgresql.org
Subject: Re: Creation of tsearch2 index is very slow
In-Reply-To: <43D0ED57.1020608@gmx.de>
* 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