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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Reply via email to