tsearch2 seem very slow

From: "Ahmad Fajar" <fajar(at)it-indonesia(dot)info>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: tsearch2 seem very slow
Date: 2005-09-21 22:08:12
Message-ID: SVONEVY5jVkjA2DJxRV0000018d@ki-communication.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I have about 419804 rows in my article table. I have installed tsearch2 and
its gist index correctly.

My table structure is:

CREATE TABLE tbarticles

(

articleid int4 NOT NULL,

title varchar(250),

mediaid int4,

datee date,

content text,

contentvar text,

mmcol float4 NOT NULL,

sirkulasi float4,

page varchar(10),

tglisidata date,

namapc varchar(12),

usere varchar(12),

file_pdf varchar(255),

file_pdf2 varchar(50),

kolom int4,

size_jpeg int4,

journalist varchar(120),

ratebw float4,

ratefc float4,

fti tsvector,

CONSTRAINT pk_tbarticles PRIMARY KEY (articleid)

) WITHOUT OIDS;

Create index fti_idx1 on tbarticles using gist (fti);

Create index fti_idx2 on tbarticles using gist (datee, fti);

But when I search something like:

Select articleid, title, datee from tbarticles where fti @@
to_tsquery('susilo&bambang&yudhoyono&jusuf&kalla');

It takes about 30 sec. I run explain analyze and the index is used
correctly.

Then I try multi column index to filter by date, and my query something
like:

Select articleid, title, datee from tbarticles where fti @@
to_tsquery('susilo&bambang&yudhoyono&jusuf&kalla') and datee >= '2002-01-01'
and datee <= current_date

An it still run about 25 sec. I do run explain analyze and my multicolumn
index is used correctly.

This is not acceptable if want to publish my website if the search took very
longer.

I have run vacuum full analyze before doing such query. What going wrong
with my query?? Is there any way to make this faster?

I have try to tune my postgres configuration, but it seem helpless. My linux
box is Redhat 4 AS, and

the hardware: 2 GB RAM DDR 400, 2x200 GB Serial ATA 7200RPM and configure as
RAID0 (just for postgres data), my sistem run at EIDE 80GB 7200 RPM.

Please.help.help.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Gurpreet Aulakh 2005-09-22 00:11:17 Re: Query slower on 8.0.3 (Windows) vs 7.3 (cygwin)
Previous Message Gurpreet Aulakh 2005-09-21 19:38:20 Query slower on 8.0.3 (Windows) vs 7.3 (cygwin)