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)


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.



Reply via email to