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: firstname.lastname@example.org, 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