Re: tsearch2 seem very slow

From: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
To: Ahmad Fajar <fajar(at)it-indonesia(dot)info>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: tsearch2 seem very slow
Date: 2005-09-23 07:35:48
Message-ID: Pine.GSO.4.63.0509231134140.27150@ra.sai.msu.su
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Ahmad,

how fast is repeated runs ? First time system could be very slow.
Also, have you checked my page
http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_Notes
and some info about tsearch2 internals
http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_internals

Oleg
On Thu, 22 Sep 2005, Ahmad Fajar wrote:

> 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.
>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message K C Lau 2005-09-23 08:53:55 Re: SELECT LIMIT 1 VIEW Performance Issue
Previous Message Joost Kraaijeveld 2005-09-23 06:49:27 How to determine cause of performance problem?